SSMS Tips and Tricks 4-4: Configuring registered servers

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)

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:

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 in the msdb database.

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 .\SQL2022 and I’ve given the registered server the name SDUDEV. 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.