Deploy and Schedule SQL Maintenance

Perhaps the most fundamental DBA task is to configure proper SQL maintenance on all of your instances.  There are many ways to approach this, including SSIS-based GUI-driven maintenance plans, custom homebrewed T-SQL scripts, PowerShell scheduled jobs, or third-party applications.  However, my (and many other’s) preferred solution is to use Ola Hallengren’s fantastic maintenance scripts (ola.hallengren.com).  This solution is recommended by many people for good reason.  It provides a good amount of flexibility, compatibility, as well as a solid amount of logging.  You can use this solution right out of the box and it will work great, but I like to tweak it a bit to better fit my needs and be a little easier to apply.

Here are a few things I set out to change:

1. The traditional method of installing this plan involves allowing the script to create its logging table and required stored procedures in the master database.  I prefer to contain all my maintenance data in a separate utility database on each of my instances, and leave master alone.  This is a fairly simple change to make, only requiring the creation of this maintenance database and a change to the USE statement in Ola’s script.
2. The names used in the job creation part of Ola’s script are long, and don’t sort alphabetically as well as I’d like.  I ended up creating a new script to create the jobs with settings and names to my specification.  This has the added benefit of separating my code from any updates Ola may make to his, so I can keep things consistent.
3. There are no schedules assigned to the jobs with Ola’s script.  In order to keep all my naming and timing consistent, I scripted out the creation and attachment of my preferred schedules.  I use three staggered schedules for my environments, dev, test, and prod, to keep the load somewhat distributed.
4. Manually applying the solution to each instance is tedious and time consuming,  so I created a PowerShell wrapper that would not only install and configure the maintenance plan, but also install all the handy stored procedures I like to use into my utility database.

Since this is a multi-script solution, I’m not going to display all the code here.  Instead, I will just call out what each particular component does, as well as where changes can be made to fit your needs.

• Creation of the utility database: Database Creation.sql
  • Modify this with any options you would like for your utility database.  Mine uses all defaults from model, then changes the owner to sa.
• Creation of the stored procedures: Stored Procedures
  • Modify these to reflect your database name
  • These are the ones I like, but the deployment script will loop through all *.sql files you put in this directory so add whatever you’d like:
    • Sp_whoisactive: http://sqlblog.com/blogs/adam_machanic/archive/2012/03/22/released-who-is-active-v11-11.aspx
    • Sp_Blitz: http://www.brentozar.com/blitz/
    • Sp_BlitzIndex: http://www.brentozar.com/blitzindex/
    • Sp_BlitzCache: http://www.brentozar.com/blitzcache/
    • Sp_AskBrent: http://www.brentozar.com/askbrent/
• Creation of Ola Hallengren’s stored procedures: MaintenanceSolution.sql
  • Get the latest from ola.hallengren.com
  • Modify to reflect your database name
  • Job creation is turned off
• Creation of SQL Agent jobs: Job Creation.sql
  • This is where most of the configuration is done
  • Set your backup destination, cleanup time in hours, and database name
  • You can also modify the parameters for the jobs here.  I will outline my settings later on.
• Scheduling of SQL Agent jobs: Schedules
  • One for each environment, modify these to fit your needs

For now, most of the heavy lifting (creating and scheduling the jobs) is done through T-SQL, although I may end up moving that to PowerShell using SMO eventually.

Here is how I have my jobs and schedules configured (these are the settings you will get without modifying anything):

• Logging table and location of stored procedures: DBAUtility
• System Backups
  • Full: Maintenance – Backups – SYSTEM – FULL
    • Dev: Daily @ 7:30PM
    • Test: Daily @ 8:30PM
    • Prod: Daily @ 9:30PM
  • Cleanup: 168 hrs (1 week)
• User Backups
  • Full: Maintenance – Backups – USER – FULL
    • Dev: Sunday @ 4AM
    • Test: Sunday @ 2AM
    • Prod: Daily @ 12AM
  • Diff: Maintenance – Backups – USER – DIFF
    • Dev: MTWThFSa @ 4AM
    • Test: MTWThFSa @ 2AM
    • Prod: Not in use
  • Log: Maintenance – Backups – USER – LOG
    • Dev: Hourly 6AM – 2AM
    • Test: Hourly 4AM – 12AM
    • Prod: Hourly 2AM – 10PM
  • Cleanup: 168 hrs (1 week)
• SystemDBCCCheckDBs
  • Maintenance-DatabaseIntegrityCheck – SYSTEM
    • Dev: Daily @ 7PM
    • Test: Daily @ 8PM
    • Prod: Daily @ 9PM
• UserDBCCCheckDBs
  • Maintenance-DatabaseIntegrityCheck – USER
    • Dev: Sunday @ 3AM
    • Test: Sunday @ 1AM
    • Prod: MWF @ 10PM
• User Index Optimization
  • Maintenance-IndexOptimize – USER
    • Dev: Sunday @ 2AM
    • Test: Sunday @ 12AM
    • Prod: TThSa @ 10PM
  • Reorganize or Rebuild Online when fragmentation is between 10% and 50%
  • Rebuild Online or Rebuild Offline when fragmentation is >50%
  • Rebuilds all modified statistics
• Cleanup Tasks:
  • Maintenance – CommandLog Cleanup
  • Maintenance – Output File Cleanup
  • Maintenance – sp_delete_backuphistory
  • Maintenance – sp_purge_jobhistory
  • All scheduled for Sunday @11PM

One thing to note is by default these jobs are only set to write to the event log when the job fails.  If you would like them to notify you differently, you will need to configure the notification settings for each job.  I may eventually automate this part as well, but for now my monitoring system handles failed job notifications.

Once I have finalized this into a format that is easier to use, I will post it on ScriptCenter.  In the meantime, the root of this project can be found on GitHub.