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
FROM AdventureWorks.HumanResources.Employee
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.

image

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.

Advertisements

About Dattatrey Sindol (Datta)

Datta is a Microsoft BI Enthusiast, passionate developer, and a blogger. View Full Profile

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 , , , , , , , , , . Bookmark the permalink. 1 Comment.

What are your thoughts?

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: