Standards for SQL Server Configuration and Maintenance

Standardization of common SQL Server maintenance tasks is a crucial aspect of efficient management of a MS SQL environment. The more complex, large andor distributed the environment, the more this rule applies. Standardization facilitates documentation (e.g. run book), reduces the time spent getting bearings in a breakfix scenario, can smooth the hand-off of tasks between shifts, and otherwise results in an orderly and organized environment.

Standardization of common ‘best practice’ configurations and instance and database specific configurations results in optimized performance, better security, ease of enforcement and reporting, and a little peace of mind for the DBA.

Standardizing SQL Agent Jobs for Maintenance Tasks

Identifying naming conventions for regular SQL maintenance tasks might seem trivial at first glance, but the familiarity that comes along with a standard naming convention can save a DBA team time and headaches. When troubleshooting an issue, the DBA team can expect to see every instance with like-named jobs and will have some familiarity with which steps each job includes; no poking around, clicking through steps or opening obscurely named SSMS maintenance plans to see what a particular job is doing.  Furthermore, the entire set of maintenance tasks can be rolled into a single deployment script and executed as a step of new server installation, or executed to bring an existing server up to par.

As an example, here is a quick sketch of the SQL Agent Jobs and steps in a standardized maintenance layout:

• MAINTENANCE.Backup.Full (weekly)
  • Full backups
  • Copy backup files to remote target*
  • Cleanup step to remove old backups (local and remote, full, diff and log)
• MAINTENANCE.Backup.Diff*(daily)
  • Diff backups
  • Copy backup files to remote target*
• MAINTENANCE.Backup.Log*(hourly)
  • Log backups
  • Copy backup files to remote target*
• MAINTENANCE.Server (monthly)
  • Cycle error log
  • Purge msdb history
• MAINTENANCE.Database (monthly)
  • DB Consistency Checks
• MAINTENANCE.Index (weekly)
  • Index RebuildReorg
• MAINTENANCE.Stats (weekly)
  • Stats Updates
• Other custom MAINTENANCE jobs as needed

* optional

Of course, everyone will have their own ideas on the best names, schedules, and what should be included in the standard, but the important thing is consistency within the SQL Agent and across instances. Here is an example of what it looks like in SSMS – easy to read, easy to troubleshoot, easy to re-distribute:

The jobs listed above cover suggested names and steps to standardize maintenance jobs, but they do not detail what procedure each step will execute. There are a many options for procedures and processes that cover the typical maintenance needs of instance and databases, here are a few ideas:

• Custom, home-grown stored procedures for backups, indexstat maintenance, consistency checks, etc. written by the DBA team. This is a good option to keep everything in house and allow plenty of room for customization.
• Other ‘script’ solutions, Ola Hallengren’s maintenance scripts are an excellent example:
  • These scripts are much more flexible than SQL maintenance plans, allowing exclusions and thresholds, easier scheduling, as well as more details on failures.
  • They are an industry standard, most DBAs are familiar with them and documentation is readily available.
  • These scripts will handle all backup types, index maintenance, stat maintenance, and consistency checks and can be configured to log results.
  • They are free.
• Lastly, and the least ideal option, are SSMS Maintenance plans. Although awkward and inflexible (e.g. there are no ‘steps’ to isolate a failure), these can be great as an initial ‘hasty’ or stop-gap set up, to be followed later (if ever) by one of the options above.

Most of the job steps that are not backup or indexstats (e.g. cycle error log) will not require a stored procedure and can be configured as T-SQL within the job step or a T-SQL step in a SSMS maintenance plan.

Configuration Standards via Policy Based Management

Sharing the management of SQL Servers can sometimes lead to too-many-cooks-in-the-kitchen issues. Keeping with the theme of standardization, there really should be a single agreed upon ‘best’ configuration for an instance. One way to adhere to that ideal is to use SQL Policy Based Management (PBM). This offers the best option to keep tabs on instance and database configurations and bring them back into compliance if necessary. SQL Server installation includes a whole set of MS Best Practice Policies (find them here-  ..ToolsPoliciesDatabaseEngine1033). Although not all of those will apply to every situation, here are 5 from that directory that are almost no-brainers and –likely- would apply to every instance in an environment:

Auto Shrink = off

Auto Close = off

File growth in Percent = off

Page verify = CHECKSUM

Page Status = no suspect pages

These ‘general’ policies are good candidates to be executed from a central server to check for compliance across a whole SQL environment. However, since in most cases each instance is unique with its own footprint of ideal configurations, it might be preferable to deploy a set of policies to each instance and customize the thresholds for that instances specific best practices. For instance, Server A runs best under MDOP 4, Server B’s databases should all be in simple recovery model, and Server 3’s maximum memory should be 16GB: similar to the maintenance standardization discussed above, a set of default policies can be deployed to each server, then revisited to set the policy thresholds to enforce compliance for the distinct Server A, B or 3 ideal configurations.

Once put into place for each instance, the policies are evaluated on a regular schedule and standards are thereby ensured.

One convenience of PBM is that if a policy evaluation is found to fail, the SSMS tool makes it very easy to bring the object back in to compliance by choosing the failed policy’s check-boxes and clicking apply. This does not work for all policies, but for something like DBs that should be in SIMPLE it makes the resolution very quick:

Also to note; manually evaluating policies is just one way to do it. Policies evaluation can also be scheduled and even real-time enforced. See BOL for more on those options.

Don’t Wait, Assimilate

So the next time you go to troubleshoot a missing backup and find yourself with 3 different maintenance plans and two job windows open, consider sitting down and defining what a maintenance task standard would look like in your environment. Then build it, batch it, deploy. The next missing backup you’ll know just where to go.  And the next time you find an important (or even not so important) instance or database configuration out of whack, consider developing a set of policies that will work across many servers. Or get more specific and give each server their own customized set. Schedule regular evaluations and maybe gain a little peace of mind that the critical production DB is not flipped to auto-shrink.