Registered Servers vs Central Management Servers

Category : Articles Published : January 8, 2017 User Rating : 4.5 Stars      Views : 3 Stars
One Click Feedback
Please help us to improve the site by rating the quality of this article by clicking a button below.




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 :

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.
Link back to this article : https://www.sqlmatters.com/Articles/RegisteredServersvsCentralManagementServers.aspx

Keywords

SQL 2012, 2008R2, 2014


Comments
Post by Raymond on Thu 20 Sep 2018 23:22. Report Inappropriate Post

Honestly, I wish I saw this article a week earlier. ALL , and I mean *ALL* of Microsoft's and other bloggers info on this is so useless: I was looking at the concepts, the core concepts of WHY we need them, and whether something is LOCAL or available to everyone. This author has nailed the concept. I'll be bookmarking this site for ALL future reference.

If anyone needs to know how to setup Registered Servers, Microsoft provides step by step guide, but they don't tell you what it is to begin with!
Post by Caius on Fri 14 Dec 2018 15:21. Report Inappropriate Post

Very concise approach. This guy explained concisely about this topic. I recommend to everyone to read the concept behind registered servers usage on this site. Thanks a lot.
Post by Raphael Ferreira on Mon 17 Apr 2023 11:54. Report Inappropriate Post

I know this post is old, but, how does one go about using the CMS server in SSIS, please?

Post a comment   No login required !

 
Name :   Email :   Website :  
Will be displayed alongside your comment
Not displayed Optional, but displayed if entered