The Froogle DBA – Central Management Servers

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.

Right click on “Central Management Servers” on the Registered Servers tab and select “Register Central Management Server…”
Enter the connection information for the SQL Server 2008 or newer instance you want to use as your Central Management Server and click "Save".
Enter the connection information for the SQL Server 2008 or newer instance you want to use as your Central Management Server and click “Save”.
Now your Central Management Server will show up in the list under Central Management Servers. Note: All DBAs on your team will need to do this to see the servers and groups you register.
Now your Central Management Server will show up in the list under Central Management Servers. Note: All DBAs on your team will need to do this to see the servers and groups you register.

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.

Right click on the CMS Server name (or a group to create a subgroup) and select "New Server Group...".
Right click on the CMS Server name (or a group to create a subgroup) and select “New Server Group…”.
Fill in the group name and description and select "OK".
Fill in the group name and description and select “OK”.

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)”

Right click on the group you want to add an instance to and select "New Server Registration...".
Right click on the group you want to add an instance to and select “New Server Registration…”.
Enter your CMS server information and select "Save".
Enter your CMS server information and select “Save”.
The error message you get.
The error message you get.

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.

Enter ipconfig from the command line in a command window on your CMS server. If you can't log into your CMS server you can ping the server by running "ping CMSServerName" replacing CMSServerName with the name of your CMS server. Record the ip address assigned to your CMS server.
Enter ipconfig from the command line in a command window on your CMS server. If you can’t log into your CMS server you can ping the server by running “ping CMSServerName” replacing CMSServerName with the name of your CMS server. Record the ip address assigned to your CMS server.
Enter the ip address in the "Server Name" box towards the top of the New Server Registration window. Then to display a more friendly name change the "Registered Server Name" box towards the bottom of the window to be the name of your CMS server. Click save.
Enter the ip address in the “Server Name” box towards the top of the New Server Registration window. Then to display a more friendly name change the “Registered Server Name” box towards the bottom of the window to be the name of your CMS server. Click save.
You now have your CMS server registered inside of CMS and it will work regardless of what machine you connect to CMS from.
You now have your CMS server registered inside of CMS and it will work regardless of what machine you connect to CMS from.

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.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s