Russ Thomas – SQL Judo

The Art of SQL Server Database Administration, Development, and Career Skills for the Technically Minded

SSMS Day 14: Registered Servers / Server Groups

31 Days of SSMS: Table of Contents

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.

reglist

Registered Servers

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.

Color Codes

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.

Other Options

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.

import

regsrv

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.

Other Services

otherAside 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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: