Have you ever had to run a query on multiple servers? You could connect to each server one by one to run it. Or you could create a script with each instance in it (using the :CONNECT command – see yesterday’s tip). But these are the hard ways of doing it. The easier way is to run a multi-server query – a query where you are connected to multiple SQL Server instances from the same query window, and run that query from there. Multi-server queries were introduced in SSMS 2008.

There are two ways to accomplish this, and we will cover them both today.

Registered Servers

The first method is to use registered servers. Let’s start off by opening the registered servers windows. This is accessible from the View menu, or by using the keyboard shortcut Ctrl+Alt+G:

This open the Registered Servers window:

Notice that there are two nodes shown. There are the two ways of working with multiple servers: either by the servers that you have registered locally, or by the servers on a Central Management Server (CMS). The main difference between these two methods is that servers registered under CMS will only use windows logins, while those in the local server groups can use SQL Authentication also. By default, all of the SQL Server instances installed on the computer that you are running SSMS from will be under the Local Servers Group.

On my laptop, I have several different instances of SQL Server installed:

Double-clicking any of these servers will open it (using the saved credentials) in Object Explorer. To run a query against all of these servers, just right-click the parent node (“Local Servers” and select “New Query’ from the context menu:

By selecting New Query, you will open a new query and connect to all of the servers in this node (and any nested child nodes). The status bar will look a little bit different also:

By default, it will be pink. Notice the left side shows the number of instances that you connected to (out of how many are in the group), and the right side shows you the name of the node that you used.

Now when you run a query, you will get the results from all of the instances:

The results are displayed in the order that they were returned to SSMS. You cannot order this result set within SSMS.

Notice that there is a column named “Server Name”. This is an optional column that you can add to the results to show which instance you are connected to for those results. It will use the name as it is entered in the registration of the instance. The options for multi-server queries are shown below:

You can see that in addition to being able to include the server name, you can also include the login name used for that instance. The final option is “Merge Results”. You can see in my example that all of the results are in one results grid – they have been merged. This does require that all of the instances return the same columns with the same order and data types. If you set this option to false, each instance will return a separate result grid.

The screen shot of the context menu above shows other commands that you can do on multiple registered servers. Object Explorer will open each instance in Object Explorer, and so on.

Nesting Registered Servers

You can add a new group or new instance by using the appropriate menu option. An example of nesting is to handle the instances of different versions, where the query might work differently. For instance, Extended Events were introduced in SQL Server 2008, so if I tried to query my 2005 instance, it would produce an error.  By nesting the servers by versions, I can handle conditions where things have been added or removed. I have a versions group that looks like this:

In this manner, I can just select the node that first supports (or last supported) a feature to query about it against all of the servers.

Poor Man’s Stress Test

Another neat thing that you can do with registered servers is to create a sort of stress test of an instance by creating multiple connections to the same instance. This “poor mans’ stress test” may be able to help you detect concurrency issues. For instance, I have the following ten registered servers:

They all appear to be different, but a simple query shows that they are all the same server:

A better solution for stress testing would be to use ostress (part of the RMS Utilities) – but this method may help you find and fix some things before you do that.

Using CMS

CMS registered instances works the same way. You start off by designating a SQL Server instance as a CMS server, and then you can assign instances to it. Here my SQL2012 instance is a CMS server.

Note that the CMS instance itself is not listed. For some reason, a CMS server cannot be registered if it has the same name as what you are trying to register.

You can get around this by using a different name. For instance, here I can use (local)\SQL2012. You can also use an IP address.

The main benefit of using CMS over registered servers is that many people can connect to the CMS server to use that. Just set it up once, it’s ready to go. While you can share registered servers, you have to export them, and others have to import them.

This post is for day nine of my month-long blog series “A Month of SSMS Tips”. I have a landing page for the series at bit.ly/MonthOfSSMS. Please visit this page for an easy place to quickly view all of the other tips in this series.

The post Multi-Server Queries (Day 9) appeared first on Wayne Sheffield.