Deploying DB Maintenance plan in SQL 2005 across many different servers

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

shub - 24 Mar 2008 16:22 GMT

I am in the process of depolying a database maintenance plan tasks for
several servers. I have designed one using the Databases maintenance
plan wizard but I want to be able to replicate the same maintenance
plan for all the SQL instances in our environment. I want to avoid to
manually create them for each and every instance? If possible I want
to also avoid importing this from other servers, I am looking to see
if there is a way to script it all.
Is there a way to deploy the same database maintenance plan for all
the SQL instances in a automated fashion? What will be the most
efficient way to accomplish this?

Any help in this regard will be greatly appreciated.

Thanks

Reply to this Message

TheSQLGuru - 24 Mar 2008 16:47 GMT

Take a look at SQL Farms and see if it can help out.

Signature

Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net

>I am in the process of depolying a database maintenance plan tasks for
> several servers. I have designed one using the Databases maintenance
[quoted text clipped - 10 lines]
>
> Thanks

Reply to this Message

shub - 25 Mar 2008 14:29 GMT

> Take a look at SQL Farms and see if it can help out.
>
[quoted text clipped - 20 lines]
>
> - Show quoted text -

Thank you so much for your response. Besides this product is there any
other option to deploy database maintenance plan across different
server in SQL 2005?

Reply to this Message

TheSQLGuru - 25 Mar 2008 18:53 GMT

You may be able to generate a script for the plan (not sure about this
though) and then execute that script against each server. Seems that most
objects in SSMS can be scripted out.

I do agree with Andrew that you should consider not using maintenance plans
at all and do/control everything with your own scripts.

Signature

Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net

On Mar 24, 10:47 am, "TheSQLGuru" <kgbo...@earthlink.net> wrote:

> Take a look at SQL Farms and see if it can help out.
>
[quoted text clipped - 24 lines]
>
> - Show quoted text -

Thank you so much for your response. Besides this product is there any
other option to deploy database maintenance plan across different
server in SQL 2005?

Reply to this Message

Tibor Karaszi - 25 Mar 2008 20:54 GMT

I guess one could investigate to export the maint plan to a .dtsx file (a maint plan is an SSIS
package after all). And use that dtsx file as base for multi-server deployment. Of course, one need
to investigate how much customization of the dtsx file is needed. For instance, you don't want to
deploy such file to another server if the old server name is in there somewhere.

Signature

Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

> You may be able to generate a script for the plan (not sure about this though) and then execute
> that script against each server. Seems that most objects in SSMS can be scripted out.
[quoted text clipped - 35 lines]
> other option to deploy database maintenance plan across different
> server in SQL 2005?

Reply to this Message

Jeffrey Williams - 25 Mar 2008 21:31 GMT

On Mar 25, 12:54 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@hotmail.nomail.com> wrote:

> I guess one could investigate to export the maint plan to a .dtsx file (a maint plan is an SSIS
> package after all). And use that dtsx file as base for multi-server deployment. Of course, one need
[quoted text clipped - 51 lines]
>
> - Show quoted text -

FWIW - this does work and the only thing that needs to be changed is
the connection. The steps are:

1) Export to dtsx file
2) Open in BIDS
3) Modify the connection to the destination server
4) Import into the destination server

However, this does not import the schedules and can cause problems if
you import over an existing maintenance plan. Once the maintenance
plan has been imported, you still have to open the plan on the
destination server and modify the plan to schedule each sub-plan.

Personally, I have found that it really does not take any longer to
create a new maintenance plan manually than it does to export/modify/
import/update on each destination server.

Jeff

Reply to this Message

TheSQLGuru - 25 Mar 2008 22:38 GMT

And everytime you do something manually you run the risk of making an error
or having some setting different on different servers inadvertently. A well
tested script can be configured to set everything right each time for each
server/DB it needs to act against.

Also, you can easily script job schedules too as well as check for existence
of existing job/maintenance plan prior to stomping on it. :-)

Signature

Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net

On Mar 25, 12:54 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@hotmail.nomail.com> wrote:

> I guess one could investigate to export the maint plan to a .dtsx file (a
> maint plan is an SSIS
[quoted text clipped - 60 lines]
>
> - Show quoted text -

Reply to this Message

Andrew J. Kelly - 26 Mar 2008 00:30 GMT

In addition you may not have the same DB's on each server so unless you
chose it to do all dbs it will fail as well.

Signature

Andrew J. Kelly SQL MVP
Solid Quality Mentors

> And everytime you do something manually you run the risk of making an
> error or having some setting different on different servers inadvertently.
[quoted text clipped - 91 lines]
>
> Jeff

Reply to this Message

Andrew J. Kelly - 25 Mar 2008 15:14 GMT

That is one of the down falls of using the maintenance plans. I would create
your own scheduled jobs and custom maintenance sps so that you can script
these and do what you want with them.

Signature

Andrew J. Kelly SQL MVP
Solid Quality Mentors

>I am in the process of depolying a database maintenance plan tasks for
> several servers. I have designed one using the Databases maintenance
[quoted text clipped - 10 lines]
>
> Thanks

Reply to this Message