Anyone who has been reading my blog for a while should realize that several divisions in my company have been merging together.  This means that there are lots of new tasks and processes to learn.  It also means that I’m finding uses for several features of SQL and SSMS that I hadn’t really had a use for before this.  For example a few weeks ago I talked about Using New Horizontal/Vertical Tab Groups.  This time round it’s a fun trick with registered server groups and query windows.

First the situation.  I am cross training on yet another new group of tasks and noticed that of the 10 requests I had before lunch the vast majority of them involved running one or more scripts on both the test and model version of the database.  I set up the script, I connect to test, run the script.  Connect to model, run the script.  Set up the next script and repeat.  It got rather tedious.  So here is my solution.

First things first I set up a registration group in SSMS and register all of the instances I need to work with under it.

Next I right-click on the registration group and select “New Query”.

Fair warning it appears that this only works in SSMS 2008 and above.

If you look at the bottom of your query you will see that we are connected to 2 of 2 instances and the connection name is the name of the registration group rather than the name of the instance connected to.

Now any script run in this query window will be applied to both instances.

Notice that the first column is [Server Name] and it isn’t a column we asked for.  This column is going to automatically be added to any result set to differentiate between the output from each server.  Quick gotcha here.  [Server Name] isn’t something you can include in your query.  You can’t order by it.  You also can’t ignore it.  If we run the same query but ordered by name you will notice that the output is still split up by [Server Name].

You also can’t dump the combined output into a single location.  If you do something along the lines of

SELECT name INTO #temp FROM sys.databases

You will end up with a temporary table on each instance containing just the data from that instance.

So what use did I find for this feature?  Well if you recall from above I have been running the same script on each of two identical instances, one test and the other model.  So having created a registration group for test and model, I open a query window to both instances.  Now I only have to run each script once and it’s applied to both instances.

I even managed to do some differentiation based on server.

Now using this feature lets you apply a single script to multiple instances quickly.  This means that you can get a lot done quickly.  However it also means that you can mess things up quickly as well.  When doing this be very precise in your script and make sure you are pointing to just the instances you expect or you can end up with some rather unexpected (and probably unpleasant) results.