Skip Navigation LinksHome > Categories > Code from a Category

How to Configure and Use the Central Management Server Feature in SQL Server 2008



User Name: codelecturer
Name: Mike Chauhan
Contact Me: www.datawebcoder.com/ContactUs.aspx
Home Page: www.datawebcoder.com
6 years of total IT experience including programming, application development, System Maintenance . 3 years of Experienced and expertise in .Net Framework VB, Visual Basic.Net, ASP.Net 2.0, 3.5 and AD... [More]
Viewed Times: 2311
Add Date: 10/26/2012
In this article we will learn about how to configure and use the Central Management Server feature in SQL Server 2008
SQL Server 2008 introduces a new feature called Central Management Server (CMS) which can be used by Database Administrators within an organization to manage all the SQL Servers like 2000, 2005 and 2008 using an instance of SQL Server 2008.

Central Management Server (CMS) acts as a central repository which holds the list of all the SQL Servers within an organization that needs to be managed by the team of dedicated DBAs. It would be ideal to configure a dedicated instance of SQL Server 2008 Developer Edition to act as a Central Management Server.

Configuring a Central Management Server (CMS)

1. Connect to SQL Server 2008 Instance using SQL Server Management Studio
2. On the View menu, click Registered Servers.
3. In Registered Servers window, you need to expand the Database Engine node and right click Central Management Servers to select Register Central Management Server…. from the popup window as shown in the below snippet.



4. In New Server Registration dialog box, you need to mention the name of the SQL Server Instance which will act as the Central Management Server (CMS). Central Management Server requires Database Administrator to register a SQL Server which will act as a CMS. All the SQL Servers which are registered to Central Management Server needs to be configured using Windows Authentication Mode. You won’t be able to register SQL Servers using SQL Server Authentication mode as it is not supported.



In the Connection Properties tab you can make the changes as appropriate or else you can use the default settings. To check the server connectivity you can click Test button and finally to create a Central Management Server click on Save button.

5. Once the Central Management Server is configured successfully then the next step will be create different groups under the registered CMS to organize SQL Servers. You can register SQL Server like 2000, 2005 and 2008 in CMS using SQL Server 2008. To create a new server group, right click the Central Management Server and click New Server Group…. as shown in the below snippet.



6. In the New Server Group Properties you need to provide the Group Name and a small Group description if you like. For this example the Group name used provided is MySQLServers, click OK to save the group name.



7. Next step will be add SQL Servers which needs to be managed using Central Management Server (CMS). You can add SQL Server 2000, 2005 or 2008 servers to CMS. In order to add a SQL Server, right click Group name (MySQLServers) and select New Server Registration…. from the popup window as shown in the below snippet.



8. In the New Server Registration dialog box, you need to add one by one all the SQL Servers which needs to part of MySQLServers group and needs to be managed using Central Management Server (CMS). All the SQL Servers which are registered to Central Management Server needs to be configured using Windows Authentication Mode.



In the Connection Properties tab you can make the changes as appropriate or else you can use the default settings. To check the server connectivity you can click Test button and finally to register SQL Server click Save button.

9. Once all the SQL Servers which you want to manage using CMS are registered under a particular user group, then the next step will be to right click the user group and click New Query as shown in the below snippet to execute the query against all the servers which are part of that group.



10. In the New Query window, execute the below mentioned query to identify the SQL Server Edition, Product Level, Product Version and SQL Server Default Collation Server Properties.





When you are executing the above query using CMS, internally SQL Server runs the query against all the SQL Servers which are registered under the group independently and finally the results are merged and displayed. If you want to change the way the results are display then you can go to Tools > Options > Query Results > SQL Server > MultiServer Results and make the changes as appropriate.



Export Registered Servers from Central Management Server

Database Administrators can export the list of registered server as a “.regsrvr” file. This can be done by right clicking Central Management Server and then choose Tasks > Export…. In the Export Registered Servers dialog box provide the file location where the registered servers need to be saved. Finally click OK to export the registered servers to a .regsrvr file as shown in the below snippet. The generated file can be opened using an Internet Explorer or a Notepad to view and modify its content.



Import Registered Servers into Central Management Server

Database Administrators can import the list of registered server which is saved as “.regsrvr” file. This can be done by right clicking Central Management Server and choose Tasks > Import…. In the Import Registered Servers dialog box provide the location of .regsrvr file which has the list of registered servers. Finally click OK to import the registered servers from .regsrvr file as shown in the below snippet.



Deleting Registered Servers in Central Management Server

Database Administrators can delete any registered server which is no longer required to be managed using Central Management Server. This can be done by right clicking the server and then choose the Delete option from the popup window.



Different Administrative Options Available in Central Management Servers

Using Central Management Server database administrators can not only execute multi server queries but they can also evaluate and import policies created using Policy based Management feature of SQL Server 2008. To know more about Policy Based Management you can refer to my previous article titled “Configure and Manage Policy Based Management in SQL Server 2008”. In order to see all the registered servers within an object explorer, you can right click the server group and choose Object Explorer option from the popup window.

You can even start, stop, pause, resume or restart SQL Server using CMS as shown in the below snippet. If you want to open up SQL Server Configuration Manager then you need to right clicking the individual registered server and choosing SQL Server Configuration Manager… option from the popup window.



Some Drawback in Central Management Servers

1. You won’t be able to register the server which is acting as CMS under any of the groups, which means it is better to configure Central Management Server as an independent instance altogether. For this purpose you can ideal use a SQL Server 2008 Developer Edition.
2. It works only with Windows Authentication.

Permissions Required to Manage and Use Central Management Servers

In order to manage Central Management Server you need to be a member of ServerGroupAdministratorRole database role within MSDB database.

In order to provide a new user access to Central Management Server (CMS), the user needs to be a member of ServerGroupReaderRole role within MSDB database as shown in the below snippet.



Conclusion
The Central Management Server is a great feature introduced in SQL Server 2008. Using CMS feature database administrators can manage all SQL Servers like 2000, 2005 and 2008 from one instance of SQL Server 2008. As Central Management Server acts as a central repository to hold the list of SQL Servers within an organization it becomes easier for DBA to maintain and mange all servers within an organization by maintaining CMS entries up to date.

Happy Programming!!!

Post a Comment

Name: (Optional)
Email: (Optional, you can get an email if somebody replys your comments)*
Email me if somebody respons my comment below:
Details**:
Enter Text
as Below:
(case insensitive, if hard to read, click the "get a new one" button)
 
    
* Your email address will not be shared with any third parties for any reason.
** Maximum 1000 charactors.