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".
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:
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:
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:
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!