Menu

Registered Servers vs Central Management Servers

You can alert someone to the contents of this page by emailing them a link.

All fields are required. Only one email address can be specified.

To name :

To email address :

Personal message :

Your name :

Your email address :

To prevent abuse please type the security code below into the box underneath.

Please enter the security code above :

You can view our privacy policy using the link at the bottom of each page.

Thanks for that !

One Click FeedbackPlease help us to improve the site by rating the quality of this article
by clicking a button below.ExcellentGoodAveragePoorAwful

Thanks !

Introduction

If you, like me, look after a large number of SQL Servers it can be very time consuming to reconnect to these servers every
time you start SQL Server Management Studio. This is where Registered Servers and Central Management Servers come into play – you
can set up server information (server name and login information) just the once and then use this stored information every time
you start SSMS. The servers can be set up as a single list of servers, or a hierarchy can be set up with similar servers
grouped together. I tend to group servers together by the type of server (production, test, development etc.), but you can equally
well group them together by application, project, data centre etc.

In the screenshot below I’ve set up some servers with both Registered Servers and Central Management Servers :

As well as persisting server information, there are some other advantages in using either Registered Servers or Central Management
Servers, for instance you can run a query against multiple servers at once. You can also set up an alias for each server – this
is useful if the server has a long name and you want a shorter alias displayed.

The above features are common to both Registered Servers and Central Management Servers, however there are differences in
implementation which will determine which one you use. I’ve listed these differences below to hopefully help you decide which one to use.

Registered Servers

Connection data is stored locally in an XML file (passwords are encrypted), so is for the local user only.

Can be used with SQL Server Authentication or Windows Authentication.

Can be used for the database engine, SSAS, SSIS and SSRS.

Central Management Servers

Connection data is stored in a central SQL Server (in the msdb database), and can be used by other users.

Can be used with Windows Authentication only.

Can be used for the database engine only.

In summary if you want a centrally managed list of SQL Servers, only use Windows Authentication and don’t use SSAS, SSIS and
SSRS then Central Management Server is probably best. However, if you want a personal local list of SQL Servers and use
SQL Server Authentication then Registered Servers would be best.

In practice I find that I need to use both, as neither provide a complete solution.

It’s worth noting that although I’ve referred to ‘servers’ in the information above, the information is
actually stored at the instance level.