Shortcut: Multi-server queries in SQL Server Management Studio

In an earlier post, I mentioned that you can create a registered list of servers, either in Local Server Groups or stored in a Central Management Server.

What I didn’t really talk about though, is what you can do with these groups of servers, rather than just executing queries on an individual server.

I’ve created three local server groups, for my development, UAT, and production servers.

The Development Servers group has three database servers in it. If I right-click the group, rather than any individual server, we get these options:

I’ll talk about policies another day but notice that you can import or check (evaluate) policies across a whole group of servers.

But the option that interests me today is the New Query option. When you click this, it opens a query window for the group of servers.

The window color at the bottom has changed from the default, and in the bottom right, we can see that the window is connected to the local server group:

If I type the query SELECT @@VERSION; and click Execute, I see this:

Even though this looks like a single result set, this is just a SQL Server Management Studio (SSMS) trick. Under the covers, it has run the query individually against each server. It has just presented the results to us as though they are a single set of results.

We can configure several things about how this happens. In Tools, Options, Query Results, SQL Server, Multi-server Results, we have these options:

We could add a column that shows our login name for each server to the results. We could remove the server name if required, although that doesn’t seem very useful. And we could choose to not have the results merged. If we do that, SSMS returns a separate result set for each server.

You can also change the color of the bar below, in this location:

Multi-server queries were an interesting addition to SSMS. They are useful for a relatively small number of servers. As the number of servers increases, they would become more fragile, and you might want to consider using a 3rd party tool that works out which servers have or haven’t had the query run, retry options, etc.

2018-11-01