Introduction
Central Management Server (CMS) was first introduced in SQL Server 2008, it enables simplified management of multiple MS SQL database servers and instances. My screenshots are taken from SQL Server 2012 but should be relevant in SQL 2008 and 2008 R2 as well.
If you are managing more than a couple of SQL instances you’ll already be aware of the difficulty in keeping track of them and their configuration. Through the use of CMS, it is possible to collate all the information you require across your SQL estate in one convenient place.
The ability to execute queries across groups of servers is a very powerful one, plus if you implement Policy Based Management (PBM) you’re onto a winner with the combination of both CMS and PBM.
A word of warning before we start
It is worth pointing out at this early stage that the server you are connected to when performing the following steps will become the CMS repository, i.e. the “main server”, all other servers should be registered underneath this one.
Note: the CMS repository server cannot manage itself, you will get an error if you try and register it with itself:
If you want to monitor the CMS repository server you’ll need to register it on another server.
Ideally, CMS should be set up on a non-production server if possible, this could be a SQL Express edition server which not only works fine from 2008 upward but provides the great benefit of being free!
CMS and Security
Security is taken care of by two roles within the msdb database. The first, ServerGroupReaderRole, allows the user to connect to CMS and the servers listed wsithin. The second role, ServerGroupAdministratorRole, grants the user the ability to add, remove and amend servers (referred to as connections in some documents).
Although you may be a member of the sa server role on the CMS server, this does not necessarily mean that you have “carte blanche” access to all the servers under CMS. You will still need privileges on the servers in order to connect and query them.
The authentication method for servers within CMS defaults to that specified in the CMS server properties box. So, if you choose Windows Authentication for the CMS server then subsequent servers will also require Windows Authentication. By way of this, you may find that you have unlimited access to one server where you a member of the sysadmin role but on another server you can only access one particular database.
Getting Started
To access CMS, open SQL Server Management Studio (SSMS) and from the View menu select Registered Servers.
This will open the Registered Servers panel, in the below image I have expanded the nodes although there isn’t anything to talk of at the moment.
Groups and Servers
Before you go adding all your servers, it is worth considering how you want to reference them in the future. You can create Server Groups to, well, group your servers! The groups can be based on business areas, geographic locations, SQL Server versions or anything else that is useful to you.
Adding a Server Group is simple, just right-click the CMS server, choose New Server Group and give it a name and description (if necessary). Click OK and job done, a new group will appear. Groups can also be nested, so you have a Production group and a Development group under each geographic location.
To add servers, right-click the group you’d like to add the server to and select New Server Registration.
The servers added don’t have to be 2008 upward, CMS allows you to register SQL 2000 and SQL 2005 too. It is worth bearing in mind that not all queries will be compatible across all versions of SQL so consider this when preparing and executing them.
CMS Metadata
The data about CMS itself is stored in two tables in the msdb database:
- sysmanagement_shared_server_groups_internal and
- sysmanagement_shared_registered_servers_internal.
Looking at the first table, sysmanagement_shared_server_groups_internal, you can see a list of the built-in groups and also those groups you set up earlier. There is a reference to the type of group that each custom group relates to (DatabaseEngineServerGroup in the example outlined in this blog), also a parent_id which is used to create the tree of groups in the Registered Servers panel.
The second table, sysmanagement_shared_registered_servers_internal, lists the servers, their type and the group that they belong to.
This information can be used externally if you wish to display the structure via another medium such as a webpage. It is worth making sure that these tables, if not the whole msdb DB, are backed up properly.
The next post will illustrate what can be done with CMS…