Deploying DB Maintenance plan in SQL 2005 across many different

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.
ThanksTake a look at SQL Farms and see if it can help out.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"shub" <shubtech@.gmail.com> wrote in message
news:da1295e1-0f53-4b03-8a70-d3ca9903c812@.d21g2000prf.googlegroups.com...
>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|||On Mar 24, 10:47=A0am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> Take a look at SQL Farms and see if it can help out.
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
> "shub" <shubt...@.gmail.com> wrote in message
> news:da1295e1-0f53-4b03-8a70-d3ca9903c812@.d21g2000prf.googlegroups.com...
>
> >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. =A0I 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? =A0What will be the most
> > efficient way to accomplish this?
> > Any help in this regard will be greatly appreciated.
> > Thanks- Hide quoted text -
> - 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?|||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.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"shub" <shubtech@.gmail.com> wrote in message
news:da1295e1-0f53-4b03-8a70-d3ca9903c812@.d21g2000prf.googlegroups.com...
>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|||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.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"shub" <shubtech@.gmail.com> wrote in message
news:20611010-8c52-4718-982c-4f4400a0b6bc@.s12g2000prg.googlegroups.com...
On Mar 24, 10:47 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> Take a look at SQL Farms and see if it can help out.
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
> "shub" <shubt...@.gmail.com> wrote in message
> news:da1295e1-0f53-4b03-8a70-d3ca9903c812@.d21g2000prf.googlegroups.com...
>
> >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- Hide quoted text -
> - 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?|||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.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message news:13uievj7t0rja8e@.corp.supernews.com...
> 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.
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "shub" <shubtech@.gmail.com> wrote in message
> news:20611010-8c52-4718-982c-4f4400a0b6bc@.s12g2000prg.googlegroups.com...
> On Mar 24, 10:47 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
>> Take a look at SQL Farms and see if it can help out.
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>> "shub" <shubt...@.gmail.com> wrote in message
>> news:da1295e1-0f53-4b03-8a70-d3ca9903c812@.d21g2000prf.googlegroups.com...
>>
>> >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- Hide quoted text -
>> - 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?
>|||On Mar 25, 12:54=A0pm, "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 deploy=ment. Of course, one need
> to investigate how much customization of the dtsx file is needed. For inst=ance, you don't want to
> deploy such file to another server if the old server name is in there some=where.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asph=
ttp://sqlblog.com/blogs/tibor_karaszi
>
> "TheSQLGuru" <kgbo...@.earthlink.net> wrote in messagenews:13uievj7t0rja8e@.=corp.supernews.com...
> > You may be able to generate a script for the plan (not sure about this t=hough) and then execute
> > that script against each server. =A0Seems that most objects in SSMS can =be scripted out.
> > I do agree with Andrew that you should consider not using maintenance pl=ans at all and do/control
> > everything with your own scripts.
> > --
> > Kevin G. Boles
> > Indicium Resources, Inc.
> > SQL Server MVP
> > kgboles a earthlink dt net
> > "shub" <shubt...@.gmail.com> wrote in message
> >news:20611010-8c52-4718-982c-4f4400a0b6bc@.s12g2000prg.googlegroups.com...=
> > On Mar 24, 10:47 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> >> Take a look at SQL Farms and see if it can help out.
> >> --
> >> Kevin G. Boles
> >> Indicium Resources, Inc.
> >> SQL Server MVP
> >> kgboles a earthlink dt net
> >> "shub" <shubt...@.gmail.com> wrote in message
> >>news:da1295e1-0f53-4b03-8a70-d3ca9903c812@.d21g2000prf.googlegroups.com..=.
> >> >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- Hide quoted text -
> >> - 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... Hide quoted text -
> - 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|||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. :-)
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Jeffrey Williams" <jeff.williams@.sharp.com> wrote in message
news:9b46cf7e-7d44-4473-acdc-458d33c7cb15@.e10g2000prf.googlegroups.com...
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
> 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.
> --
> Tibor Karaszi, SQL Server
> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>
> "TheSQLGuru" <kgbo...@.earthlink.net> wrote in
> messagenews:13uievj7t0rja8e@.corp.supernews.com...
> > 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.
> > --
> > Kevin G. Boles
> > Indicium Resources, Inc.
> > SQL Server MVP
> > kgboles a earthlink dt net
> > "shub" <shubt...@.gmail.com> wrote in message
> >news:20611010-8c52-4718-982c-4f4400a0b6bc@.s12g2000prg.googlegroups.com...
> > On Mar 24, 10:47 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> >> Take a look at SQL Farms and see if it can help out.
> >> --
> >> Kevin G. Boles
> >> Indicium Resources, Inc.
> >> SQL Server MVP
> >> kgboles a earthlink dt net
> >> "shub" <shubt...@.gmail.com> wrote in message
> >>news:da1295e1-0f53-4b03-8a70-d3ca9903c812@.d21g2000prf.googlegroups.com...
> >> >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- Hide quoted text -
> >> - 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... Hide quoted text -
> - 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|||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.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13uis61kv2i4n92@.corp.supernews.com...
> 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. :-)
>
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "Jeffrey Williams" <jeff.williams@.sharp.com> wrote in message
> news:9b46cf7e-7d44-4473-acdc-458d33c7cb15@.e10g2000prf.googlegroups.com...
> 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
>> 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.
>> --
>> Tibor Karaszi, SQL Server
>> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>>
>> "TheSQLGuru" <kgbo...@.earthlink.net> wrote in
>> messagenews:13uievj7t0rja8e@.corp.supernews.com...
>> > 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.
>> > --
>> > Kevin G. Boles
>> > Indicium Resources, Inc.
>> > SQL Server MVP
>> > kgboles a earthlink dt net
>> > "shub" <shubt...@.gmail.com> wrote in message
>> >news:20611010-8c52-4718-982c-4f4400a0b6bc@.s12g2000prg.googlegroups.com...
>> > On Mar 24, 10:47 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
>> >> Take a look at SQL Farms and see if it can help out.
>> >> --
>> >> Kevin G. Boles
>> >> Indicium Resources, Inc.
>> >> SQL Server MVP
>> >> kgboles a earthlink dt net
>> >> "shub" <shubt...@.gmail.com> wrote in message
>> >>news:da1295e1-0f53-4b03-8a70-d3ca9903c812@.d21g2000prf.googlegroups.com...
>> >> >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- Hide quoted text -
>> >> - 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... Hide quoted text -
>> - 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
>