Systems Engineering and RDBMS

Multi Server Queries in SQL Server 2008

Posted by decipherinfosys on September 30, 2008

SQL Server Management Studio (SSMS) in SQL Server 2008 version now has the capability to run the same command on multiple servers at the same time and the results are displayed to a single window.  This is nothing new from the perspective of third party tools that have been out there for quite some time but is something new in SSMS and is a welcome change for the DBAs/Database Developers since there are many times when we want to run the same command across instances in order to collect information or make changes.

The very first thing that one needs to do in order to make this possible is to create a server group.  On the SSMS Menu, click on View and then “Registered Servers”.  In my local server group, I have 2 instances registered:

Now, click on the server group and then click on “New Query” from the Menu and when the window opens up, type:

select  @@version

And the output will be shown in the results pane below for all the registered instances in that server group.  Here is the result output from my environment:

And as you can see from the output from above, this is backward compatible as well since I have one instance which is the SQL Server 2005 instance an the other one is a SQL Server 2008 instance.  Also, one instance can be part of more than one server group.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: