Central Management Server

Introduction:
A SQL Server CMS ( Central Management Server ) is just a central repository that holds a list of managed servers. SQL Server 2008 SSMS introduces a new feature, Multiple Server Query Execution, in Query Editor. 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. 

Some of the usages for adding the sql instances to CMS are listed below :

•  Configure group of servers
•  Generate report or document from multiple servers
•  Analyze result from multiple servers
•  Run any SQL query against multiple servers

Pre-requisite:
1. SQL Server 2008 for Registering Central Management Server
2. SQL Server 2008 or SQL Server 2005 or SQL Server 2000 servers other than the registered Central Management Server.

Now we will setup central management server using SSMS( SQL Server Management Studio ) in SQL Server 2008.

How to:
To register a central management server and run the query against all the registered servers follow the below steps.

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”

3. Then register the SQL 2008 server.

4. Create the sub folders and register the required servers.

5. To run a query against all the servers, right click on the central management server and select “New Query”

6. In the Query editor, type your query and execute it.

Select SERVERPROPERTY('ProductVersion') AS 'Version', 

SERVERPROPERTY('ProductLevel') AS 'Level', 

SERVERPROPERTY('Edition') AS 'Edition'

7. In the results, you can notice that for each record the respective server name will be displayed.

Note :  Centralize the management and administration of a number of SQL Server instances from a single source can allow     the SQL DBA person to save significant time and effort.  Also similar procedure to add latest SQL servers(2012,2014,2016).