Here are some innovative ideas I've come up with to save the connection information for multiple SQL Server instances:
- Write them on a piece of paper (and then lose it).
- Write them on Post-it Notes (and have so many of them that I need an assistant to organize them).
- Save them in a text file (and then forget where I saved that file).
- Save them in an email (and then spend half an hour trying to find it).
- Call the Help desk.
- Shamelessly ask my nice co-worker time and again.
- Get a tattoo.
- Memorize them all.
- Use Registered Servers in SQL Server Management Studio (SSMS).
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:
- Preserve connection information.
- Determine whether a registered server is running.
- Easily connect Object Explorer and Query Editor to a registered server.
- Edit or delete the registration information for a registered server.
- Create groups of servers.
- Provide user-friendly names for registered servers.
- Provide detailed descriptions of registered servers and registered server groups.
- Export and import registered server groups.
- View the SQL Server log files for online or offline instances (SQL Server 2012 only).
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.
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).
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.
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.
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.
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."