The Art of SQL Server Database Administration, Development, and Career Skills for the Technically Minded
Registering a server with SSMS is simply the act of saving connection information including server name, location, default database, server login, description etc within your local install so you do not have to remember connection details from memory each time. You can simply double click and instantly connect to a SQL Server instance. There is much more benefit to the practice of registering servers however than simple memory assistance. It’s day 14 – let’s get started.
One of my favorite features of SSMS is the ability to register servers within the application and group them into organizational units. I can’t tell you how many times I’ve opened up SSMS not to connect to a server but to remember what the name of a specific box in an specific business function is called. If you work in a variety of environments you’ll find that server naming conventions range between everything from random guids to rock bands from the 80s. Grouping servers in a way that makes sense to you can not only save time but keep you from accidentally bringing down a production server.
Most folks group servers into Production, Stage, QA and Dev/Test etc. From there I will often also group them further into business units. My current environment has over 100 database servers. Keeping them organized within registered servers is the only way I stay sane. At the lowest level is the actual database server identified by the classic canister icon.
Like we learned yesterday you can give these server registrations their own color codes … so for example, whenever a red server pops up you know it’s production and you should tread lightly. Setting this option in registered servers is basically the same as connecting manually. Select properties from the right click menu and then click ‘Connection Options’ tab and assign a color.
Basically all management of registered servers is done through right click menu and/or hotkeys. Adding new servers, adding new groups, moving servers between groups, changing colors and other properties etc. You can also gain access to the servers log from the right click menu as well as stop and start the underlying server service etc.
Working with a Team? Importing / Exporting
When working with teams of DBAs or database developers you can also import and export your groups for use by others or yourself in additional work environments. To do this look under the tasks item of the right click menu and select Import or Export.
Exporting your registered server list generates a simple XML file with the breakdown of your groupings along with the related meta-data such as connection color etc.
The export file has a regsrvr extension but internally is a simple XML set. Open it with your favorite XML parser to see how it’s organized.
Notice that you have the option of saving usernames and passwords when exporting that file. The exported password is hashed and salted so that it will only work on the box the list was created on; its use is limited to local backup of registered servers only. When sharing your exported lists, any stored passwords will have to be re-entered. Including the hash on an export destined for another box or user exposes security for no benefit.
Aside from SQL Server the registered servers box can also contain groups for SSAS instances, SSIS instances, and Reporting Services Servers for management within SSMS and the object explorer interface.
Central Management Server
If you really want to get tricky – one of the coolest things you can do with a group of registered servers is set them up under a central management server. Once you have done this you can manage policies, issue t-sql statements, and effect other management actions against all servers within a group in a single statement or command. Tune back in tomorrow and we’ll walk through setting up a central management server and issuing statements en masse.