Grouping of SQL Servers

Ability to query groups of servers simultaneously in SQL Server 2008 Management Studio:

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?