Combine Multiple SQL Server Query Results with Registered Servers


By:   |   Updated: 2021-03-05   |   Comments (1)   |   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ů"

registered servers 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.

registered servers new server registration

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!

server groups list

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 query using registered servers

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.

run query using registered servers

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.

run query using registered servers

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.

multiserver results configuration
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


Last Updated: 2021-03-05


get scripts

next tip button



About the author
MSSQLTips author Jim Evans Jim Evans is an IT Manager currently with Harsco who has managed DBAs, Application and BI Developers and Data Management teams for over 20 years.

View all my tips
Related Resources



Comments For This Article




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.


download





Recommended Reading

Execute SQL Server query on multiple servers at the same time

SQL Server Central Management Servers System Tables

SQL Server Central Management Server Security

SQL Server Multi Database Query with Registered Servers

Different ways to sort multi-server SQL Server query results using Central Management Server














get free sql tips
agree to terms