in

SQLServerCentral.com

The largest free SQL Server community.

Ken Kaufman

Maintenance Plan Quirks

 

The move to maintenance plans based on SSIS was a very impressive step.  As an administrator it provides the ease and flexibility of creating and maintaining plans through an object based interface.  There are several differences in the Maintenance plans implementation of SSIS compared to the full blown product, primarily in its extensibility, as it doesn't expose all the features.  However where it beats SSIS is on the management of DBA specific tasks and ease of use.  Not to say SSIS is difficult to use, but changing and troubleshooting can be cumbersome.  If you need to change a SSIS package it requires opening up Visual Studio, making the changes then saving to the appropriate location where a job (Assuming your using jobs to schedule) expects it.  Under maintenance plans all setup and changes occur within Management Studio.  If you're trying to determine what a co-worker setup it's much easier then tracking down the package, moving to a dev environment then reviewing.

 

 

There are some quirks with SSIS which I'm not sure will be addressed with 2008, predominately in the way the Maint jobs are tied to Jobs.  If you don't understand how this works, these quirks could cause headaches.  First unlike SSIS maintenance plans are tied to sql jobs, or to be more specific sub plans.  Every Maint plan has at least one sub-plan, which is represented as a job after it's created.  If you try to delete the job, you'll get a foreign key violation, which ties the sysmaintplan_subplans and the sysjobs table.  The main oddity comes in the form of saving plans.  When you create a sub-plan it creates a new job, however when you edit a plan then save it overrides the job, by wiping out all the steps then rewriting only the single step that represents the sub plan.  In essence this doesn't allow you to append to that job, if you need other processes to occur after the maint plan runs, because every edit wipes out your appended steps.  An example of this is after backing up locally, you might want a second step to copy your data to a backup server. 

 

There are a few ways to get around this, the first is the obvious, which is put your serialized processes into the package.  The issue here is that maintenace plans is a scaled down version of SSIS, and doesn't provide the same functionality, such scripting or running executables without hacky workarounds.  The second is to put your serialized processes into a seperate job that performs these other processes, and as part of the maint plan run the execute job task that points to this new job as the last step. The issue here is it takes away your ability to separate processes.  For example in the backup/copy example, this prevents you from backing up without copying and vice-versa. 

 

My preferred solution is to call the maint plan with a hand rolled job.  The key is to remember the plan is simply an SSIS package stored in msdb, in a psuedo directory "Maintenance Plans".  To call the plan simply select the "SQL Server Intergration Service Package" type, navigate to the Maintenance Plans directory, and select your package.  Since sub plans are actually individual SSIS packages under the umbrella of the Maint plan you need to specify this package.  This is accomplished by navigating to the "Set Values" tab.  Under "Property Path" type "\package\[name of sub plan].disabled", then under the corresponding "value" type "false".  For Example if your sub plan is called sub_plan2 the path would be \package\sub_plan2.disabled.  Once in place any saving and/or editing will not effect this job since it references the plan and not the job.  The downside here is you have two jobs, the one created through Maint plans and the one hand rolled.

 

Before ending there's one more point to be made about scheduling through a separate job, which is the ability to run multiple sub plans through using the set values.  If you have multiple sub plans you want to run serially, under the default behavior you need to call one sub plan job then the other.  However using the Set values allows you to run serially within one job step by simply adding the two sub plans into the set values tab. 

   

 

Comments

No Comments
Copyright Red Gate Software
Powered by Community Server (Commercial Edition), by Telligent Systems