Originally Posted 8/26/2014

So far we have discussed two of the three core components of the Froogle DBA’s toolbox (Central Management Servers and Extended Properties).  With these two features we can manage systems at scale by improving our ability to apply scripts to all or a portion of our environment, make our environment more self-documenting, and improve communication between the members of our DBA team.  In my last post I showed you how you can populate folders in CMS using the Extended Properties you have defined on your databases using PowerShell.  There is an issue with this approach however.  It makes it more difficult (not impossible) to answer the question “Which databases don’t have Extended Properties set?”.  This is a limitation that the Management Data Warehouse can help us overcome.

The Management Data Warehouse (MDW) is a database that collects data sent by data collectors running on the same, or different, instance(s) of SQL Server.  That is to say that it is really just like any other database, except this database is populated by Data Collectors configured in Instances of SQL Server to gather information of interest.  Out of the box, SQL Server provides Data Collectors that can be configured to collect information of interest in troubleshooting performance problems in SQL Server.  It also ships with some reports designed to run inside of SQL Server Management Studio to analyze this data.

Setting up Management Data Warehouse involves two steps.  First, we need to create the Management Data Warehouse database on the server we want to consolidate data in our environment on.  For the Froogle DBA we will use the same instance of SQL Server we use to host Central Management Server to host the Management Data Warehouse database.  We will do this to facilitate some of the other integrations that make the Froogle DBA framework easier to support and maintain.  Secondly, on each of the instances you want to gather data on, you will need to configure Data Collection to report to the Management Data Warehouse database.

In order to configure the Management Data Warehouse database you will need to follow the images below.

Now that we have a place to put data we collect, we need to tell SQL Server to collect data we can later use.  In order to do this we need to configure Data Collection.  The Data Collection is going to be run as jobs on the instances of SQL Server that are collecting.  As such you need to be aware of the account that the SQL Server Agent is running on each of the machines and ensure the accounts have the appropriate permissions in the MDW database, or you can configure an account for Data Collection to run as and use proxies to run Data Collection as that account.  Some data collection relies on caching data in a folder in the file system.  Go ahead and create a folder for this purpose (i.e., C:\MDWCache) and then make sure that the account that is going to perform Data Collection has full control on the folder.  In order to configure collection follow the steps in the following images.

At this point you have Management Data Warehouse up and running and the default collectors collecting data.  Don’t forget to configure Data Collection following the second set of images on all the instances you wish to manage.  We will show how to use the data collected and some custom collectors important to the Froogle DBA later.