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.