SQL 2005 - Maintenance Plans

I made a decision that to really get things going and identify "trouble spots" that i would continue my quick win approach.

So basically i`m working through installing and configuring our test SQL 2005 box following our SQL 2000 install instructions. Through out this approach i`m documenting and making necessary changes so i can have first drafts of install docs which i`m sure will evolve into excellent documents by the end of the project :D

So, having done the basic install which i talked about previously i decided lets tackle maintenance plans as there should not be to many surprises here.........

I have got to say that the new maintenance plans are far more flexible than ever before with alot of added functionality and over all i'd say its a job well done. However the new maintenance plans have inflicted much pain for me.

The Wizard

When i saw the maintenance plan wizard i thought to myself 'Hello old friend' and started to prepare a generic plan but through out i felt something was missing and then i clicked. I was only able to create a schedule to execute the plan at the end of the wizard and there was only 1! Surely there must be 1 for each set of tasks like SQL 2000? ie 1 for backups, one for dbcc's, one from transaction logs etc! No, just 1 schedule for what was created in the maintenance plan wizard!

Now i feel personally that this devalues the wizard significantly as we will have to run it multiple times for each item requiring a different schedule so we may as well use the maintenance plan designer!

Your probably thinking, use the designer, its more flexible and wizards are for wimps! Well, i do prefer the desginer but we will deploy to 25 countries and generally each country will do their installs from our documentation and have little or no SQL experience so wizards are good for them. Today they can follow 1 wizard and hey presto their SQL Server is being backup up, optimized etc.

Logging

Having got quite frustrated by the issue with the wizard but having accepted it i continued on. I was looking at the reporting options and then the real killer hit! The option to log the maintenance plan execution to a remote server was missing and a big black cloud arrived over my desk! Searching around and checking my books confirmed it had been removed and there appears to be no work around.

The loss of remote logging is a real blow because our 150 SQL servers log all their maintenance plan activity to one central MSDB. From this central location then run reports on which SQL servers have not run maintenance plans, who is reporting errors, which databases are not being backed up and so on. Now with the removal of this logging option our inhouse centralised solution will not work out of the box!

I've rattled around the maintenance plans and even imported them into SSIS to try and combat the problem. The issue with using SSIS is that although i can add multiple log destinations i lose all the flexibility. So right now i've not come up with a solution and have put this on my significant issues list. Part of the problem of coming up with the solution is that there are so many ways to solve it in 2005 and more than 1 technology available to do it with that i must remember to keep it simple and reliable. Since a rewrite is pretty much inevitable though i am considering expanding the new consolidation process to include other logs generated by SQL.

Maintenance Cleanup Task

I'd created the transaction log backup and set it to create subdirectories but now there is no option to delete the old backups in the transaction log task. This was quickly identified as something that has been moved to the maintenance cleanup task. The problem with this is that the maintenance cleanup task is only available in the maintenance plan designer which pretty much put the final nail in the coffin for using the wizard!

Next and most importantly, my transaction log backup is creating sub directories. However the maintenance cleanup task does not deal with sub directories!!! I`m not sure why Microsoft have removed this functionality but it seems crazy to offer the creation of files in sub directories without the ability to maintain the files in them!

To work around the problem you have to create a task for each subdirectory (not very dynamic...) or write your own script. I`ll be knocking up a script and post it in my next blog but don't expect anything elegant!  ;)

Summary

I still believe the maintenance plans have improved overall and especially the rebuild and reorganise index tasks. We don't use all the maintenance plans functionality on all servers because its more like taking a sledge hammer to crack a nut and some things are best done through scripting (for example analysing fragmentation on large tables and taking appropriate action based on the results).

The 2005 maintenance plans have proven more of a problem than i thought they would but is that not always the way? At the end of the day we will come up with better maintenance plans although they are not going to be as straight forward to create because for us, the Wizard is dead.

Until next time!