I’m going to show how to use a new feature in SQL Server 2008 Management Studio: the ability to query groups of servers simultaneously.
In Registered Servers, right-click on a group of servers and click New Query. What comes up will look like a normal query window, but pay close attention to the very bottom of the screen after we run a query:
“Group L” shows the name of my registered server group. I keep my lab servers in Group L for Lab.
“LAB\Administrator” is the login that was used for authentication. (Okay, you caught me, I’m logging in as the domain admin. Thank goodness this isn’t a blog post about security.)
“Master” is the database, of course, and it would make sense to use Master because it’s one of the few databases we know exist on every server.
“6 rows” is obviously the number of rows the query returned, but let’s take a look at the query and the number of rows:
I ran a “SELECT GETDATE()” against this group of servers, and SQL Server Management Studio did the hard work for me: it connected to every SQL Server in the group, ran that query, and then combined the results back into a single results grid. It automatically added a “Server Name” column at the beginning of my results to identify which server (and which instance) the results came from. If we ran a query that returned multiple rows per server, that would work fine too.
This query would be useful if we wanted to check the dates on all of our servers, but let’s be honest – that’s not a big problem for us database administrators. We’re geniuses.
Taking a SQL Server Inventory with SSMS 2008
Let’s go tackle a harder problem, like taking an inventory:
In the above example, I’ve grabbed the SQL Server version for each of my instances. Now we’re starting to get somewhere, but what we really want is the kind of detailed information we can get from sp_configure, so let’s see how that looks:
Now we’re cookin’ with gas – but wait. Look at that first column. We’re getting lots of data back, multiple rows for each server, and it’s hard to compare this data back and forth. For example, maybe I want to see whether “allow updates” is turned on for all of my servers – so I should probably sort by “name”, right?
Can’t Really Order By with SSMS 2008 Group Execute
Uh oh – turns out that doesn’t work. Remember, SQL Server Management Studio simply runs this same query on every single instance, then dumps the data into the SSMS results window. It doesn’t process the query results together. You can’t “join” between tables on different servers – well, you can, but you have to set up linked servers, and that doesn’t really have anything to do with the Group Execute functionality.
Another thing you’ll want to do is take this data and insert it into a table. Again, no dice there – if you try to insert this into a temp table like this:
SELECT @@SERVERNAME INTO #MyServerList
SSMS will execute that query on every single server, so every server will end up with its own temporary table with one record in it. Not exactly what you wanted.
Next up on our feature list: the ability to schedule these multi-server queries and take action on the results. I might want to run a query every night checking for failed jobs or sp_configure changes. Again, not going to happen – this can’t be automated with a SQL Server Agent job. This functionality only lives in SQL Server Management Studio.
Be Aware of Case Sensitive Collations
One more thing you want to be aware of: if any of your servers are case-sensitive, then you need to write all of your group execute queries in the proper case. I recommend that if you’ve got any case-sensitive instances, then use a case-sensitive instance as your personal testbed server like on your workstation. That way, as you’re writing new utility queries, you’ll know for sure that they’ll succeed on your case-sensitive instances. It’s a real pain to bang out a hundred-line utility query only to find out you’ve got case errors all over the place when you try to execute it.
It might sound like I’m down on this feature, but I’m not: it’s really useful. I love using it to quickly find out a piece of information across lots of servers, like find out if there’s any locking or deadlocks going on. It’s also really useful if you’ve implemented a Central Management Server as a centralized list of your instances. But it’s not a solution for automated reporting or proactive reporting.
Worry not – SQL Server 2008 has a different new feature specifically aimed at automated, proactive management: Policy-Based Management. More on that later.
29 Comments. Leave new
Cool quicky!
It looks like you can only issue queries against the system databases, is this correct? I would love to be able to query multiple production databases at once instead of having to manage that with threads in c# code…
No, it works on user databases too. Remember to fully prefix your objects like databasename.schemaname.tablename.
Brent,
I tried using this on about 188 servers together. The query window was not able to connect to 45 of them. Can’t figure how to handle them. I do can connect to those normally thru Management studio.
Any ideas???????
Kumar.
Hi, Kumar. Are you able to connect to them through Windows authentication? What errors do you get during the CMS connection? Have you tried running a Profiler trace on the ones you can’t connect to, and see why?
Windows Authentication “Yes”
Errors received while opening the group execute query window : None
Errors received while executing a query:
An error occurred while executing batch. Error message is: The server is not connected.
Profiler trace not yet. Do you suggest that is the best way of knowing the problem?
I did try increasing the connection time out from normal 15 secs to 60 secs and i was able to connect to 152 of 188.
Yes, I’d take the Profiler trace route.
OK.. i will do that and try to analyze.
Thanks for prompt replies.
Kumar.
images are failing to load. Regardless, article was super useful
How can you run this as a job? I would like to kick off task that runs a query on the servers(50)+. Looking like SSIS for this task
Unfortunately, it’s client-side, so you can’t directly run it as a job. You could use PowerShell or SSIS for it though.
Thanks for the info. I’ve setup group queries previously, but I was trying to determine how to schedule these queries. Obviously, since this is completely client-side – we can’t do this.
If I want to schedule a job that queries multiple servers for last successful backups – I’m assuming my best bet is configuring linked servers?
Linked servers is one way, or you could use openrowset, PowerShell, or a third party monitoring tool. Generally I find that once I start monitoring for things, I want to monitor for a lot more things – so I want to use the most scalable way of doing this possible. To get started fast, I really like third party monitoring tools for this instead.
Hi Brent, i can’t understand the group by in ms sql server, so please tell me Worked on easiest way.
First try reading the Books Online page, and then let me know what questions you’ve got:
http://technet.microsoft.com/en-us/library/ms177673.aspx
Hi Brent, what is stored procedure and how to learn stored procedure in ms sql server?
Nambuselvam – that’s another great question to ask Books Online. Books Online is the online manual for SQL Server and you can browse it completely free. Give it a shot and you’ll be amazed at the things you can learn about in the manual. It’s really deep since SQL Server has been out for more than a decade, and the documentation has constantly been improving. Enjoy the journey!
thanks Brent. yesterday,you told the website was very useful .
Great, glad we could help!
k Brent. you will tell correct website to learn in sql server.
Hi Brent,
1.Without using CMS or Batch Files can we run different queries for different servers on a single query window ??
2. If query Completes on first server and it should be disconnect and and another query need to run.
Is there any possibility on this ??
Murali – when you need dependencies like this, you’re best off building a client application in .NET, PowerShell, or Java. SSMS isn’t really a tool for this.
Thanks for your information Brent
Hi Brent Ozar,
Can you please guide that Is there any way to execute a single job on multiple servers ?? Actually I’ve write triggers on msdb database sysjobs tables. These triggers populate tables to another database. After that I query that table whom are populated and Want to shoot the output email of this job. Can you please guide any good and easy way to do that … ??
Waqar – sure, start with Books Online here: https://msdn.microsoft.com/en-us/library/ms180992.aspx
Hi Brent,
May i know how this output can be insert to master instance database table (where all the instances been registered).
thanks
Vellan – you can’t.
It is very nice tool but without storing data in a table no useful.
What if I have 600 servers and checking for the job failure report? Using linked servers and loop thru all of them it’s an old fashion technique.
Two words: monitoring software.