In previous posts we’ve covered how to setup registered servers inside Management Studio and some of the benefits of using them. The most popular use is that by having registered servers gives we have the ability to query multiple servers at the same time, which is really useful for a wide range administration tasks.
I’m going to use the same server group that was created in the last post.
I can query either server separately by right clicking on the registered server name and selecting New Query, just as we would on a normal everyday connection in Management Studio however we have the same ability on our server groups so now I’m going to right click on my Local Test group and select New Query.
This opens a new query window as normal but whatever command I execute will also be ran on all servers in the group. There’s an obvious danger here and it might be tempting to use this feature to issue updates, deletes or even drop object commands across multiple servers which I always advise against.
To keep things nice and safe I’ll run SELECT @@VERSION to return version information for all servers in my server group:
This has ran the SELECT @@VERSION command across both of the servers in our group. Note the Server Name column isn’t the actual name of our SQL Server but the name we specified for our registered server, we can easily add that in to the command though (and give the returned columns an alias too):
If we have the same database, such as system databases, across our servers we can also query our instances in the same way. If I execute the USE command which will switch database context, such as the following:
However trying the same command with a different database that only exists on one of my local instances means I will get an error:
In this case the database does not exist on my SQL Express instance so the USE statement has caused an error. This can also happen if our command contains database objects such as tables or views that do not exist across all servers. In this example I’ll try to query the sys.dm_server_services DMV that was introduced in SQL Server 2016 and won’t be valid on my 2012 Express install.
However, the query has completed successfully on my Local Default Instance and I can view the query results by switching from the Messages tab to Results.
Being able to query multiple servers at once is very useful for a DBA, the above examples are very simple but they can easily be extended to checking items like backup job histories or SQL Agent alerts across large number of servers. We have to be careful in ensuring our queries are valid and try to avoid using cross server updates (especially deletes) where the results may not be intentional.
A critical factor in the usefulness of registered servers is keeping all of the instances up to date. Servers can change; they can be migrated to a newer build, removed completely or be upgraded and new instances should also be added during the installation/provisioning process.