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