One of the features in SQL Server Management Studio (SSMS) that I find very useful is to have a list of registered servers and databases in SSMS. This lets me quickly pick from a number of servers rather than flipping the drop down in the connection dialog. This post looks at this feature and how to customize your list of servers.

The Registered Server Pane

In SSMS, there is a Registered Servers pane, but it is not shown by default. If you open the View menu, you see the entry below. You can also use CTRL+ALT+G to get this open.

If opens above the Object Explorer by default.

I don't like that, so I've docked it below the Object Explorer. If you grab it and move it away from the OE, you can then drag it back over. Once it's over the OE, you will see a set of 5 images, a central one and one on each edge. Choose the lower one to dock below below the OE. Or you can dock it anywhere you like. Here's what I like:

In the View menu, there is also a Registered Server Types. As you can see there are Database Engine, SSAS, SSRS, and SSIS items.

If I select one of these, say Integration Services, then my Registered Servers pane changes. The icon at the top for SSIS is also selected. I'm not sure of the value of this item, but I guess it's not a lot of technical debt for the SSMS team.

If you need connections to different types of services, this will help you filter them.

Organizing Servers

The Registered Servers pane lets that you can add registered servers and then pick from them in a list. You can also see your connections from Azure Data Studio or your Central Management Servers. I use Azure Data Studio at times, and as you can see, my list in SSMS is the same as ADS. ADS is on the right and the two connections I have in there are shown in SSMS.

I can't edit the ADS items, but I can connect to them and run queries or perform other actions.

This is a test VM and I removed everything in local server groups, so let's add our own servers.

Adding Groups

I often want to organize servers by groups. There are usually a number of servers that either correspond to some application or some department, and putting them in a group lets me quickly find all the servers that are relevant to some situation.

To add a group, I right click the Local Server Groups, and I see a New Server Group menu item.

Once I click this, I get a dialog for the group name and description. I used to just add a name, but in a team, it has proven helpful to add a short description of what this group is and who the contact is. When we're dealing with a group, often we are in SSMS, so having some info in the description is handy.

I can continue adding groups as needed for the different situations I run into. I can even add the same server to multiple groups,

Adding Servers

To add a server to the list, you right click the group and then select New Server Registration.

I get the dialog that shows my the same type of properties I see in other SSMS connections. This is SSMS 20, which is slightly different than other versions. You can see I've entered the server name from my VM and checked the Trust server certificate box. At the bottom, I can enter the server name. In the description is where I should enter contact info for a client. This VM is just me, but I might enter the internal group or business contact for this server at work.

As a contrast, here is the dialog on SSMS 18.

Once I've added my servers, I see the entire list. I've broken these into two groups.

One other note. I only have 2 instances on this VM, but I've added multiple servers. I can add the same server multiple times and give it a new name and SSMS accepts this fine. In the image above, all the PROD servers are the same instance. This is handy if I have a server that I've grouped into something like Finance and into the Prod groups. This is helpful when there might be two ways I look at the servers. They can be in the test group and also in the Finance group for the Finance test server. You can see the same server added in two groups below.

If I try to add the same server twice to the same group (I've made this mistake), I get an error.

Using the Servers

Once you have registered servers, you can do a few things. Let's click the group inside Registered Servers. We see a menu that includes these items: New Query, Object Explorer, Evaluate Policies, Import Policies and the New items.

If I right click a server, I see the same items, with the addition of View SQL Server Log and without the New items.

Of these options, let's see what they do.

I wrote about importing and exporting servers, which is handy for ensuring the entire team has the same list.

New Query

When I click this, no matter whether I pick the group or one of my DevFrankD instances, I get a single query window for this same instance. Even if I add a different instance with a name that puts it first, I still get the same instance. I added a second instance to this VM and added it to the Production group as Accounting. I can only get this as a connection in a new query window if I right click this instance. Not sure why.

Object Explorer

If I select the Object Explorer item, I get that instance opened in the OE tab (window?) in SSMS. If I pick a group that has two instances, I get both opened. Here is the before, where I have a single connection.

Here is what I see after selecting Object Explorer from the menu.

This is handy, as I often have a single connection in SSMS when I start working, but right clicking the group lets me connect to all the instances I want. I've used this where I put all the servers I normally work on in one group, so I can connect all at once 😉

Evaluate and Import Policies

This lets you work with the Policy-Based Management features of SQL Server. I don't really work with this, but it brings up an empty dialog where you can select policies. I've connected to my instance, where I have the default AG policies.

I don't quite know what these do, but you can run them against a server or group.

View SQL Server Log

This brings up the standard SQL Server Error Log for a server. This is the same thing you get when you double-click a log under Management | SQL Server Logs.

Summary

This article examined how we can set up and use registered servers in SSMS. It's a basic look with some of the tips I've learned over time.  In a future article, I'll look at Central Management Servers.