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.

The Froogle DBA

Originally published 8/20/2014

First, I really meant Froogle and not Frugal.  Froogle is a play on words combining Frugal and Google.  The Froogle DBA is the name of my talk that I will be giving at SQL Saturday #320 in Raleigh, NC on 6 September 2014.  It is basically a presentation on how I have seen components of SQL Server put together along with some open source monitoring.  This configuration has allowed a team of 2 Database Administrators to manage about 2,000 databases on over 70 instances of SQL Server.  They are able to provide 24×7 on-call support to everything from departmental to mission critical systems.  Technically speaking I pitch in on the after hours rotation, so you could argue it is 2.5 Database Administrators, but either way this is managing a lot.

This framework is frugal because it allows our employer to get the most from it’s SQL Server and human capital investments.  The Google comes in because we have found that this framework makes it very easy for us to take information, and/or scripts, we find through with our google-fu and integrate them into our environment.  The major components of this framework are Central Management Server, Extended Properties, Management Data Warehouse, Policy Based Management, and an open source monitor.  Right now, that monitor is Zenoss.  I have seen other open source monitors used with great success as well.  We like Zenoss because it provides agentless monitoring of our servers.  In our minds that is one less thing to maintain.

I plan to post on each of these topics to provide some background for my presentation.  This will be information that I won’t have time to cover, but compliments the presentation.  The hope it that through the information on this will provide a richer understanding of how to successfully implement the framework.  I hope this information is as beneficial to you as it has been to us.

SQLSaturday #320 Raleigh 2014

Originally published 8/20/2014

4058256_orig

I was humbled to have been selected to present at the upcoming SQL Saturday event in Raleigh, NC  on 6 September 2014.  I will be giving a talk on “The Froogle DBA”.  No that is not a typo, it is me trying to be clever.  If you want to know what it means then you’ll have to come down and listen to the talk.  In the process of preparing my presentation I quickly realized two things.  First, the topic I am discussing is way bigger than can be fully covered in 75 minutes.  Second, I needed a place to put all the scripts and other goodies for the talk.
Well, this is the space that I have created to hopefully handle both of these issues.  I plan to use this blog as a place to cover the content of the presentation in more depth.  I also plan to provide copies of my slides and scripts here as well.  So, stay tuned for more great information on how to use your existing SQL Server investment and some open source and freely available tools to manage and monitor SQL Server at scale.