The Froogle DBA – Extended Properties

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.

Right click on the database and select "Properties".
Right click on the database and select “Properties”.
On the left side click on the "Extended Properties" tab.
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).
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".
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".
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.

9525218_orig

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