This month’s T-SQL Tuesday is being hosted by Allen White (blog | @SQLRunr ).  The topic:  Share your T-SQL tips and tricks.  And if this post looks familar, like something I might have mistakenly posted, say, last Tuesday, well I have no idea what you’re talking about.

This one was tough for me.  What do I do that makes my job easier?  I script all of my routine tasks, so I went through my arsenal of scripts, dismissing each in turn for one reason or another. Too specific, too boring.  Hmm, what to do.   Then I realized that there was one thing I could share, something I do to make the execution of those scripts easier: I organize my registered servers into groups.

It surprises me how many folks out there aren’t aware (or simply don’t think about) this feature.  In SSMS 2008, you have the ability to organize registered servers into groups.  For example, I frequently have to create/drop a SQL login in all environments for a particular application.  Rather than go to each server and drop the login, wouldn’t it be awesome to run the statement once?   So I created a server group for my application, then created sub-groups for my three environments.  I then registered the appropriate servers in each group.

Now, anytime I need to run a statement against this group of servers, or one of the subgroups, I can right-click on the group name, and choose New Query.

Which opens a query window like this.

The first thing you’ll probably notice is that the status bar is pink.  This indicates that multiple connections have been opened. If one or more of your connections have failed, the status bar would indicate that, too.

If this happens, right-click in the query window and open the properties window.  There you’ll find a section listing which connections failed.  You can still use the query window against the connections that succeeded, you’ll just get an error for those connections that failed.

Type in your query and hit execute.  The statement executes on all the servers at once and returns the results for all servers in one resultset.  The respective instance name is also included in the results by default.  You can also change this behavior.  Under the Tools menu, open Options.  Under Query Results -> SQL Server -> Multiserver Results, you’ll find options for including the login name and server name in the results, as well as an option for merging the results, like we see in the example above, or having the results come back in separate resultsets.

So there you have it.  This little “trick” saves me a lot of time when I have to query multiple instances.  And you can register the same server in multiple groups, so don’t think you have to carefully plan out your grouping scheme.  Do what makes sense for you and your environment.