Server, SQL Data, & Logs

Monitor, Manage and Protect SQL Server, SQL Data and SQL Logs

What should we be doing to monitor, manage and protect our SQL Server?

If you're a Database Administrator (DBA), you probably don't need our advice. However, we suggest subscribing to Brent Ozar's email list and getting his free SQL Server Health Check Script. See https://www.brentozar.com/blitz/

If you are not a DBA and the idea of managing and protecting your SQL Server makes you a bit nervous, you're in luck. As long as the data is protected, it's pretty hard to break it. That said, monitoring your SQL Server is prudent, and we understand that this might also make you nervous. If so, you should probably ask your IT people (or 3rd party IT) how they monitor your SQL Server and what data they use to decide if it is behaving acceptably. When they provide this info, ask them what you should pay attention to in their report on a month-to-month or week-to-week basis so you can be aware of changes to address. It helps to know these things when you call them to tell them that SQL is sick. For example, "You know line four in your SQL performance report? Well, it's over 80% now. Can you take care of this tomorrow?" is a better conversation to have than "We're down, we don't know why, can you fix it?" It's better because you can monitor server health even if they forget to do so or get busy - and that makes you a better informed SQL Server owner, even if you aren't a DBA.

How often should we backup our SQL Server databases?

While the answer to this is very much connected to your answer to "How often do we need to backup Company data?" SQL offers some capabilities that allow your business to be a bit more resilient than you might think. Understanding the recovery modes will help you understand why we recommend full recovery mode and daily backups.

SQL Server has 3 recovery models: FULL, BULK-LOGGED, and SIMPLE:

FULL recovery mode "is a database recovery model that completely logs all transactions and automatically stores the full set of log records until after they are backed up...It is also possible to restore the database to a specific recovery point in a log backup."1. Microsoft states, "Production databases should be in full recovery mode, and the transaction log should be backed up frequently to help ensure recoverability with minimum data loss."2

• As with anything, there are pros and cons. The pros of Full mode are that if you have a good "full" backup and every transaction log backup taken since the full backup was done, you can restore after a failure right up to the moment of the transaction log backup even if you have not done a full backup in weeks. While we don't recommend not backing up SQL databases for weeks, it happens. You must retain both the full backups and the log backups for this to work. You can restore to a full backup without rolling the database forward with the transactions on the log backup, but you cannot fix a database with a log backup by itself.
BULK-LOGGED recovery mode is available for "...when you try to import massive data and do not want to increase the log with data."3

You would not want a database to remain permanently in this mode, so we will not mention it again.
SIMPLE recovery mode is available when databases are under a program's management (like DAT-MAIL's Mail.dat<tm> databases) or for data that you can afford to lose.

Using Simple mode means that there is no way to recover data lost since the last full backup. However, this mode does offer transaction recovery, which means that if you are updating a database and the program dies, SQL Server will roll out the in-progress changes so that the database is not left with a partial update. This transaction level recovery is the reason why databases in Simple mode have and use their own LDF (database log file).

Our recommendation:

For most databases, including Window Book's wbdb and wbdbcla, we recommend running in Full recovery mode, and our databases default to Full recovery mode when our installers create them. With the necessary full and transaction log backups, this means recovery of your databases is back to the moment of your last transaction log backup. To utilize this capability, you should run a full backup daily (what we recommend in most cases) and then run a transaction log every x hours - where x is the amount of work you are willing to redo after a failure.

Why is it OK for Window Book to use SIMPLE recovery mode for DAT-MAIL's Mail.dat (MD****) databases?

If you ask this question, we assume you understand that you should not use Simple recovery mode for SQL Server. Unlike our wbdb and wbdbcla databases that get frequent, if not constant use, the MD*** databases are kept separate from other databases and backed up under DAT-MAIL control. They are accessed only when the job is open in DAT-MAIL and are otherwise detached from SQL Server for performance reasons. In addition, there is never a time where you would want to roll forward (recover) a Mail.dat database from a backup to a specific point in time. Any changes DAT-MAIL makes to these databases must either be considered complete, or they must be rolled back completely. DAT-MAIL knows when it needs to backup these databases, when it needs to restore them and so on. It needs to "own" the backups for these databases because the mail processing workflow often requires restoring the database (and thus, the Mail.dat), archiving it, etc. The MD*** database is, essentially, "the job". As such, DAT-MAIL is managing these databases and their backups. Window Book strongly suggests that no other SQL database backup system should touch these databases. It is, however, OK for traditional hard drive backup systems to back them up, either individually or as part of a whole-drive-backup.

We also use Simple mode for these databases because Full would require log maintenance on these databases. At most shops, that would significantly increase the workflow required to backup, transaction log backup and log truncate for each of these databases. The usage of these databases does not require up-to-the-minute recovery (as explained above), so we feel its best to avoid the possibly significant maintenance load for these (often numerous) databases. It simply is not worth the work.

Can I set up DAT-MAIL to perform automatic purging of unnecessary files?

Yes. Window Book has tools for DAT-MAIL to provide automatic purging. Contact Tech Support for more information.

Where are the SQL database files?

By default, they're at C:\Program Files\Microsoft SQL Server\<SQL version reference>. MSSQLSERVER\MSSQL\DATA\, but the default location can change, and sometimes the part right before \DATA\ changes a little depending on the type of SQL instance (Named vs. Default) and the version of SQL Server that you're running. We recommend that data files (.MDF) and log files (.LDF) be on different drives for performance reasons, but not everyone can do that.

What does Support mean, when they say there is a 10 gig per database limit?

SQL Server comes in different editions: Enterprise, Developer, Standard, and Express. Some versions also have Datacenter and Web editions. Each edition has its limitations. The free Express Edition of SQL Server only supports databases up to 10 gigabytes. That means the .MDF file (where the data resides) cannot exceed 10 gig (10,000,000,000) in size. Near this limit and you will run into performance and data issues. If you have a high-volume shop or process massive mail jobs, you should look into licensing SQL Server Standard Edition.

What is the .LDF file? We have a massive one on our drive, and it is causing us to run out of disk space.

The LDF file is the SQL Server transaction log for a particular database. This file grows with database changes (new records/changed records/deleted records). When a record is changed or deleted, it logs the before image of that record. It also logs adding records. The idea is that you can take the last full backup, and using a SQL utility, you use data on the transaction log to "roll forward" from the previous full backup to re-apply all the adds, changes, and deletes.

The growth pattern of an LDF file can seem a bit erratic. Its size comes from the level of activity on your databases combined with backup and log truncation frequency, combined with the recovery mode. How much free disk space you have will affect how you react. A fast-paced, high-volume site using full recovery mode will need to back up and truncate logs.

Can we shrink the transaction log?

Yes, but you need to be realistic about it. Shrinking the log to an unnaturally small size saves disk space temporarily. SQL Server will once again expand the log's size when working on the database, and each time the log grows, it is an expensive process from a RAM and CPU perspective. When the system has to make these expansions, it can temporarily impact the performance of your applications. Microsoft recommends that you do not shrink the log unless there is no choice. They may say this is because people are likely to take the shrinkage to an extreme and end up suffering through repeated log growth periods, which are expensive from an I/O and CPU perspective. Those things negatively reflect on Windows and SQL Server, even though they may have been instigated by shrinking the log to an unreasonably small size.

We don't have an IT staff; what would be my to-do list?

Start with the Checklist / How to, salt to taste for your environment.