Every now and then in my current role as a DBA for Hewlett-Packard a new customer signs with us and “gives us” his whole SQL Server environment. As a DBA(and in countries like Ukraine – as a Developer) we all have a number of SQL Agent Jobs that we need on all of our servers – be that related to maintenance, backups or specific ones for the customer/company. Now think about it – the reason behind us creating jobs lays in the fact that we want to automate a specific process or script, right? However, a neat feature inside SQL Server that I am stunned almost no one talks about is CMS – Centralised Management Server/System which allows you to execute scripts or evaluate policies across your whole environment with just one click! So if that’s true wouldn’t it be great if:
- I build a CMS server for my customer/organisation by adding there all of the SQL Server instances in my environment and categorizing them by whatever criteria I want (one server in your CMS can be part of more than one group)?
- Build a script (yes, just one .sql file) that will create all the jobs that I have to have in my environment?
- Execute that script from the CMS server and thus deploy all of my jobs in the whole environment with just one click?
The answer to the question above is just a simple “YES” and if you think about it you didn’t just get your maintenance jobs deployed! You just created a CMS server that you will be able to use in the future. You also created a script that you will be able to reuse(probably for your next customer or to share it with a teammate of yours). Last, but not at least your whole environment now has all the needed jobs and they follow the same naming convention (and I personally really like and strive for this). So, automate the automation – create a script that you automatically deploy to all of your servers, which by itself creates jobs that execute automatically using the schedules you have “assigned” for them!
Thanks, Hemanth, for hosting this one and looking forward to see the other posts for this month! I think #50 will be full of very clever ideas and solutions!