solving sql server problems for millions of dbas and developers since 2006
join MSSQLTips for free SQL Server tips














































Run SQL Commands Against Multiple SQL Servers Without CMS

MSSQLTips author Tim Ford By:   |   Read Comments (5)   |   Related Tips: 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:

execute a query across the entire group of SQL instances in the group

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:

SELECT @@version;

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:

 identify the SQL instance and database you're executing against

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:

identify the instance and database in the lower right corner of the query window

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:

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:

we can create a subgroup (and a subgroup of a subgroup and so-forth) in the Registered Servers Explorer

SELECT @@version;

All this is capable without a Central Management Server

All this is capable without a Central Management Server.

Next Steps


Last Update: 8/10/2010


About the author
MSSQLTips author Tim Ford
Tim Ford is a Senior Database Administrator and SQL Server MVP. He has been working with SQL Server since 1999.

View all my tips


print tip Print  
Become a paid author




Recommended For You








Comments and Feedback:
Tuesday, August 10, 2010 - 9:49:12 AM - Tony Henley Read The Tip
Two questions:

1) If you can do this with Local Server Groups why set up CMS?

2) Is there a way you can do this via command-line?


Tuesday, August 10, 2010 - 12:33:19 PM - Brad Marwood Read The Tip
I would also like to do this using a job or even powershell if there is no other way.


Wednesday, August 18, 2010 - 7:58:08 AM - ryan m Read The Tip
is this for SQL 2008 only?


Wednesday, August 18, 2010 - 8:04:48 AM - Brad Marwood Read The Tip
I am running it from 08 to a selection, but would be happy if i could do so for all 08s even.


Friday, February 18, 2011 - 3:10:18 PM - Cathy Sherwood Read The Tip

I don't see the query option when I right click. What is the minium version I am running Microsoft SQL Server Management Studio      9.00.1399.00



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates

Signup for our newsletter


Comments
*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.