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.

  • I would probably use msdb rather than master, even though Ola uses master. Even better: my own utility database. As framed, though, this question isn't really a good fit for the site - there is no right answer to where you should put this data, only opinions. And it isn't really a "give me a bunch of tips and suggestions around this general idea" format. – Aaron Bertrand Aug 29 '14 at 12:25
  • We use a utility database to keep these parameters and only handle the main parameters. Include/exclude, schedule, retention, and so forth. We deploy over linked servers by either creating or updating the jobs on each server to match the current settings in our database. That way the configuration is in one place, but the jobs run on each server. – RLF Aug 29 '14 at 12:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.