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.
SELECT SERVERPROPERTY(‘Edition’) AS Edition
, SERVERPROPERTY(‘ProductLevel’) AS ProductLevel
, SERVERPROPERTY(‘ProductVersion’) AS ProductVersion
, SERVERPROPERTY(‘Collation’) AS Collation
GO
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.
