Tips ‘N’ Tricks – SSMS – Execute Single Query against Multiple Servers
Sometimes while doing comparing the data across different environments, we fire similar queries against different environments and compare the data in those environments to identify the differences as part of the analysis exercises. Firing same queries against different environments and comparing the data is a little inconvenient as it requires either copying the data to an excel, switching across different tabs/windows, or using split windows etc. However, SQL Server 2008 Management Studio (SSMS) offers a very great way to do this using Registered Servers feature.
Follow the below listed steps to execute a single T-SQL query against multiple servers.
To be able to fire same query against multiple servers, all those servers should either belong to a single Server Group or only those servers should be present in the Local Server Groups folder in Registered Servers window in SSMS. Refer to this tip to check out how to create Server Groups and Register Servers/Databases.
For the sake of this demonstration let us assume that you want to fire a particular query against different servers in different environments (Servers belonging to each environment are registered under different Server Groups in the below screenshot).
Right click on the Local Server Groups (you can also right-click on individual Server Groups to query all the servers in that Server Group), and select New Query from context menu.
Paste the following query into the newly opened Query Window and click on Execute.
SELECT Title, HireDate, VacationHours
WHERE EmployeeID = 5
Above query is used in this scenario to compare the Title, Hire Date, and Vacation Hours for a particular employee across different environments.
You will see the results as shown in the below screenshot.
Notice that in the above screenshot, even though only 3 columns are present in the Select List, an additional column is added by SQL Server called Server Name to indicate the name of server from which the particular record is fetched. This method can be really handy to compare simple values like this across different environments when the number of records returned by the query is just a few records in each environments.
Note: In the above screenshot, values of all the 3 columns are same in different environments since the same copy of Adventure Works exists in all these environments.
Take a look at the other Tips and Tricks in this Series on Tips, Tricks, Techniques, and Shortcuts to Improve Productivity, and Design and Coding Skills.
Posted on May 24, 2012, in Shortcuts, SQL Client Tools, SQL Server, SSMS, SSMS Tips, T-SQL, T-SQL Tips, Tips 'N' Tricks and tagged MSBI, Multi-Server Queries, Shortcuts, SQL Server, SQL Server Management Studio, SSMS, T-SQL, Tips, Tips and Tricks, Tricks. Bookmark the permalink. 1 Comment.