Run SQL Commands Against Multiple SQL Servers Without CMS

By: Tim Ford   |   Updated: 2010-08-10   |   Comments (6)   |   Related: 1 | 2 | 3 | 4 | More > Central Management Servers

Problem

I understand that I can use Central Management Servers to run a T/SQL query against multiple instances at once - can I do this without CMS though?

Solution

Absolutely. You don't need to set up and configure CMS in order to run a query against more than one SQL instance. This is an option by using your Local Server Groups you probably already have configured in your Registered Servers Explorer window inside of SQL Server Management Studio. Just in case you don't utilize the Registered Servers Explorer, there is a nice tip from MSSQLTips.com on creating a Local Server Group in SQL Server Management Studio.

Once you have a Local Server Group in Registerd Servers Explorer you can execute a query across the entire group of SQL instances in the group - granted that the instance is online and the current security context has appropriate rights to each SQL Server instance. How do you do this? The infamous right-click in Microsoft Windows functionality. Let's take a closer look:

On my local instance I have two server instances registered. Note that these instances do not need to reside on the same physical server. In this example you'll note that I did not have any local groups of servers created - simply individual instances. By right-clicking on Local Server Groups and selecting New Query a new query window will be displayed in SSMS and will react as any query window would. To demonstrate the functionality in this tip I'll run a simple query to return the version of SQL Server on the individual instance:

Before executing any query you should always identify the SQL instance and database you're executing against. This holds true here as well. The database in question will be identified here:

Alternately, you can identify the instance and database in the lower right corner of the query window. The active instance in this case is a group - not a specific instance:

The behavior of the output of the query when running against multiple instances is slightly different than a single instance. You'll notice below that it also includes a column correlated to the instance the query ran against:

Conversely, we can create a subgroup (and a subgroup of a subgroup and so-forth) in the Registered Servers Explorer for narrowing down the execution scope. The behavior is identical if I add another instance (Gandalf), a new Local Group (GroupX) and then run the same command against just GroupX:

All this is capable without a Central Management Server.

Next Steps

• Learn about using CMS servers to execute a query across multiple SQL Server instances.
• Learn how to register SQL Server instances in SQL Server Management Studio
• The author blogs about SQL Server topics at his personal blog: thesqlagentman.com.

About the author

Tim Ford is a Senior Database Administrator with MindBody.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips