Blog

In SQL Server Management Studio, it is possible to execute a multi server query against several servers at the same time. The description from TechNet says most of it:

This topic describes how to query multiple servers at the same time in SQL Server 2012, by creating a local server group, or a Central Management Server and one or more server groups, and one or more registered servers within the groups, and then querying the complete group. The results that are returned by the query can be combined into a single results pane, or can be returned in separate results panes. The results set can include additional columns for the server name and the login that is used by the query on each server. Central Management Servers and subordinate servers can be registered by using only Windows Authentication. Servers in local server groups can be registered by using Windows Authentication or SQL Server Authentication.

Here I’ll show how to create a multi server query from a group under registered servers.

In SSMS, select “View” -> “Registered Servers”. In the windows that opens, right-click “Local Server Groups” and select “New Server Group”. Give the server group some meaningful name, and a description if you want and hit OK.

Now there will be a folder created with the name you gave the group. Right click your group and select “New Server Registration”. Enter the server name (and instance) and select Save. Repeat this for one or more additional servers. After you have added all the servers, it should look like this:

That’s the setup to start with a multi server query. Right-click the server group and select “New Query”. This open a traditional query window. You can tell the connection is a multi server connection by the pink (?) color on the status bar in the lower part of the window. It also says “Connected (N/N):

When you run a query that doesn’t return data, the return message tells you it has executed on multiple servers:

If you run a query that returns data sets, there are optional extra columns returned. The returned extra columns can be configured by choosing “Tools” -> “Options” and selecting “Query Results” -> “SQL Server” -> “Multiserver Results”:

The default behavior is to include the server name to the results, but you can also include the login name used. There is also an option to merge the results or not.

Note that the statements are executed in an isolated way per instance, they are not executed in an atomic transaction. This means that the statement can fail on one server and succeed on others. So usually best practice would be to manually start a transaction, and do a rollback in case of failure on one of the instances.