SQL 2000 had maintenance plans and while they weren’t the most robust tool for backing up your databases (log backup in simple mode=FAIL, why not just skip it dummy?) they usually got the job done or were able to let you know that they failed.

The thing about maintenance plans that I found to be the coolest was that you could create them with stored procedures, so all you had to do was come up with a generic script to and add it to “new instance build” suite of scripts.  That way you could at least not have to be the tool that gets surprised when system databases haven’t been backed up or you get tons of disk I/O because the user databases have never had any regular maintenance run on them.

Enter SQL 2005, which I have found to be a pretty darn great product, and maintenance plans are totally hosed until SP2 was release (twice, whoops) and wouldn’t you know it, they can’t be created programmatically and more.  Since SQL 2005 maintenance plans are just a subset of SSIS packages you can create an integration services package with maintenance plan tasks and then deploy it to MSDB.  There are a few issues with this approach, mostly centered around the package configurations feature of SSIS.

Package configurations pretty much fixed everything that was wrong with DTS.  In order to promote DTS packages from one environment to another you had to open the package, change the server name, hope that you had the same database name so your data pump tasks don’t have to be changed, then save your DTS package to the PROD server with code that not only hasn’t been tested but also can’t be automated easily.  DTS cool guys might bring up INI files and some other stuff but I didn’t work with that many developers that did this except in code, and that was pretty rare.

Enter SSIS package configurations, which allow you to set package properties at run time without having to modify code.  You can do this from an XML file, an environment variable or a database table.  My favorite method is to use an XML indirectly through an environment variable that then sets the connection string of the connection manager that holds the database table configurations you want.  This works great until you have multiple instance on a server and only one environment variable for the server.  I tried using the /CONFIG switch but it didn’t seem to override the values when run from an agent job or from command prompt.  What did work was using /SET parameters to assign package properties, and this allowed for configuration in the agent job step and keep the script simple.

Combine the agent job creation with With the help of a SQLCMD script that takes parameters and a centrally stored DTSX file I was able to come up with a global maintenance plan that can be applied to a new SQL build but still able to be altered for exceptions simply by changing the values of the /SET commands.

Steps:

• Create DTSX package with maintenance plan tasks using BIDS 2005 (for backwards compatability).  In my example I set up a Backup Database and Maintenance Cleanup Task.
• Deploy DTSX file to MSDB of target instance using DTUTIL
• Change package type in msdb system tables.  This allows the package to show up in the “Maintenance Plan” section of Management Studio so it can be modified through the built in DTSX editor
• Create agent job with SSIS task using DTEXEC’s /SQL syntax and assign a schedule to it

DTUTIL Syntax:

• dtutil /Q /File “c:\BackupNightly.dtsx” /DestServer $(INSTANCE) /Copy SQL;”\Maintenance Plans\BackupNightly”

Script to update System table:

• 2005 – update msdb..sysdtspackages90 set packagetype = 6 where name IN ( ‘BackupNightly’ )
• 2008 – update msdb..sysssispackages set packagetype = 6 where name IN ( ‘BackupNightly’ )

Agent Job excerpt (the @command section should be on one line):

* @subsystem=N’SSIS’,
* @command=N’/SQL “Maintenance Plans\BackupNightly” /SERVER “$(INSTANCE)” /MAXCONCURRENT ” -1 ” /CHECKPOINTING OFF /REPORTING E
/SET “\Package.Connections[Config].Properties[ServerName];$(INSTANCE)”
/SET “\Package.Connections[Local server connection].Properties[ServerName];$(INSTANCE)”
/SET “\Package\Maintenance Cleanup Task.Properties[FileExtension];bak”
/SET “\Package\Maintenance Cleanup Task.Properties[OlderThanTimeUnits];2”
/SET “\Package\Maintenance Cleanup Task.Properties[OlderThanTimeUnitType];0”
/SET “\Package\Maintenance Cleanup Task.Properties[CleanSubFolders];False”
/SET “\Package\Maintenance Cleanup Task.Properties[FolderPath];$(BKDIR)”
/SET “\Package\Back Up Database Task.Properties[DatabaseSelectionType];1”
/SET “\Package\Back Up Database Task.Properties[DestinationAutoFolderPath];$(BKDIR)”
/SET “\Package\Back Up Database Task.Properties[VerifyIntegrity];True”
/SET “\Package\Back Up Database Task.Properties[BackupFileExtension];bak”
/SET “\Package\Back Up Database Task.Properties[BackupAction];0″‘

Like the SQL Agent scheduler the SSIS Maintenance Tasks properties take a little bit of research to decode.  For instance “DatabaseSelectionType” can be either a 1 (all), 2 (system) or 3 (user).  A lot of these properties can be decoded using this post on MSDN.

In the end SSIS maintenance plans can be a pretty powerful administration tool for automating DBA tasks on an instance, albiet a little harder to script out than SQL 2000 maintenance plans.  On the upside SSIS packages have built in error handlers that you can add custom events to which makes them a much better tool for customizing your own maintenance and alerting solution.  Additionally since DTUTIL is script-able it would be very easy to redeploy packages to all (or sleect) target servers once changes have been made, without having to re-deploy the entire build script.