Managing multiple SQL Servers has its challenges. One of the big challenges is having to collect data from each of the servers you manage and figure out which servers need attention. SQL Server has introduced a new feature called Central Management repository which can be used to manage multiple instances. One of the challenges to using CMS is that you have to register all the SQL Servers manually into CMS. The below article will guide you on how to register hundreds of servers in a second for SQL Server CMS.
Steps to distinguish production and non-production SQL Servers registered in CMS while executing the query. Below article will guide you how to use customize colors in CMS.
To identify the production and non-production SQL Instances registered in CMS within the same group; you can use the Custom Color feature. In this article I will guide how to use to the custom color feature of CMS.
Step 1: Connect to Central Management Server (CMS) from SSMS.
Step 2: Right click on the registered CMS server and select properties.
Step 3:Go to Connection Properties tab, checked the Use Custom color box and select the desired color. Click on Save to proceed.
Step 4: Right click on the SQL Instance and Open a new query window. You can see the blue color in the status bar. This way we can identify the production and non-production box. By specifying the custom colors.
Ouput Image of SSMS
Central Management Server
SQL Server Central Management Server is just a central repository that holds a list of managed servers. Microsoft has introduced CMS feature in SQL Server 2008 SSMS.
Use of Central Management Server
1. Multiple Server Query Execution, we can execute query against multiple servers and get the result at source.
2.Centralize the management and administration of a number of SQL Server instances from a single source can allow the DBA to save significant time and effort.
3.Evaluate policy against the multiple server from single source.
4.Control Services and bring up SQL Server Configuration Manager
5.Import and export the registered servers:
You must have at least 1 SQL Server 2008 instance which can be used as CMS
Steps to Create CMS and Register Server
1. Open the “Registered Servers” from the “View” Menu in the management studio of SQL server 2008.
2. Right click on the Central Management Servers and select “Register Central Management Server” and Register the SQL Server 2008 instance as CMS.
3. Create the groups under Registered CMS servers to define the group for each server.
4. Right Click on groups and register the all the SQL Server instances as per their group. (SQL Server 2000,2005 and 2008)