SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Multiple instances, single query window

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.


My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.


Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...