Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Registering SQL Server instances for easier management


By:   |   Read Comments (4)   |   Related Tips: 1 | 2 | 3 | 4 | More > SQL Server Management Studio

Attend a SQL Server Conference for FREE >> click to learn more


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.

Server group Server Alias Server IP Login
TestServers Sales_TestServer 10.16.5.13 sa
TestServers HR_TestServer 10.16.5.13\MSSQL2008 sa
ProductionServers Sales_ProductionServer 10.16.1.230 dba (or any other)

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
there may be a slight difference in the SSMS 2008 and SSMS 2005 interfaces, but both work on the same principal

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.

 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.

create a server group right click on the Database Engine folder in SSMS 2005 or Local Server Groups folder in SSMS 2008

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".

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.

In the connection properties tab change any parameters for customization if required

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.

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.

register the connected instance of Sales_TestServer


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.

 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.


Last Update:


signup button

next tip button



About the author





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Wednesday, February 18, 2015 - 1:34:10 AM - Rob N Back To Top

Hi, I have created a Connect Item in hope that Microsoft will add the ability to filter Registered Servers (that way you could run queries on a subset of instances instead of whole folders).  https://connect.microsoft.com/SQLServer/Feedback/Details/1051746

 


Thursday, November 10, 2011 - 12:51:18 PM - SAinCA Back To Top

Download the *FREE* SSMS Tools pack from http://www.ssmstoolspack.com/ and use the "Windows Connection Coloring" menu option.  It won't color the status bar in SS2005 but it does let you color a configurable stripe that I position at the left side of each query window, 5 pixels wide.

Hope that helps 2005 users...


Tuesday, May 11, 2010 - 3:10:45 AM - @tif Back To Top

Color customization is not available in SSMS 2005. You need SSMS 2008 for this purpose. To utilize this feature use SSMS 2008. It would work fine both for SQL Server 2005 and SQL Server 2008 instances.

Thanks


Monday, May 10, 2010 - 3:14:26 PM - gmamata Back To Top
Can we differentiate the registered Dev, QA and production sql servers with different colors in sql server 2005 SSMS? Is this possible? thanks

Learn more about SQL Server tools