SSMS: Connect To Several Servers In One Click (Okay, Two)

I’ve been using the SQL Server Management Studio (aka SSMS) since it was first released with SQL Server 2005.  And yet, it hasn’t stopped to surprise me.  Earlier this week I’ve discovered a feature which I will use frequently as of now!

While doing my day-time job at the customer, I have a habit of connecting to several servers each time when I open SSMS.  I always connect to them in the same order so that I can quickly locate them in the Object Explorer.

The servers I want open all the time are DEV DB server, DEV SSIS server, UAT DB server and UAT SSIS server.  The SSIS servers are needed to get quick access to the Job Agent, while the DB servers are what I use all the time to actually do my job.  To avoid confusion: on those SSIS servers, I’m connecting to the Database Engine, not the SSIS service.

So, earlier this week I was trying to find a method to easily connect to these servers with as few clicks as possible.  And guess what: I found one! (Well, otherwise I wouldn’t be writing this of course.)

In the following paragraphs I’ll describe a method which you can use to connect to several servers at once, with just two clicks!  I can tell you, it sure beats the Connect To Server popup window!

Screenshots taken using SQL Server 2012, but this should work as of 2005.

First we’ll create a group of all the servers that we’d like to connect to with just two clicks.

Open the Management Studio and switch to the Registered Servers view.  If you can’t find it, use the menu to select View > Registered Servers or hit CTRL+ALT+G on your keyboard.

Open the Registered Servers view

Right-click the Local Server Groups node located under Database Engine and select New Server Group… to create a new group.

Creating a new registered servers group

Give it a good name and click OK.

New Server Group Properties

Right-click the new group and select New Server Registration….

Enter the server credentials as appropriate.

New Server Registration

What’s important here, besides the obvious such as entering correct credentials, is the Registered server name.  The servers will be ordered alphabetically using that name, and that’s also the order in which they’ll be opened!  So if you’re like me and you want your servers to be opened in a specific order, you’ll need some naming creativity or use numbers.  In my case I’m glad that DEV orders alphabetically before UAT, which is what I want. Smile

Ow, in case you’re wondering about that server name shown in the screenshot above, the dot refers to localhost and sql2012 is the instance name.

To demonstrate that I’m not joking, I’ll now register a second server and name it “Another server”.  After clicking the Save button, here’s what the Registered Servers window displays:

"Another server" shown above "My DB Server"

As I told you, “Another server” is shown above “My DB Server” even though it was created later.

Now, what you’ve all been waiting for, how do I tell SSMS to connect to these servers?

Click number one is a right-click on the group name, My Favorite Servers in this case:

Use Object Explorer on the group name to open all servers in the group!

And click number two is the one on Object Explorer!

SSMS will now switch to the Object Explorer window and connect to the servers, in alphabetical order!

Object Explorer is connected to multiple=

How’s that for a time-saver huh?  I hope you’ll enjoy this as much as I will!

As a little extra, let’s quickly discuss one more feature of the Registered Servers window.  When you right-click a group, you also get a New Query menu item.  Clicking that will open a query window that’s connected to all the servers in the group.  You’ll be able to tell because the database dropdown in the menu reads <multiple>:

Query window connected to multiple=

And the status bar mentions the name of your server group and also <multiple>:

Query window connected to multiple=

Having a query window connected to several servers at once let’s you do interesting things, such as quickly checking what version and edition you’re running on all those servers:

Running the SELECT @@VERSION command on more than one server at once

Of course, I’m sure I don’t need to remind you but I will anyway, this means it’s actually a dangerous window as well.  In the database dropdown, you’ll get a list of all databases that are found on each server.  There’s no advanced logic used in the process of building that list: if the database name is found on all servers in the group then the name of the DB is added to the list.

You can select one of those databases and execute queries against them, just like this:

Running a query against more than one database over several servers

The status bar will now mention the actual database it’s connected to, which is actually its regular behavior.  And the messages pane will mention how many servers you’ve run the statement against.

In the case above, I created a table in two tempdbs on two servers.  Imagine that I ran a DROP TABLE instead, and I forgot that one of the servers in the group was the production server on which I didn’t want to drop that table.  So, be careful with those windows.  To avoid errors, close them as soon as you’ve finished the job for which you needed them open!

Have fun!



Tags: , , ,

  1. Clive’s avatar

    Wonderful… absolutely exactly what I have been looking for… Why didn’t I find this sooner… Thanks very much.


  2. Soner Gönül’s avatar

    That’s just.. lovely.

    Thank you very much.


  3. Ian Henderson’s avatar

    This is a great workaround. In any given day I can be accessing multiple servers and it’s great to be able to open a connection to all three of them in a single click.

    I do have one question though, and perhaps I’m just being a bit silly:

    When I first launch SSMS (2012), the first thing I see is a Connect to Server dialog box which invites me to connect to a server. Is there any way of reconfiguring SSMS so that I don’t get this prompt?


    1. Valentino Vranken’s avatar

      Hi Ian, yes, there’s actually a setting for this. It’s located in Tools > Options > Environment > Startup > select “Open empty environment”. Thanks for an interesting question, wasn’t aware of this before you asked either! ;)


  4. Webdesign Moçambique’s avatar

    Hello to every one, it’s really a nice for
    me to visit this web site, it consists of priceless Information.


  5. seizethecarp’s avatar

    Came here to say thanks!


  6. Hye’s avatar

    I’m impressed, I must say. Seldom do I come across a blog that’s both equally educative and amusing, and let me tell you, you have hiit the nail on the head.
    Thee issue is something not enough folks are speaking intelligently about.
    I am very happy that I found this in my search for
    something regarding this.


  7. Outside And internal Lighting tewkesbury’s avatar

    Its like you read my mind! You seem to know
    so much about this, like you wrote the boiok in it or something.
    I think that you can do with some pics to drive tthe message
    home a bit, but instead of that, this is great blog.
    A great read. I will certainly be back.


  8. homepage’s avatar

    Hi there, its fastidious paragraph concerning media
    print, we all know media is a great source of information.


  9. Hannelore’s avatar

    Hі there, I want to subscribe fоr thiѕ web site tο
    tаke newest updates, thᥙs whrre cаn i do it plеase һelp out.


  10. Andy’s avatar

    Hi there, everything is going nicely here and ofcoiurse every one is sharing data, that’s actually fine,
    keep up writing.


  11. Toni’s avatar

    Right now it looks like Drupal is the top blogging platform available right now.
    (from what I’ve read) Is that what you’re using on your blog?


  12. Stephane Cote’s avatar

    Wow.. this will save me SO much time.. Thnx


  13. Alt Pokerpelangi’s avatar

    is a trusted QQ Texas hold’em Online and Bandar Ceme
    Online gambling site that offers on-line card games such as Online Online Poker, DominoQQ, Capsa Online, Ceme Online, Ceme99, Online
    Gaming Online Casino Poker Sites. QQ Online Poker Ceme, the most effective and also safest on-line
    poker agent website with 24 hour IDN Online Casino poker service.
    For those of you Lovers of the video game Online Online
    and that intend to play wagering Online Poker, Online Ceme, Domino QQ, City Ceme, Online
    Betting, Bandar Capsa Online in 1 ID


  14.’s avatar

    Tremendous issues here. I am very happy to look your article.
    Thank you so much and I am taking a look forward to touch you.
    Will you kindly drop me a mail?


  15. judi onlin terpercaya’s avatar

    It’s ցreat that yоu are getting thoughts
    from this article as well as from oᥙur argument
    made here.


  16. Jocelyn’s avatar

    Aw, thiѕs was a vеey good post. Spending some time and actual effort
    to generate a very good article… but wnat caan I say… I hesitate ɑ lot and don’t manage to
    get nearly anything done.


  17. judi Online tanpa modal’s avatar

    What’ѕ up, its gоod article about media print, we all
    underѕtand media is a impressive source of fɑcts.


  18. Sang’s avatar

    This piеce of writing presеnts clear ide in upport of the new usеrs of bⅼogging, that truly
    how to do running a blog.


  19.’s avatar

    is a trusted QQ Online poker Online and also
    Bandar Ceme Online wagering website that supplies on the internet
    card video games such as Online Texas Hold’em, DominoQQ,
    Capsa Online, Ceme Online, Ceme99, Online Gambling Online Casino Poker Sites.

    QQ Online Poker Ceme, the very best as well as most safe online
    texas hold’em agent website with 1 day IDN Online Online poker solution. For those of you Lovers of the video
    game Online Online and that wish to play wagering Online Texas hold’em,
    Online Ceme, Domino QQ, City Ceme, Online Gambling, Bandar Capsa Online in 1 ID


© 2008-2019 BI: Beer Intelligence? All Rights Reserved