Shortcut: Configuring registered servers in SQL Server Management Studio

When working with SQL Server systems, it can be hard to remember the names of all the servers, to remember connection details for the ones that need SQL logins (instead of Windows authentication), and to remember other details of those servers, such as which environments they are part of (eg: production, UAT, test)

SQL Server Management Studio (SSMS) has a facility to help you to do this. It allows you to register server details in a single place.

By default, the window isn’t shown, but from the View menu, you can choose Registered Servers.

When the window opens, you can see this:

Note the toolbar near the top of the window. It is showing that we’re configuring database servers but the other icons let you know that you can also work with Analysis Services, Integration Services, and Reporting Services servers.

The first decision that you need to take is to decide where the details will be stored. Local Server Groups are stored on your local system ie: the system that is running SSMS. If you move to a different system to work, you won’t have those connection details. Alternately, a Central Management Server can be configured. This is a server that agrees to hold connection details. While this seems a great idea (because the details would be held in a single place), one of the down-sides of this arrangement is that only Windows authentication can then be used. Local Server Groups can also work with SQL logins.

Let’s create a server group as an example. If I right-click Local Server Groups, here are the available options:

Note that there is an option to Import (and Export) these details. This at least allows you to move details between systems.

Let’s create a new Server Group:

It just needs a name and an optional description, then OK. When it’s created, right-click it, and choose New Server Registration:

I’ve connected to the server SDUPROD and I’ve given the registered server the same name. Note that you don’t need to do that. I could have called it PayrollServer or some other more meaningful name. You’ll also notice that there are tabs for configuring other connection properties.

I’ve then created a second server called HRServer and under the covers, I’ve pointed it to another server.

Now I have all my servers in groups, in an appropriate location. I can right-click them to open new queries to them, and to do much more.