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.
]]>