In Microsoft SQL Server 2008, a feature called Central Management Server (CMS) was added to enable the execution of T-SQL commands and Policy Based Management policy evaluation on multiple servers at one time. As someone working in a large enterprise environment, I’m constantly looking for features which enable better, more efficient ways to manage hundreds of servers within a team support environment. While the minimum version requirement to perform as a CMS is SQL 2008, you can register lower level version instances. Now we are talking – because how frustrating is it to have a new feature available, but virtually useless until you have a majority of systems on the new version. So, now you need only one SQL Server 2008 instance and you can start taking advantage of this new feature to manage the current SQL 2000 and SQL 2005 instances already in place.
However, if you read BOL (Books Online), then you’ll see that the instructions for adding groups and instances are very manual and done via the SQL 2008 SSMS (SQL Server Management Studio). Did I mention that we have hundreds of instances to manage? And, did you catch the reference to “groups”? It doesn’t say explicitly in BOL (at least that I’ve found), but an instance can belong to more than one group. This opens up a whole plethora of possibilities for using the CMS. But first we must determine how to automatically create the groups and populate them and maintain them.
A little further investigation reveals that there are now a couple of tables in the msdb database named dbo.sysmanagement_shared_server_groups and dbo.sysmanagement_shared_registered_servers which are designed to contain the groups and registered servers for the CMS. So, now I can bypass the manual registration in SSMS and write a PowerShell script (what else?) to create the desired groups and populate them from our central asset database based on whatever criteria I want the group based on. Then I can schedule that script as a job to run in SQLAgent to ensure the CMS groups are refreshed and relatively in sync with the asset database (considered “the truth”).
The msdb database in SQL 2008 also contains two new roles: ServerGroupAdministratorRole and ServerGroupReaderRole. If you want someone other than the Windows Authenticated logins already in the sysadmin role on the CMS instance to manage your CMS, then you can assign their Windows login to the ServerGroupAdministratorRole. Similarly, if you want others to use the registered groups and servers in this CMS, then their Windows login must be assigned to the ServerGroupReaderRole on the CMS instance.
BOL (2008) states that the CMS Server cannot be a member of a group that it maintains, I haven’t figured out why yet. The work around is to register the CMS server name as servername\instancename, but register it as servername\instancename,port in its CMS Group, this seems to work just fine, but be aware of this caveat.
SQL Server 2008 R2 did not provide any changes to the CMS feature, so you should be able to use either SQL 2008 or SQL 2008 R2 as your CMS. I’ve had no problems using SSMS 2008 with a CMS based in SQL 2008 R2 or using SSMS 2008 R2 with a CMS based in SQL 2008.
The first key to using CMS is to determine what groups make the most sense for your organization. Depending upon what data is available in your asset database, the choices are almost limitless. For instance, you might chose groups based on region alone or based on region plus version for another combination. Thus, you might have groups which look similar to AM, AP, EU, AM_2000, AM_2005, AM_2008, SQL2000, SQL2005, SQL2008, etc. You could also have groups based on classifications such as DEV, QA, and PROD; or even based on application landscapes such as Biztalk and Sharepoint.
Some things to consider when setting up your CMS include connectivity and security. The CMS is not magic – although it might seem like it the first time you perform a multiple-instance query! The CMS does not store authentication credentials, only the server connection info. Thus, when using SSMS to execute T-SQL or policies against a group, Windows Authentication is used. The authenticated account will have no different privileges than if you had connected to each instance in an individual query window using Windows Authentication. You’ll actually see in the status bar of the Query Window in SSMS how many instances in the group were successfully connected (e.g. 9 of 10 or 10 of 10). Therefore, even if you connect successfully to every instance in the group, if your login has differing privileges on the instances, then your results may differ as well depending upon the privileges required to execute the query submitted. So far, this works exactly like Registered Servers in your individual SSMS, however, the benefit is the ability to access the CMS groups and registered servers from anywhere in your organization, by anyone (with the proper access of course!). No longer do you have to keep registering your servers – and your whole team now has access to the same groups and registered servers.
On the connectivity side, if a CMS group contains hundreds of instances, especially if regionally dispersed, it may take a while to establish the connection to all of the instances, so be patient – or consider further dividing your groups into smaller subsets. If an instance is offline or there is a connection timeout or you do not have permissions, then an error\warning will be reported for that instance when you try to execute a query in SSMS against the group; results will still be returned for all other instances.
Also, be very, very, very careful when executing anything other than a SELECT statement – always check the status bar in the Query Editor window to validate the number or instances to which you are connected, which group you are executing against, who you are executing as and to which database you are connected by default.
So for a very simple example of what it can help you do, consider that you have version stored in your asset database and use that to build version groups, but you aren’t 100% sure that these are correct. In SSMS, you can right-click the version group in the Registered Servers in SSMS and select “Run Query”. Then, you can execute the query “Select @@version” or “Select SERVERPROPERTY(‘ProductVersion’)”. You can quickly scan the results for any instances which do not return the expected version.
Hopefully, this provides a good introduction to CMS and has started you thinking about how you can use it outside of SSMS…which will be the subject of my next post!
Filed under: SQL Server, SQLServerPedia Syndication | Tagged: Automation, CMS, Enterprise Management, Policy-Based Management, SQL Server 2008, SQL Server 2008 R2, SSMS | 3 Comments »
This is a consensus-based development of security best practices which have become the de facto security configuration standards. If you are in charge of your SQL Server security configuration, you need a copy of this document – it is what your auditors will be using soon! 
