Hidden Gems in SQL Server Management Studio: Registered Servers

As a SQL Server Database Administrator you tend to spend more time in SQL Server Management Studio (SSMS) than you do with your family. It’s surprising just how many peers I speak to though that are not using components of SSMS that I think of as routine – or are not using them to their full potential. I’ve explored this before in my articles on the SSMS Template Explorer (intro  |  deeper dive) and now it’s time to give another component its due: Registered Servers.

The Registered Servers Window

The Registered Servers window is accessed via the SSMS menu under View or through the keyboard shortcut of Cntl + Alt + G and looks like this before you make any additions as we’ll be discussing here in our next two articles on the subject.

Taking a moment to examine what we have “out of the box” you’ll see the four icons across the top as well as the three nodes presented below those. You’ll not have an expandable + sign if you’ve not used this component before so disregard that for now. The Local Server Groups node and Central Management Servers nodes become expandable as you start adding entries like we’ll be doing here in this article.

This window, like all others in SSMS has the ability to either pin to a consistently open state or auto-hide depending upon the pin icon in the upper right. You can also close the window at any time by clicking the X. It’s important to know that you’ll not lose your registered servers if you do so. The entries you add here are persisted through application closure as well. The only time you lose these settings are if you uninstall SSMS or other “scorched earth” scenarios such as drive formats, O/S uninstalls, etc.

The four icons across the top of the Registered Servers window denote the various types of registered servers you can store and work with inside of Management Studio:

 Database Engine

These are registered servers you’ll be using in your day-to-day work as a Database Administrator and to some extent as a Database Engineer or Database Developer. This is likely the most-frequented of the four categories of servers that can be registered and is where the bulk of all work inside SSMS is conducted. It’s also the focus of this first article in the series.

 Analysis Services

These will be your Analysis Services registered servers which, if you’re a SQL Server Business Intelligence Developer you’ll be accessing frequently.  Note that these registered servers entries will be completely independent of those collected under Database Engine. This will be the case with the remainder of the categories as we go forth.

 Reporting Services

As you’d expect from the name, these are your registered SQL Server Reporting Services (SSRS) instances. I’d expect you to have these listed under both Database Engine and here because those entries under Database Services will allow you to administer to the underlying Microsoft SQL Server instance hosting SSRS while when hosted in this category you’ll have options for working directly with the SSRS components themselves.

 Integration Services

Finally we have SQL Server Integration Services (SSIS) servers. As follows with the previous categories these entries pertain to the SSIS-related aspects of SQL Servers in your environment and the features offered for entries here will focus on the BI-centric aspects of those instances – not on the underlying SQL Server administrative functions for the hosts upon which they sit.

Focusing on Database Engine

For this initial article we’re going to focus on the most common of these four categories: Database Engine.

The Registered Servers isn’t valuable without adding servers so I’m going to walk through that process first.  Population of this window is exactly what you’d expect from a Microsoft-developed graphical user interface (GUI). Adding instances, moving instances, deleting instances, and categorization are the core operations of populating and maintaining the integrity of your Registered Servers in SSMS. We will explore each one in detail below.

Adding an Instance                                                                         

If you’ve ever connected to a SQL Server instance in the Object Explorer or through creating a new query in SSMS then you’ve gone through the same process of registering an instance in the Registered Servers window. The only thing you’re doing differently here is that you’re persisting the registration information for re-use. It’s vital to note that just because you’ve created a Registered Server entry does not mean you’re opening a connection to that instance. You are not. You’re simply storing the criteria which to make the process of connecting to an instance in the Object Explorer or a new query much quicker.

Start by right clicking on Local Server Group and selecting New Server Registration from the available options on the pop-up menu. You’ll be greeted with a dialog box that is quite similar to what you see when establishing a connection to a SQL Server instance in Object Explorer or inside of a query window:

The first thing to do is select the SQL Server you’re planning on registering. If you go the route of using the pull-down functionality in the GUI you’ll find yourself waiting while your workstation queries the network to return all possible hosts broadcasting that they’re a SQL Server instance. I learned early on in my career that my time is more valuable than that. I manually enter the name of the server here.

Of note: if you intend to connect to a specific port on a SQL instance and not the default port of 1433 then follow this syntax instead (do not include the < and > symbols in your entries):

,

You can also register a server with it’s IP address instead of its DNS entry or cname.

Also, don’t forget that instance name if connecting to a named instance:

Regarding a selection for Authentication that’s simple enough and you only have two options:

If you select SQL Server Authentication you’ll see that the dialog box changes slightly to include text boxes for both Login and Password. You should also note then that the Remember  password checkbox is enabled when this choice is made. I tend to always use Windows Authentication whenever possible but there are Registered Servers I have in place when doing testing that connect as a login with different rights than my network login and it’s glorious sysadmin privileges. If I want to see how an end user is going to be able to interact with a database and where their rights are constraining their ability to work with an environment this comes in handy. It’s also nice to know that you can register the same server multiple times in the Registered Servers window. Note that when using the option for SQL Server Authentication and selecting Remember password you are creating a security vulnerability because that login and password combination are saved in a file on your workstation in a dedicated path that is common to all SQL Server Management Studio installs. This is one reason why I tend to never check the Remember password option.

You may wonder “what is the difference between the Server name and Registered server name values in this dialog box?”

This functionality allows you to distinguish between how you connect to an instance of SQL Server and how you commonly refer to a SQL Server. Imagine your day-to-day work with SQL Server. You may have a server with a painful-to-remember named instance like “DSC8675309ajennyjenny” that broadcasts on port 5543. However you and your entire company simply call it the “Accounting Box”. That being the case if you complete the dialog box as shown below you’ll see the corresponding record in the Registered Servers window in SSMS once you save.

The so-painfully-long instance name that will not even fully display in the New Server Registration dialog box is now displayed in a much more friendly and recognizable fashion in the Registered Servers window.

You may notice that there is a Local Instance entry already here. I’ve already pre-populated the window with that so you can see the difference between a server that is recognizable on your network as broadcasting and one that is not. It does not mean you’ve established a connection only that it’s broadcasting and running.

What about that other tab in the New Server Registration dialog, Connection Properties? We can take a closer look by right-clicking on the Account Box entry and selecting Properties from the available options.

This tab affords you the ability to connect to a specific database upon using the Registered Servers window to open the instance in Object Explorer or in a new query window. Additionally you can set the network protocol, packet size, time-out for both connections and query executions as well as encryption session settings for repeatable use each time. I tend to never use these options however I do use the last one I’ve not yet mentioned: Use custom color.

This option is valuable as an extra precaution from that action we always fear as a data professional – connecting to production instead of test and running a harmful query. (TRUNCATE TABLE anyone?) By setting a custom color for the server any time you connect to that instance in SSMS in a query window you’ll have a visual representation for whether you’re connecting to a production instance (I set the custom color to red), staging or training (custom color of blue), or test (custom color of green) in the bar located at the bottom of the query window as well as the tab for the new query. Of course you can choose what works best for you but regardless this is what you’re greeted with (using my Local Instance as an example where I’ve set it to yellow:

Before we move onto categorizing, moving, and deleting Registered Servers I want to show you the main reason why I always use this functionality inside of SSMS.

If you right-click on an entry you’re greeted with the following pop-up window:

The most common functions I use (and likely you will too) are the first two: New Query and Object Explorer. The first will open a new query window with the session settings in the entry (if that server is reachable and there is a corresponding login with proper credentials on the SQL Server instance.) The other will open the SQL Server instance in Object Explorer with those credentials (same caveats apply.)

The View SQL Server Log option allows you to launch the SQL Server Log for the instance indicated in the entry without having to open the instance in Object Explorer and then navigating through the expanded tree view to Management and so forth to launch it. Typically when you’re looking at the SQL Server Log it’s for a critical reason and this lets you get there quicker.

You also have Policy Based Management (PBM) options for evaluating and importing PBM policies and if rights allow ability to control your SQL Server services and open Configuration Manager on the local instance.

We’ve already discussed what selecting Properties does here so let’s turn to the scary one for just a second: Delete. Selecting this option will not erase or uninstall SQL Server or any of its components – fear not. It only removes the entry in Registered Servers from SSMS. You can still add it back at any time and no action is taken against the SQL Server instance represented in this entry.

Categorization of Instances

In my opinion this feature ranks right up there with the ability to connect quickly to an instance in Object Explorer or a new query window without having to remember painful server names and passwords. Imagine an environment where you’ve multiple SQL Server instances segregated into TEST and PROD spaces. Consider furthermore that you also have a couple departmental areas that get grouped logically under PROD. For our example here I’ll use “Accounting” and “Manufacturing” as examples. Start by right clicking on Local Server Group as you did earlier when you selected New Server Registration from the available options on the pop-up menu. Unlike New Server Registration this time we’re going to select New Server Group… from the available options. Doing so raises this dialog box:

Using the scenario I stated above let’s say we have the following servers in our environments and we want to classify them into PROD v. TEST:

PROD

TEST

I can step through the process of creating four Server Groups. Each group is nested upon creation under the node you right clicked on when selecting New Server Group.

Now, with the groups in place I can right click the group name in Registered Servers and select New Server Registration then go through the process described earlier to add a SQL instance to the groups. Once I go through that process for the six SQL Server instances described above my Registered Servers window will look something like this:

Primarily this serves as a visual method for classifying SQL Server instance in my environment of hundreds of instances in a fashion I can relate to and understand. It also allows for me to classify the same server into different areas. For instance I could also add another group under Local Server Groups called Accounting and then register all my accounting department servers – prod, test, stage, etc. there. You can nest and sub-nest instances and you can register instances multiple time and under multiple security constructs.

Now why I really love the grouping options: I can run the same query amongst all instances registered in a single group!

That’s right. Say I’m deploying a change across all my Accounting SQL Servers (using the example above.) I can right click the group name (Accounting) and select New query from the available options. A single query window opens connecting to all servers in that group. I can then deploy the query to all servers at the same time. The command is issued concurrently, not sequentially so there are no dependencies on the process failing for all or for subsequent instances if the first one fails for some reason – perhaps the schemas do not match as an example.

Using a simple query to highlight this I’ll run the following across all my instances located under the TEST server group:

SELECT COUNT([name]) AS database_count FROM sys.databases;

There are a few things regarding behavior to conclude from this screen capture:

As I stated earlier – I love this functionality. I use it all the time particularly when pushing new scripts out to a ton of servers or if I’m staging multiple new SQL Server installs at the same time. It’s a timesaver like you’d not believe.

Moving an Instance

Oops! Let’s say that you really wanted to locate the Manufacturing02 box under TEST since it’s really not a production server. That is as simple as right clicking on the server and selecting Tasks then Move from the available options. You can then select the node you want to move it to (TEST in this example) and click OK. I’d love to see this as a drag and drop feature in SSMS vNext. But for now this is the process required.

Summary of Part 1

In this introduction I’ve shown you how to populate and use core functionality of the Registered Servers window inside SQL Server Management Studio. In future articles I’ll show you how you can share your entries with your teammates, go into topics on Central Management Servers and how to use this work to populate them, how to work with the other types of Registered Servers and more.  This also kicks off a new category of articles here on SQL Server Pro that covers SQL Server Management Studio directly. We will be dedicating at least 1-2 articles per month covering all aspects of SSMS as well as all the new enhancements coming in the future of SQL Server 2016 and beyond that I’ve seen but am not free to talk about. All I can say is that they are fantastic and what many of us have been waiting for since SQL Server Management Studio was released in 2005!