Registering SQL Server instances for easier management

Problem

I have to manage a number of SQL Server instances across the network. To access the servers I generally use IP addresses along with a custom port number if one is configured.  I could use the server name, but the server names are getting longer and also less meaningful.  It is a bit time consuming and annoying to keep track of each server by its IP, so it would be nice if there was an easier way.  It would also be nice to keep associated properties of the connection like default database, default protocol etc. also persistent in certain cases. Although saving connection properties in the connection frame of SSMS may serve this purpose, but it also contains ad-hoc created connections and I often purge this information. Is there a way to arrange my servers inside SSMS to work in a more managed way.

Solution

Along with increase in the number of server instances, databases and other objects a DBA has to manage, a DBA has to adopt new practices for better manageability. Registering SQL Server instances is one step to adopt a managed administration of multiple servers. Instance registration is provided both in Enterprise Manager and SSMS. In this tip we will go through the registration process of SQL Server instances through SSMS 2005.  The same procedure will work in SSMS 2008 with some minor interface differences which I have tried to note.

Planning for Instances Registration

Some planning would make the task easier. First of all make a list of available instances to be registered with the following information.

• Server group: Server group may be based on broad categories. e.g. Production servers, test servers, development servers. These categories would be used to create server groups during the registration process.
• Server Alias (meaningful name): Server Alias is meaningful name provided to instances during the registration process. It would help to identify each instance with a meaningful name rather than IP address.
• Sever IP (and port if customized)
• Login for server

These are the basic parameters that you would need to register SQL Server instances. Additional parameters may be added like default protocol or a customized task bar color in SSMS 2008. For working in this tip I have gathered the following information for servers to be registered. If you are going to register less than 5 instances then you can probably skip the server group creation.

In this example I will create two server groups for the three registered servers.

Creating Server Groups

As I mentioned, creating server groups is optional, but it can help with managing your registered servers. In this example I will create two server groups TestServers and ProductionServers.

Following are the steps to create server groups in SSMS 2005. (Note: there may be a slight difference in the SSMS 2008 and SSMS 2005 interfaces, but both work on the same principal.)

• Open SSMS
• Click View in the menu and then click “Registered Servers” as shown below or you can use the keyboard shortcut Ctrl+Alt+G

The registered server explorer may differ slightly in SSMS 2008 than that of SSMS 2005 shown above. Below is the screenshot of registered servers explorer both in SSMS 2005 and 2008.

.

To create a server group right click on the Database Engine folder in SSMS 2005 or Local Server Groups folder in SSMS 2008 and select New Server Group.  This will be done twice for each of the groups we are creating.

Again New Server group frame may be slightly different in SSMS 2008.

Register Servers

We can now register servers in the created server groups according to our plan. Right click on appropriate server group and select “New” and then “Server Registration”.

A new server registration frame will appear. Provide the required information in first tab and any parameters for customization in second tab. 

In the connection properties tab change any parameters for customization if required.   This would be done three times for our three servers.

Once a server is registered, you will have it in your registered servers list every time you open the Registered Servers explorer. All parameters and customization will be retained. Here is a screenshot of how things are now setup for our two server groups and our three servers.

Register a connected instance

You can register an instance directly when it is connected. In that case you do not have to provide login credentials. The server will be automatically registered with the same login credentials that are used for the current login. To do this, right click on the instance and select “Register…”.  A new window will open to allow you to enter server alias, description and the group you want to put the instance in.

To simulate the exercise I will again register the connected instance of Sales_TestServer. On clicking the save button, we have option to either overwrite the pre-registered instance or just cancel the operation when prompted.

Move registered instance from one group to another

If at any time you need to change the server group of a registered instance, it can easily be done by selecting the “Move To…”option by right clicking on the registered server.

After registration of a server alias, the description or login can easily be modified by selecting “Properties” of the registered instance from the menu as shown above.

Next Steps

It is obvious from the above that server registration is flexible and there are various facilities provided to register and manage the registered servers. 

Some things to note:

• A server can be registered even when it is stopped. In that case login credentials verification would be pending until you actually connect to the server.
• If required at some time, you can import/export registered servers from one machine to another
• Customize the connection properties of registered servers to make more efficient use.
• In case of SSMS 2008 also customize the status bar color of the query window in the connection properties. Click here to read more about it.
• Here we have discussed about registering SQL Server database engine instances, similarly you can also register Analysis Services, Reporting Services and Integration Service instances.