April 8, 2002 at 12:00 am
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/awarren/sqlmaintenanceplans.asp>http://www.sqlservercentral.com/columnists/awarren/sqlmaintenanceplans.asp
April 11, 2002 at 5:29 am
A good article. My wish list for maintenance plans is that you could add steps to the generated jobs without affecting the plan definition. I know Microsoft say don't alter the generated jobs but it is so convenenient. We altered our maintplan backup jobs so that when a backup is taken we run a procedure to generate code to recover all databases (for disaster recovery puroposes) this works fine but does affect you ability to regen the maintenance plans. You can get around it (we have a scripts which can strip all of our added steps and one which will re-add them) but it's a pain.
April 11, 2002 at 5:50 am
Ah, good idea. Rather than hard coding a few extra features, make it extensible? I could go for that!
Andy
April 11, 2002 at 7:12 am
I use maintenance plans. But my pet peeve is any software that, when something breaks, provides little in the way of meaningful feedback about the cause. (As an aside, my experience with web developers as a species is their absolute refusal to include conditional displays of the commands they're sending to the database -- too much of my time is spent looking at their code and trying to intuit the actual command being sent in.)
DTS is rife with this flaw: Nine out of ten failures yield "Error 22029: The step failed." Yeah, like, "No shinola, Sherlock!" How hard would it have been for the developers to have found a way to inform the user (me, in this case) the source of the actual problem? It *knows* what the problem is, it just won't tell me.
The benefits of maintenance plans, in my mind, outweigh the irritations. But it would be better if the jobs they spawned would specify any problems they encountered. As for DTS, I don't bother with making my own packages. If something breaks, I like to know where to start fixing things.
Black boxes are fine for things that always work, which is precisely why I despise wizards: They don't always work. Sometimes, they don't even "most often" work. Just last month, I wandered into a disaster during what should have been a routine upgrade from SQL Server 7 to 2000. Point --> click, what could be easier? Only problem is, the wizard broke and henceforth resisted all of my efforts to cajole it to function correctly. (I never did discover why it broke; if the wizard knew, it wasn't sharing that knowledge with me.) At least, with a checklist and a command line, as inconvenient and error-prone as that is, you are the pilot. When a wizard is doing the work, you are the passenger, and when the wizard begins steering for the side of a mountain at full speed, all you can do is scream.
Edited by - Lee Dise on 04/11/2002 07:13:24 AM
Edited by - Lee Dise on 04/11/2002 07:42:42 AM
April 11, 2002 at 9:09 am
Good comments. The only thing I can say about DTS is that at least you can save the code as VB and step through, essentially using it as a super code generator. That usually gets you pretty close the error. I'd like to see all wizards have a code generation option - my favorite example is Excel macro's - record a sequence and it generates vanilla VBA that you can run, modify, etc.
Andy
April 11, 2002 at 9:49 am
An excellent article. As a SQL DBA that was thrown in at the deep end, it provided a way to swim, and even now that i know my way round SQL like a roadmap, I still find it far more useful.
I have to agree that better feedback would be great, some of the errors are a little esoteric, even if you have got half a mind what has just kicked up the daisies.
Finally, like the DTS, a way of saving VB code or something readable would be great, at least this would help in the documentation process.
---------------------------------------
It is by caffeine alone I set my mind in motion.
It is by the Beans of Java that thoughts acquire speed,
the hands acquire shaking, the shaking becomes a warning.
It is by caffeine alone I set my mind in motion.
April 11, 2002 at 10:08 am
SQL Server's Maintenance plan wizard is unusuable in a serious environment because it doesn't handle new databases or transaction log backups nicely.
On most production SQL servers, some databases have truncate log on checkpoint, turned on, others have them turned off. A complete backup procedure needs back up transaction logs hourly or more frequently. So it needs to look at every database, identify the log settings, and decide whether or not to back up the log.
For nightly backups, the procedure needs to backup the logs first, then do a full database backup.
And of course, the backup procedure needs to handle rotation of backup media (file or tape), ensuring that N days of backups are on-hand, but everything gets copied to tape automatically.
And it needs to log errors and send alerts when anything in the whole preocess goes amiss.
Nedless to say, I write my own scripts.
April 11, 2002 at 10:22 am
Good article, as I am not a production DBA, and find the plethora of tools really confusing.
Does the wizard only do complete backups? Should I use this for a complete backup, but then create my own scheduled job for differential? Sorry to be so dim!
April 11, 2002 at 10:50 am
It will do complete or log, not differential.
Andy
April 11, 2002 at 1:52 pm
I use the maintenance plans for simple things like backing up the logs every half hour, and full backups every 12 hours. It works great. If it fails, it calls my cell, and emails me. Last Sunday night it SQL Server called me and alerted me to a problem, I went in fix it and there was never a data problem and all users were completly unaware of anything going wrong. It worked like a charm!
- Vega
April 12, 2002 at 2:34 pm
One thing you didn't mention was the ability to use Maintenance Plans in a Multi-server administration (MSX/TSX) environment.
I've tried to carefully create a Maint Plan that can be used on new servers. When we build a new development box, we just subscribe to the MSX, then change the Maint Plan servers tab to include the new box. Bingo - backups and reorgs handled automatically, with errors sent to the MSX and paged to us from there.
It's not quite that simple, as there are bugs that prevent multi-server Maint Plans from downloading automatically. There's a Q article on it somewhere. But its still better than applying scripts manually, AND its more visible than scripts. When someone else takes over maintenance on the box, they'll notice the TSX settings on the box.
I think MS should put some resources into making this tool better. It lets us concentrate on design and programming rather than dull administration.
Vince Iacoboni
April 12, 2002 at 3:18 pm
A good article, I use maintenance plans on servers which are located in ships at sea, they provide good service and are easily changed by less experienced administrators if required. These DBAs only receive 1 days training and if major problems occur we get flown out to the vessels to cure the problems. I have no suggestions for changes but have found the current system a great bonus over the awful maintenance features that used to be in SQL 6.5.
April 12, 2002 at 3:27 pm
One of the main reasons I don't use it is the fact I use differential backups most of the time which I have coded to do the differential nightly Sunday - Friday which on Friday unloads the tape for me when done. I also have it check for possible new databases and do a full backup on them if that has not been previously done allowing me to keep all databases backed up without much interference. Saturday I just do a full backup of all databases which is code since I ran into the problem with sqlmaint.exe like everyone else and just never saw a reason to change since this has never been an issue for me using the code. Finally the other items I tend to do coded to rebuild indexes, shirnk log, shirnk database, update useage and so on. These do the larger databases which do have a backup copy elsewhere 1 day a week seperate from each other to keep times to a minimum and the smaller locally backedup databases (which are wholy owned by me and are generally replicated to another site as well) all run Friday right after the last diff of the week. I will be changing this though to do a full backup to the local D: drive Friday night then these processes and then a full backup to tape when done for time constraint and other reasons. All and all though sqlmaint has never fit my timing without having multiple jobs whereas I coded into much fewer jobs plus I can script my jobs for quickly building on other servers.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 12, 2002 at 3:46 pm
Interesting point about the multi server admin. I only have two right now, not a lot to maintain! Is the consensus that if the bugs were fixed or at least gave meaningful error messages AND included at least an option for differential we'd all use them?
Andy
April 15, 2002 at 7:13 am
I use maintenance plans as much as possible and had planned to start using MultiServer Administration until I ran into a possible complication with clustered servers. In any case we use it in our testing and development environment. Thus we have one maintenance plan that backs up all databases and we simply send that plan to all the target servers. So we know the same maintenance plan is on all the servers and when changes are needed we make them on one server and the changes go to all the others. Very convenient!
To me maintenance plans work well and we use them on all our servers.
Robert Marda
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
Viewing 15 posts - 1 through 15 (of 60 total)
You must be logged in to reply to this topic. Login to reply