By Denise Crabtree, Senior Database Administrator Consultant at Taos
Want to be a more efficient a DBA? Need an easy way to manage multiple serves concurrently?
Multi-server queries is the answer.
With registered servers or a Central Management Server, it’s possible to run multi-server queries.
The first step is registering servers into logical groups. A server can be in multiple registered server groups. It’s possible to register servers by application, location, version, or development cycle.
Here are the steps for registering a server: Connect to the database instance in SQL Server Management Studio (SSMS). In object explorer: right click server name, select “register” from drop down menu. Recommendation is to register servers with Windows Authentication only.
To see the registered servers: View –> Registered Servers
Make a group name that helps you: All Production SQL Servers, Dev SQL Servers, for example. For my purpose, I named a group 00_Demo.
When a server is registered, it goes into the “Local Server Groups”. To categorize, right click à tasks à move to the correct group. Register it again to group the server different ways. An alias can be created for the server by modifying the display name in the properties window.
Once the group is created it is possible to run a multi-server query. Right Click the Group and select New Query from the menu:
The query window has some interesting things to note:
The Information Bar at the bottom of the window is pink, and it reports the number of servers in the group for successful connection, and the group name.
Running a query.
By default, multi-server queries returns the server; but that can be changed in the Options menu.
Multi-Server queries are a powerful and useful tool. This tool can help you work more effectively as a DBA in a multi-server environment.