Combining Queries in SQL Server with Registered Servers

Combine Multiple SQL Server Query Results with Registered Servers

By: Jim Evans | Updated: 2021-03-05 | Comments (3) | Related: More > Central Management Servers

Problem

If you manage many SQL Servers, often you may want to run the same query on multiple servers and combine the results. One way to take advantage of this is via Registered Servers in SQL Server Management Studio (SSMS). In SSMS Registered Servers is specific to the user’s individual machine and does not depend on a designated instance of SQL Servers.

Solution

When managing many Microsoft SQL Servers, the use of Registered Servers is a time saver! For one, it provides a list of all your servers for which you have an option for saving credential. You can simply click on the server name to connect to it. Also, you can save your credentials thus eliminate the need to go through login prompts when connecting to servers. You can create multiple functional groups of servers such as a Production, Development Servers, QA Servers. You can set up groups of Audit Servers or a group for the dreaded old SQL Versions. Once you have these group defined, and the servers registered you can run queries across all the SQL Servers in the group and have your results consolidated. Let’s see how.

First Create a New Server Group in SSMS

In SQL Server Management Studio (SSMS), open Registered Servers from the View menu or by combo keys: Ctrl + Alt + G. Right click on Local Server Groups and select "New Server Group…"

Give your Group a name.

registered servers new server group properties

Register a SQL Server in Management Studio

Back in the Registered Servers pane, right click the Group name you just created. Select New Server Registration...

registered servers new server registration

Edit Server Registration in SQL Server Management Studio

Enter a SQL Server name and authentication. Optionally change the Registered Server Name to a Server name that you want Displayed in the Registered Servers list. Also, you can Add a description that will be seen if you hover over the Registered Server name.

Expand your Server Group

Here you will see the Servers that you registered. Be sure to add a few more serves as you see I have added 2 others. You can double click on the individual server names that you registered to connect to them in the Object Explorer window. If you saved the Credentials you auto connect!

Run a Multi-Server Query in SSMS

To run a query across all the servers in the group and have the results consolidated, click to highlight the Group in Registered Servers. Click "New Query" in the menu. A new query window will open and you can see at the bottom of the windows that SSMS was able to connect, in my example, to 3 out of the 3 servers from my registration group.

Run a Query with Results Consolidated

Start by running a simple query. Walla! The results from each server are consolidated and the Registered Server name is the first column.

Note that the queries will run in the default database for the login. It is a good idea to include USE master; in your queries if targeting the master database. I did not because the login I used for my Azure database has limited access.

Run multiple Queries against Multiple Servers

Run 3 different queries and see what happens.

Pretty cool, SSMS consolidates the 3 result sets separately!

Control How SSMS Handles the Results

In SSMS from the menu click Tools, then Options. In the Options pane, go to Query Results, SQL Server, Multiserver Results. Here you can control the functionality of the Multiserver Query results.

Solution - Wrap Up

Imagine the benefit to Registered Servers especially when managing many SQL Servers. I have used Registered Servers to effectively manage up 100s of SQL Servers organized in logical groups. This is a huge time saver when trying to manage inventory or pull information from all your servers. It surprises me that more people do not use Registered Servers. I often export my registered servers and share with my team. Hope you enjoy this tip. If so, please leave a comment.

Next Steps

See other tips on Registering SQL Server instances for easier management
More about SQL Server Multi Database Query with Registered Servers
Read more tips about Central Management Servers

About the author

Jim Evans is an IT Manager currently for Crowe who has managed DBA, Developer, BI and Data Management teams for over 20 years.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2021-03-05

Comments For This Article

Tuesday, October 26, 2021 - 3:02:28 PM - Randy Davis	Back To Top (89364)
Hi Jim. Great article. I discovered this "feature" several months ago and it has been a game changer! I'm wondering if you know whether it's possible to group by a column and have data from different servers merge together. Currently, everything returns grouped by Server, then by my group by columns.

Monday, August 16, 2021 - 7:21:29 PM - J. Jaya

Hi - Is a New Query the only option? Is there a way to open a saved query to be executed against a registered server group?

----- My current method is tedious and hoping for something simpler as I use these queries against in 4 isolated domains (DEV, QA, STAGE, PROD): Open saved query in a non-registered query window. Copy script. Open New Registered Query window. Paste text to New Query window. Save Registered query changes using initial query's name. Thanks for your insight.

Monday, March 29, 2021 - 9:00:00 AM - Ricardo Cardoso	Back To Top (88466)
Hi Jim. Great article! My question is: Is there a way to automate the results collect from CMS queries? A job that runs CMS T-SQL and put it into tables would be great. Thanks in advance for your help.