Maintenance Plan Portability
Maintenance Plans should be scriptable, deployable and maintainable without the need to manually create/update a plan on each server/instance. Current functionality requires the DBA to manually generate a SQL Maintenance Plan one at a time for each instance in the environment. Built in SQL Maintenance Plans should be scriptable, deployable and maintainable with TSQL or in some automated fashion that will allow deployment to large enterprise environments that do not require the use of a GUI.
I agree we need something in-house instead of using 3rd party products just to ensure the DB is reliable and recoverable.
I got a few up-votes in Connect for this idea, but I can't find it now.
I would reedit this connect item as follows (under the expecations when i started in MSSQL business 17 years ago and the epic community solutions didn 't exist - yet):
- Microsoft: if you want to keep the "create maintplans stored procedures" code hidden from us. Please make Olas or tigertools Maintenance Solution available in the Toolbox with all its params and best practice advice
That would be a great leap in DB Maint and DBA community collaboration
Another one hand-in-hand: Please do not cut off any (job logging) content and replace it by '... job failed'. Not one developper does do this any more in the year 2018! Uups perhaps that would be its own connect item - sorry.
@adrian: agree to scriptable, deployable and maintainable with TSQL
Pedro Lopes commented
@sqlkiter also available in the Tiger Toolbox is https://github.com/Microsoft/tigertoolbox/tree/master/MaintenanceSolution
The Maintenance Plans are SSIS packages. You can export and import the plan via connecting to SSIS. The exported file is an xml file and you have options to encode sensitive details by password on the extract. The only issue I have with this method is scheduling, e.g. you need to script the schedule addition to run the package (or manually implement the schedule creation).
Hopefully that helps.
Ra Osolage (sqlkiter) commented
@Adrian, we've been wanting this for decades. :-)
If, by chance, this request gets squashed/ignored (again), take a look at the Ola Hallengren maintenance solution. I think you'll probably like it better than maintenance plans. Haven't met a person who didn't.
Great idea Adrian! It's such a pain to re-create maintenance plans on new server builds. Being able to script them out would certainly speed up the process and allow for some fine tuning.