Central Management Server (CMS), as the name suggests this feature enables the DBAs and System Administrators to administer multiple servers by designating a set of server groups, which maintain the connection information for one or more instances of SQL Server.

When I was choosing the chapters for my book SQL Server 2008 R2 Administration cookbook the Managment of data platform was one of the pre-elected chapter and this CMS feature was set as one of the recipes in this cookbook. The reason is simple, whenever I'm dealing with any administrative problem on data platform then to obtain necessary information I prefer CMS tab on SQL Server Management Studio tool.

In this blog post I'm covering most of the contents from that recipe.

In this recipe, we will go through the implementation strategy for the CMS feature in your SQL Server data platform. Before we continue into the recipe, it is essential for you to understand the core functionality of this feature, which uses the multiple features from the SQL Server core database engine:

• SQL Server system repository
• Manage the SQL Server farm from one location
• Easy reporting about system state, configuration, and performance

CMS is introduced in SQL Server 2008, as earlier versions of SQL Server cannot be designated as a Central Management Server. The main characteristic of CMS is to execute TSQL and Policy-based management policies at the same time against these designated server groups.  The CMS feature implementation is a dual-process (setup and configuration) making use of SQL Server components. The prerequisite of services is to install SQL Server 2008 R2 and Reporting Services. To support the extensive features of the core database engine Enterprise Edition, it is recommended that the CMS be a single point of resource to manage the multiple instances of SQL Server. However, for cost purposes, you can still make use of the Standard Edition.

So how-to-do-it part is important to implement this feature, to get started, we will need to complete the following steps to implement the central management server feature to the existing SQL Server data platform:

1. SQL Server Management Studio is the main tool to designate the CMS feature. Open SQL Server Management Studio, on the View menu, click Registered Servers.
2. In Registered Servers, expand Database Engine, right-click Central Management Servers, point to New, and then click Central Management Servers.
3. In the New Server Registration dialog box, register the instance of SQL Server that will be the central management server.
4. In Registered Servers, right-click the central management server, point to New, and then click New Server Group. Type a group name and description, and then click OK.
5. In Registered Servers, right-click the central management server group, and then click New Server Registration.
6. In the New Server Registration dialog box, register one or more instances of SQL Server that will be members of this server group. 
7. After you have registered a server, the Central Management Server (CMS) will be able to execute queries against all servers in the group at the same time.

There's more to the steps so I recommend to read that chapter in the book for complete understanding with screenshots.

Lets concentrate on best practice in implementing CMS is using TSQL statements and Policy-based management policies can be executed at the same time against the server groups; CMS maintains the connection information for one of more instances of SQL Server. It is essential to consider the effective permissions on the servers in the server groups, which might vary; hence, it is best to use Windows Authentication in this case.

The CMS feature is a powerful manageability feature and is easy to set up. However, it is potentially dangerous to implement a policy that may not be suitable for a set of server groups. In order to identify where I'm executing a query or information from DMVs I recommend you to  to represent the CMS query editor with a specific color that can easily catch our eye to avoid any 'unintentional execution of queries or policies'. To enable such a representation follow these steps:

• ??Open SQL Server Management Studio, on the Tools menu, click on Options. 
• In Options, select Editor Tab and Status Bar.?
• Select the Status Bar Layout and Colors section from the right-hand side of the options window.
• As I've shown in the book a screenshot, shows I have selected the RED color which highlights that extra care is needed before executing any queries

Potentially the SQL Server instance that is designated as a Central Management Server maintains server groups, which maintain the connection information for one or more instances of SQL Server. The best policy is to use CMS and all subsidiary servers on the network are registered using Windows Authentication only; in case local server groups are registered using Windows Authentication and SQL Server Authentication.

Coming to the how-it-works part the storage for centralized management server groups, connection information, and authentication details are available from the msdb system database in:

• dbo.sysmanagement_shared_registered_servers_internal 
• dbo.sysmanagement_shared_server_groups_internal system tables
• dbo.sysmanagement_shared_registered_servers
• dbo.sysmanagement_shared_server_groups system views

So it is evident that MSDB is a key aspect when you need this CMS feature to work, so ensure to perform regular backup for system databsaes too. Also the user and permissions information on the registered servers may vary based on the user permission that uses Windows Authentication connection.

Additionally I would like to remind that for this recipe, we have chosen to select the policy from SQL Server 2008 R2 Best Practices Analyzer that is downloaded from and installed on the server. Before closing out this topic I mention that the ease of executing a single query against multiple-servers at the same time is one of my favorite manageability features. The results that are returned with this query can be combined into a single results pane, or in a separate results pane, which can include additional columns for the server name and the login that is used by the query on each server.