I got fed up with creating maintenance plans on several SQL servers. So I've devised a script that generate a series of SQL Agent Jobs (with notifications) that utilises Ola Hallengren's SQL Server Maintenance Solution (http://ola.hallengren.com). It has a few configuration options like time of full backups and how often to run the transaction log backups etc.
I've tried to write it in a way that means I can update fairly easily (damn you typos!!) and then reapply to SQL instances whilst retaining the existing configuration from when it was first run. It's basic but it works.
I'd like to take it up a notch though and store that config, perhaps in a table. That however generates more sql in my script in maintaining it e.g. creating, updating values etc, but I think it would help in the long run.
Does anyone have any tips or suggestions? I'm wary of storing things in master/msdb but these seem to be the most sensible place. Is it really that bad? Indeed the default for Ola's Maintenance Solution is the master db.
Thanks in advance :)
EDIT: I guess the questions I am asking here are:
- Should I be using the master or msdb to store configuration for my bespoke maintenance solution?
- If not, where is best place to store the configuration? In a separate database? A config fie?
I'm keen that this works on an instance level rather than something central so that it can be used in a number of different scenarios.