Managing a large number of SQL Servers can be difficult at times. Keeping them all organized is as vital as keeping them online, well, almost. SQL Management Studio offers a few tools to help keep your extensive list of servers organized and easily accessible at any moment. Registering SQL Server instances in SQL Management Studio is easy and provides a quick way to connect and administer your servers.
The first step to registering a server it to open the Registered Servers explorer in SSMS. The option is available by clicking View — Registered Servers.
Once the window opens you should see it snapped to the left pane of Management Studio. In the screenshot below you will see that I already have 3 server groups created. In this example I have my registered servers organized into groups that allow me to more easily distinguish which environment I am working with.
Generally you will want to create groups to put your servers in. In order to create a group right-click on Local Servers Groups and select New Server Group…
Provide a Group Name and description (optional) and click OK. You will then see the new server group listed under Local Server Groups.
The next step is to register servers under the newly created server group. To do this, right-click the new group and select New Server Registration…
On the New Server Registration window you will need to enter the Server Name and specify the Authentication type. By default, Windows Authentication will likely be selected and the User name and Password option will be grayed out. If necessary you can change this to SQL Authentication but in most cases, and if available, authenticate through your Windows and domain credentials.
One nice feature to be aware of when registering servers it the ability to assign a nickname to a server. In the example above I kept the Registered server name the same as the server but you can name this however you want. Once saved, the name you assign to the server will be displayed Object Explorer as well as in the drop-down when connecting to the server.
Once saved you will see the new registered server listed within the server group.
In the example above I have 3 servers registered within the Lab server group. You can easily connect to the server by double clicking the server name. This will open the server in the Object Explorer in Management Studio.
Another useful feature available to further organize your serves is the ability to assign color codes to the tabs of each server. This is very useful when connecting to both lab and production servers simultaneously to ensure you are working in the intended environment.
To configure this, right-click on a registered server and select Properties…
On the Edit Registered Servers Properties window click on the Connection Properties tab.
Check the box next to Use custom color. Then click the Select… button to choose the color you want to use for that server. I assign red to all my production servers and green to all lab environments. This makes it immediately obvious which environment I’m working in.
I’m not sure why the text is partially cut-off on the custom color option but I’ve seen this in multiple installations of Management Studio. But dont’ worry. It still works.
You can also change the tab color of the query window by simply right-clicking on the server under Registered Servers and selecting Tab Color (Server). There are several default colors generally associated with various server types to choose from.
An additional option on the the Connection Properties tab allows you to define a default database upon connection to the server. This makes launching query windows and performing other tasks easier if you tend to connect to a particular database more often than others.