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.