In the previous blog post we introduced Central Management Servers (CMS) and went over how to set one up and create groups and add servers. Central Management Servers provide the Froogle DBA the ability to centrally manage their environment and share a common list of instances with other DBAs on their team. The power of CMS really begins to grow however when you have it set up so you can apply scripts and evaluate policy to groupings of like instances. For example, if we are asked to tell us what applications are still running on SQL Server 2005 instances it would be great to have a group in CMS defined in CMS with just SQL Server 2005 instances in them. We could then query the group to return the list of database names on all SQL Server 2005 servers rather quickly.
But how do we figure out what application these database support. One way would be to keep track of the mapping of databases to applications in either a spreadsheet or a table in a databases. But there is another more scalable option, Extended Properties. Extended Properties are key value pairs that can be stored inside of a databases and associated with the database itself or any of the objects inside the database. You can see the extended properties in Management Studio by right clicking on an object and selecting properties. In the property window there will be a tab for Extended Properties. The following images show how you can see the Extended Properties for a database.
Right click on the database and select “Properties”.On the left side click on the “Extended Properties” tab.On the Extended Properties tab you can see the key value pairs that have been defined for the object (or not defined as in this case).
To add an extended property to the database it is as easy as typing the key in the first empty row under “Name”. Then if you want to assign a value to the property just enter that next to it under “Value”. The following images show how to add an Extended Property.
Enter the name of the Extended Property under “Name”.If you wish to assign the property a value you can do so now by entering it next to the name of the Extended Property under “Value”.
But why would we want to use these Extended Properties? In order for us to scale our support for SQL Server systems we have to do our best to make the environment as self documenting as possible. So, as in the examples above, you can create an Extended Property in each of your databases that keeps track of the application(s) supported by the databases. This means that if the database moves from instance to instance you always know what the database is supposed to be supporting. This also allows you to answer the question proposed above definitely with a single query without having to translate databases to applications using a spreadsheet or a lookup table.
Below is an image of some Extended Properties I have found beneficial over the years. We have discussed how you can use CMS to query Extended Properties to answer questions, but we can also improve Central Management Servers with Extended Properties. You will notice in the image that there are two Extended Properties on this database prefixed with “CMS”. We will show in a later blog post how we can use these Extended Properties to build dynamics groups inside of CMS. But that is for another day.
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”.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…”.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…”.Enter your CMS server information and select “Save”.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 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.
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:
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.
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.
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.
So I started the ModernDBA blog on Weebly and it served me well in the beginning. But I wanted a little more control than they provided, so I am trying out WordPress to see how I like it. I’ll be working on moving my existing blog posts over to the new site over the next week or two. I apologize in advance for any inconvenience as I work through this migration or content and domain name.
moderndba 
You must be logged in to post a comment.