Originally posted 8/23/2014

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.

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.

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.