Originally published 8/22/2014

Before I start talking about Central Management Servers let me take a moment to better explain what I mean about managing SQL Server at scale.  In the first blog post in this series I failed to mention what I meant by scale.  Scale means a lot of different things.  In this case, scale is meant to the overall size of an environment.  Most often this means having a very large number of databases and/or instances managed per DBA.  

The core of the Froogle DBA’s ability to manage Microsoft SQL Server at scale is Central Management Servers (CMS).  A Central Management Server is an instance of SQL Server that maintains server groups that contain the connection information for one or more instances of SQL Server.  CMS was introduced in SQL Server 2008, so the instance of SQL Server serving as the Central Management Server has to be running SQL Server 2008 or newer.  But you can use CMS to manage instance at least as far back as SQL Server 2000.  Instructions on how to register a Central Management Server can be found here, or you can consult the images below.

The step above will need to be done on all client machines you wish to share Central Management with.  In the case of the Froogle DBA, this means they will want all the members of their team to register the same CMS.

Once you have your Central Management Server registered, the fun really begins.  Now you can create a central version of the truth as to what SQL Instances your team manages.  You would be surprised the number of places I have been where the simple question of “Which instances are we responsible for?” can’t be answered.  I recommend just having a group under your CMS named “Instance List” which you simply place all your managed instances in.  First, this allows you to quickly see all the instances you are responsible for, and secondly we will use this  list later on when we start integrating other SQL Server components with CMS.  In order to create a group in CMS you can follow the instructionshere, or consult the images below.

Now you can register the SQL instances you manage under the “Instance List” group you just created.  If you are like me the first instance you are going to want to create is the Instance serving as your Central Management Server.  This will fail, as the next series of images shows, with the following error .

“You cannot add a shared registered server with the same name as the Configuration Server.
(Microsoft SQL Server, Error: 35012)”

In order to register the CMS server in CMS you are going to need to register it by IP address.  You will find a lot of advise on the internet to use the loopback interface (127.0.0.1) to register the CMS server.  This is great if you want everyone to have to log into the CMS server to use Management Studio.  If you take this advice and connect to a CMS server and run a query on the CMS server everything will look like you are querying the CMS server, but you will really be querying the default instance of SQL Server on the host Management Studio is running on.  Instead, you are better off ensuring that your CMS server has a static ip address (or pin the ip via DHCP).  If you just can’t get a static ip address for your CMS server then you can register it this same way, but in place of the ip address you are going to need to know the port number SQL Server is running on and then enter ServerName,PortNumber (i.e., SQLMON,1433) for the Server Name in the New Server Registration window.  An example setting it up using the ip address in the New Server Registration window is shown in the next images.

Registering the rest of the instances in your environment should be pretty straight forward.  I have run into situations where I needed to register instances where DNS would not resolve the SQL Server.  If you run into this situation you can use the same technique as above (assuming that you have a static ip address for the instance to add).  If the instance is running on a non default port, you may need to add the port number (i.e.,  192.168.1.5,1234 with the ip addresss then a comma then the port number).

Once you have servers registered in CMS, you can now run scripts in Management Studio on either individual servers or the group as a whole.  You can also evaluate policy at the group level and perform a lot more tricks.  For now this is enough information for the Froogle DBA.  If you don’t plan to follow the Froogle DBA plan then you may want to add other groups and register servers manually.  Some example of the groups that I have found useful are grouping servers on the following:

• Version of SQL Server
• Edition of SQL Server
• Applications
• Environment (Development, Test, QA, Integration, Production, etc..)
• Features (SQL Server features such as Mirroring, Availability Groups, Clustering, etc..)

But ultimately the groups you create and use are the ones that are meaningful and useful to you and your team.  There is a lot more about Central Management Servers to go over with for Froogle DBAs, but this is enough to get you started.  We will revisit CMS as we go over other tools in the Froogle DBA’s toolbox.