skreebydba

Central Management Server (CMS) allows a query to be executed against multiple instances of SQL Server at one time. It is a very useful tool for returning information about your environment and creating the same object on several instances. I will use one of the queries from my previous post in this example.

To set up CMS, open SQL Server Management Studio 2008 or later and click View and select Registered Servers. On the Registered Servers pane, right-click Central Management Servers and select Register Central Management Servers.

Enter a SQL Server name into the Server name field. You can test the connection using the Test button. Once you are satisfied, click Save. Your server will appear in the tree on the left.

Right-click the server name and select New Server Registration.

In the New Server Registration dialog, enter a server you want to add. After testing the connection, click Save. The registered server will appear under the Central Management Server. Repeat this for all of the servers you want to add. Also, note that you can create multiple levels in the CMS tree structure. In my environment, we have the clustered servers grouped together by both data center and environment. This allows queries to be executed against the entire environment as well as any subset of the instances.

To execute a query against multiple databases, right-click on the level in the tree you want to run against and select New Query.

A query window will open. You will notice that the status bar is pink and the lower left-hand corner will show Connected (n/n), displaying the number of instances you are connected to.

For this example, I am going to use the xp_fixeddrives query from my last post, but you can use CMS to execute any T-SQL query.

IMPORTANT NOTE: USE EXTREME CAUTION WHEN EXECUTING ANY INSERTS, UPDATES, DELETES, DROPS OR ANY OTHER POTENTIALLY DESTRUCTIVE QUERIES.

Once you click Execute, there is no going back. If you execute

DROP DATABASE master

against all of the instances in your environment, you could potentially drop every copy of master. Additionally, there is no way to control the order in which the servers return their results, so canceling a query may have unexpected results.

Once you’ve absorbed that, click execute. Your query results will return with one result set per instance. The first column in each row will always contain the instance the returning that row.

There are a few things I recommend when running CMS queries.

First, always declare a USE clause. This will prevent problems when running various system stored procedures against 2005 and 2008 systems at the same time.

Second, when running a SELECT statement, always use a column list. This will prevent problems if the number of columns returned has changed between versions of SQL Server.

Third, keep in mind CMS connects to each instance and executes your query, returning each result set individually. This means you have no control over the order the result sets return and cannot order the entire result set. If you code an ORDER BY in your query, each result set will be sorted. If I need to sort the entire result set, I will copy it into Excel and do the sorting there.

I use CMS on a daily basis to return information from my environment. It also makes creating and modifying code used in MSX/TSX jobs very simple. With a single execution I can push a new stored procedure out to every instance in my environment. Have fun playing with CMS to see what uses you can find for it.