SQL Server 2008 Management Studio: Group Execute

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:

SQL Server 2008 Management Studio Group Execute

“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:

SSMS 2008 Querying Multiple Servers At Once

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:

Getting Multiple SQL Server Versions

Run SP_Configure on Multiple Servers

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.

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

More Posts - Website

Follow Me: