Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Old Forums
 CLOSED - General SQL Server
 How to migrate a maintenance plan?

Author  Topic 

DBADave
Constraint Violating Yak Guru

366 Posts

Posted - 2004-11-30 : 17:05:53
I want to migrate a maintenance plan from server A to server B (not just the jobs). What is the best way to do this?

Dave

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-11-30 : 17:07:53
Best way is not to use them. Maintenance plans are for non-DBA types. Anyway, the definitions are stored in the sysdbmaint* tables in the msdb database. You could possibly transfer the data from there.

Tara
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2004-12-01 : 00:15:00
You might be able to script it out, and then re-run the script on ServerB - you'd be able to see what you were getting too!

Kristen
Go to Top of Page

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2004-12-01 : 00:34:41
maintenance plans are ok, especially if you just want to get something running immediately. just realize that they do not scale well, and you have some very limited choices (no differential backups for example). If you have an instance with a few small databases they should work fine. If your database grow to a medium size (10gigs +), then you may run into problems.

The problems usually are multiple jobs running simultaneously and colliding with each other. This happens usually because the databses are getting too large, and your time interval between jobs is too short. Also, it is sometimes difficult to troubleshoot problems with a maintenance plan. The sqlmaint tool is also somewhat buggy and will occasionally shit the bed for no good reason. Other than that, they are great! ;)

Microsoft could (and should) make the maintenance plan wizard more robust. They definitely have not kept pace with Oracle in this area. Maybe in SQL 2005..

btw, check Tara's Blog for some scripts that you can schedule in sqlagent to take the place of using maintenance plans. They are very good and will save you a bunch of time since you won't be reinventing the wheel.





-ec
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2004-12-01 : 01:03:30
"maintenance plans are ok"

I'm moving out of that camp ...

We put maintenance plans in place for clients 'coz it was Cheap & Cheerful.

We prefer to use the "All user databases" flavour so there is no chance of a new database being created which doesn't get backed up. So we backup Database AND Logs

Maintenance plan fails if any of the databases is set to SIMPLE (backup works, fails to delete old Transaction Log backups)

Maintenance plan fails if any of the databases is set to Offline/etc.

Reorganisation fails if database cannot be put into single user mode - we get around that by having a separate maintenance plan JUST for maintenance, so that the whole thing doesn't fail!

A separate maintenance plan is needed for System databases ('coz you can't do Trans Backup on master etc. But there is an option for "System databases", so that's not too tough)

We've just put our own "Admin Toolkit" (which includes Mon-Sat DIFF backup and Sunday FULLs) on a client's box that is tight for space and released 50GB of backup space compared to Maintenance plan that was doing the equivalent (But no DIFFs of course)

Caveat Emptor I reckon!

Kristen
Go to Top of Page

DBADave
Constraint Violating Yak Guru

366 Posts

Posted - 2004-12-01 : 11:10:23
Thanks everyone for your comments. I've always written my own scripts for backups, performance tuning, etc... We've recently transitioned production support to a third-party and they are pushing maintenance plans. Any more pros and cons of maintenance plans is appreciated.

Dave
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2004-12-01 : 12:01:09
They won't clear down MSDB.

Add "read only" databases to the list above that cause Maintenance plans to fail "Can't write to it, cancel job"

Dunno whether they do a good, and full, job of Index Rebuild / Defrag and Updat Statistics, but might be worrth checking whether that is thorough.

It really looks like something a summer student wrote IMHO. An outsourced outfit who think that the maintenance plans are "good enough" would give me some cause for concern that maybe they don't know enough to save-the-day when the need arises.

Kristen
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2004-12-01 : 12:46:42
I would absolutely require them to schedule a disaster test to make sure they can recover...do you have a disaster box?

It's more fun when it's not scheduled though.

Just pull the plug



Brett

8-)
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-12-01 : 13:14:28
Have you ever had to troubleshoot why a maintenance plan has failed and all it says is sqlmaint.exe failed and there's no more help in any of the logs or job history?

Tara
Go to Top of Page

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2004-12-01 : 14:22:58
quote:
Originally posted by tduggan

Have you ever had to troubleshoot why a maintenance plan has failed and all it says is sqlmaint.exe failed and there's no more help in any of the logs or job history?

Tara



Yes, troubleshooting maintenance plans is near impossible. My point was that the MP is OK for a small installation, once things start getting more complicated (larger databases, read only database, full/bulk/simple logging, etc) it is time to switch to a more reliable and scalable solution.


-ec
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-12-01 : 14:28:39
I would say that MPs are OK for non-DBAs like in a mom and pop shop or where the developers are assuming the DBA role.

You wouldn't be caught with one of them even for a small system, would you?

Tara
Go to Top of Page

Hommer
Aged Yak Warrior

808 Posts

Posted - 2004-12-01 : 14:31:27
Could somebody repost Tara's script site link? I have seen it before, but don't know I can find it.

Issues like this used to be way above my head, now they start to make more sence. I will like to try out those scripts then recommend them to my boss.

Thanks!
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2004-12-01 : 14:36:45
Follow the WebLogs link at the top of the page, and then Tara's Log.

Kristen
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-12-01 : 14:57:51
Here's the specific db maintenance routines blog:

http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx


Tara
Go to Top of Page
   

- Advertisement -