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:
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.