Saving Your SQL Server Connections in MSSMS

So one of the most frustrating things I noticed when I started using SQL back in the day was that you had to connect to each SQL server individually.  This can be rather annoying when you are managing multiple SQL server farms.  So a new feature of the Microsoft SQL Server Management Studio (MSSMS) that I discovered through curiosity was the concept of “Registered Servers”.  Basically, as the name infers, these are SQL servers that you have setup connections to with the appropriate authentication type which you register with the MSSMS.   You can even create groups of these servers so that you can logically separate them by function or location.

Configuring Registered Servers and Groups

To access Registered Servers, you simply click on the View Menu and select Registered Servers. This will open another tab view next to Object Explorer which opens by default when you launch the MSSMS.

From here you are presented with the option to connect to Database Engines, Reporting Services, Analysis Services, etc.  Obviously we are talking about SQL Servers here so you want to look at Database Engines.  In the tree, you will see the Default Local Server Group.  You are not restricted to use this group and can make your own logical groups as you see fit.  The one recommendation I can make is to create groups of SQL Servers that you desire to open simultaneously. 

Once you create the groups, you will add or “Register” SQL database engines into each group.  Simply clicking on “New Server Registration” will take you to the dialog where you specify the SQL Server Name, IP address, or Server Name\Instance Name,  and the authentication required to connect to that server.

Since with Windows Authentication, the authentication token is inherited from the Logged On user, there is no need to save the credentials and no option to.  However if you choose SQL Authentication, and also desire to streamline opening all the connections simultaneously, you will need to select the option to “Remember password” which will be presented to you if you do choose the SQL Authentication type.

Opening Connections Simultaneously

Ok, So you have added all the logical groups and servers that you desire.  So what is the trick to opening these in Object Explorer?  This is actually quite simple.  All you need to do is Switch to the Registered Servers view once you open the MSSMS.  Close the initial dialog which will prompt you for connection information once the studio is loaded (This occurs due to the fact the default view is Object Explorer and there are no connections open  – at the time of this writing, there is unfortunately no way to change this or configure as an option).

Once you are at the Registred Server View, you can right-click the group of servers you desire to open connections to and select “Object Explorer” from the context menu.  This will switch you back to Object Explorer view in the MSSMS, automatically open connections to all the servers in that particular group, and expand the Object tree.

Conclusion

So while this is not totally streamlined, the method presents a better alternative to having to open each connection individually.  I am hoping that in the future the MSSMS development team will at least provide a means to open Registered Server Groups from the initial start-up dialog so that you have one less step in the process. It would also be nice to be able to highlght multiple Server Objects in the Object Explorer and be able to dynamically create a Registered Server Group from those selections.  Enjoy!