SQL Tip: Multi Server queries

My coworkers ask me from time to time to “do that magical thing you do” and then ask me to share with them how to do it. I have showed it several times…so now I’m going to blog it for reference. 🙂

That “magical” thing I do is pretty simple and stems from me being lazy. If I want to get some information from multiple servers rather than connect to each server and run the query separately, I use multi server queries to query all of them at the same time. I have some reports that use linked servers to accomplish similar functionality but if I’m just issuing a query from SSMS then building the linked server query is more work than it’s worth. Again, I’m lazy and that sounds like too much work. So how do you run a multi server query? It’s really simple and easy – you need to use “Registered Servers”. (You could use a “Central Management Server” but let’s assume that you’re not the DBA who can make that happen). And, even if you’re not going to use multi server queries, if you’re not using Registered Servers hopefully this tip will help you start using it – it’s a great feature of SSMS.

This isn’t intended to be a ‘how-to’ on Registered Servers, but in case you’re not aware of it I’ll get a couple basics out there to help you be able to follow along with the real point of this post. If you don’t already see “Registered Servers” when you open SSMS, go to the “View” menu and choose “Registered Servers”. Now that you have it you can dock it wherever you like – I personally like to have it docked with “Object Explorer” so that they are tabs. That’s it; now you’re up to speed. 🙂 Here’s a link to a quick summary of “Registered Servers”. Basically, it’s a great way to manage all your server connections. Do it.

Now, if you haven’t already done so, you’ll need to create some server groups and/or registrations. It’s pretty simple to add these via the UI, or…you can use a PowerShell script I wrote that will create groups and servers from txt or csv files (if you want to be able to do it really quickly). I wrote another blog on this script here.

Once you have at least one group created with more than one server, you can simply right click on the group and choose “New Query”. This will open a query window that will connect to each server in the group, and you can query each server in one shot! You’ll also notice that the status bar for the query window will be a different color. You can determine the color in “Tools”–>”Options”–>”Text Editor”–>”Editor Tab and Status Bar”–>”Group connections”. You can also manage the multi server query results in “Tools”–>”Options”–>”Query Results”–>”SQL Server”–>”Multiserver Results”. I like to set “Merge results” and “Add server name to the results” to “True”.

Something I haven’t mentioned yet is that you’re only going to see the databases that exist in all server instances you are connected to (i.e., if you try to change your database via the drop down):

So, if you’re trying to query all your ConfigMgr (SCCM) databases, which have a different database name for each site, you’re either going to have to make your query dynamic (to run against the correct database name) or be smarter about creating your server registrations. The easiest way to do this is to define the SCCM servers to connect to the CM database as the default database. If you do this, then when you choose “New Query” on the group you’ll see “Multiple” in the name of the database you’re connected to. That’s exactly what you want:

You do that by specifying the “Connect to database” option under the “New Server Registration” “Connection Properties”:

Let’s say that I wanted to know what each SCCM site’s “site range” was. I could open a new query against my “CM Virtual Names – CMDB” group (which connects to the specific CM database for each site) and run/get the following:

Or, let’s say I have a group for all my SQL Servers and I wanted to know the compatibility level of the CM database (if there is one). I’d do this:

Whew, they’re all the same except for the secondary sites. Oh, and running that example also reminded me that before you run your query you’ll be able to see if you were able to connect to all the servers in the group. Sometimes you can’t connect to all of them for some reason. You’ll see this in the status bar:

And when you run your query you’ll get some “errors” in the messages window:

Pretty sick right? I actually use this more often than you might think. Want to know the exact version of SQL running on your servers? Want to know a particular setting on each server? Create a group named “All SQL Servers” and you’re able to do that really quickly and easily!

2 thoughts on “SQL Tip: Multi Server queries

  1. Hi Benjamin!

    How do you actually run a query on Registered Servers using Powershell? Connection strings that you are using to connect to Registered Servers only?I’ve never done this before as I always connect to the Database first via Powershell and run some queries on that DB Server.


    1. Hi Mitch, I don’t use PowerShell to connect to the registered servers but rather SQL Server Management Studio (SSMS). I do have a tool that uses PowerShell to create the registered servers but that’s all it is used for. Doing this in PS you’d have to connect to each server and run the commands against each – if creating a resultset (datatable or dataset) you’d have to loop through each datatable returned from each server and merge the information into one datatable/set. SSMS essentially does all this for you if using SQL which is why it can be a very handy and easy tool to use.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s