Ad hoc Automation – isn’t that an oxymoron? Isn’t it hard to automate something? That answer depends on what you mean by automation. If you want to build SSIS packages and SQL Agent jobs to comb your network and return all kinds of information about the servers and instances and track changes over time then you’re not going to knock that out before your second cup of coffee. But suppose you just need to know what the SQL Server version is for each instance you own. Or what if you are tired of cleaning AdventureWorks tables out of master and you want to set everybody’s default database to tempdb? These tasks are a slice of pastry using the multi-server query feature of SQL Server’s Central Management Servers (CMS).
I’m not going to go over the details of how to set up a CMS as Kimberly Tripp has already done a great job. Instead I’d like to focus on some of the things you can do with them to automate repetitive tasks.
SQL Server CMS allows you to interact with a group of servers much as you would with a single server. The grouping of servers is entirely up to you. You can have as many groups as you wish and you can configure them as you please. This is important because, with a little thought you can slice and dice your environment by version (2K, 2005, 2008), by environment (Dev, Test, Prod), by function (Log Shipping, Replication, Clustered) and so on. This allows you to customize your code and multiply the power of your CMS.
For example, suppose you have a server discovery query that returns SQL server metadata for an instance like this:
Wouldn’t it be handy to be able to generate that same information for every server in your enterprise in a single, Excel ready, recordset!
At one client site we decided to make every login use tempdb for their default DB. Connecting to each of the 100+ instances and doing this manually didn’t appeal to me so I wrote and tested a TSQL script to do it on a single server, connected to every server at once, and made the change in about 2 minutes. Notice I mentioned that I TESTED the script. Remember, as Uncle Ben reminds us “with great power comes great responsibility.”
I believe SQL Server’s CMS is a very useful but under-appreciated feature limited only by your creativity.