Using Registered Servers in SSMS

Here are some innovative ideas I've come up with to save the connection information for multiple SQL Server instances:

After some contemplation, I opted for the last option because of the security and empowerment it offers. It's another step toward standardization and consistency in how you use different tools. The extra capabilities that come with it make it an indispensable utility. For example, with the Registered Servers tool, which is available in SQL Server 2005 and later, you can:

How to Use the Registered Servers Tool

I'll walk you through how to use Registered Servers in SQL Server 2008 R2. (Note that this demonstration is limited to the Database Engine only and not any other type of registered server.) Here are the steps:

1. Open the utility in SSMS by pressing Ctrl+Alt+G or selecting Registered Servers on the View menu.

2. Create a new server group. In the Registered Servers pane, right-click Local Server Groups in the Database Engine folder. Select New Server Group, and enter the group's name and description. In this example, I named it Demo, as Figure 1 shows. After you click OK, the server group will show up in the list of Local Server Groups.

 
Figure 1: Creating a new server group

3. In the Registered Servers pane, right-click the server group you just created and select New Server Registration. On the General tab of the New Server Registration dialog box, specify the server name in the Server name text box and the authentication information, as Figure 2 shows. In theRegistered server name text box, enter the user-friendly name you want to give that registered server. Whatever name you specify in the Registered server name text box (DevServer1 in this example) gets added to the server group's folder (Demo in this case).

Figure 2: Specifying the name of the new registered server 

4. On the Connection Properties tab, specify the database to which you want to connect and the timeout values, as Figure 3 shows. If you want the server's status bar to display a specific color when connected to this server, select the Use custom color check box and select the color. In this example, I assigned green to the Dev servers. When I later double-click DevServer1 to connect to it, the status bar will be green (see Figure 4), visually indicating that I'm connected to a Dev instance.

Figure 3: Assigning a color to the registered server

Figure 4: Observing the green status bar of a registered Dev server 

5. Repeat steps 3 and 4 for the other registered servers in the server group.

Figure 5 shows the completed Demo group. Besides assigning green to the Dev servers in this server group, I assigned orange to the QA servers, blue to the UAT servers, pink to the PreProd servers, and red to the Prod servers.

Figure 5: Observing the completed Demo group 

After you've created a registered server group, you can execute the same query against multiple servers simultaneously by right-clicking the server group, then selecting New Query. You can also export the registered server information for distribution to other co-workers. They can then import this file rather than redefining all the servers. This promotes reusability.

To export the registered server information to a file, right-click the registered server group, select Tasks, then choose Export. In the Export Registered Servers dialog box, specify the name you'd like to give the file and where you'd like to save it. As Figure 6 shows, you can indicate whether or not to include the authentication information. The saved file will have the extension .regsrvr.

Figure 6: Exporting the registered server information 

Registered Servers and Central Management Server

I wondered if the registered servers could be maintained by a single authority and published (or pushed) to SSMS clients rather than every individual having an editable copy. I discovered that the Central Management Server tool in SSMS provides this ability, which can make database professionals' lives easier.

With the Central Management Server tool, you can manage multiple servers by creating a server group and designating a central management server for that group. You can set up a server group and its central management server from any workstation in a manner similar to how you create a registered server group. You need appropriate permissions on msdb.dbo.sp_sysmanagement_add_shared_registered_server to accomplish this. Once defined, you can point SSMS on any other machine to the central management server and access the list of servers.

The list is always in sync, and real-time changes can be published in case changes are made to the connection strings. Another advantage of using a central management server is that passwords aren't stored when the servers have been registered with SQL Server authentication. For more information on the Central Management Server tool, see "How to: Create a Central Management Server and Server Group (SQL Server Management Studio)"  and "Central Management Server."