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.