SQL Server Data Collection And Management Data Warehouse

Introduction 

We all need to collect system and performance information regarding our SQL Servers. Some of us use third-party tools, SQL Trace, or a homegrown solution. Did you know Microsoft has a built-in tool to help you accomplish this? It’s called Data Collection and the data it collects (collection sets) is stored in a relational Management Data Warehouse. The data collected is used to generate reports giving us very readable and useful insights to our servers. Not only can you get performance information, but you can also use SQL Profiler to export trace definitions and create custom collection sets. This is not a new SQL Server feature, it has been around for some time. I find however that it is not used as much as it should be and that could be just because many don’t know it's there or how to use it.

Let’s quickly set one up and show you how easy this is to configure.

Setting up the management data warehouse

The first step to set this up is to create a Management Data Warehouse to store the information in a collection. You can accomplish this by using a very simple wizard.

Under Management, you will find Data Collections. Right-click and choose Tasks then Configure Management Data Warehouse.

Monitoring data collection

Here, you need to choose the server you want to store your Data Collection Data into. Then, choose a database for your data. In this case, I choose to create a new one and name it MgtDW. Also, one Management Data Warehouse can act as a central collection store to house all collection sets for multiple servers.

Accessing reports

Next, you need to grant access to the users. This is done by Roles.

Once you have set up your DW, now, it’s time to set up your data collection.

Data collection uses SQL Agent and SSIS to collect data and populate the data. I am not going to dive into the details of exactly how it works in this blog. Data Collection either runs constantly or on a user-defined schedule. After you complete your setup, you will see new jobs.

If you go back under Data Collection you can now see that it is collecting data on your server.

Looking at logs you can now see that data is being collected.

Now that you are collecting data, you can see what’s being collected easily in Reports. To get to these reports, right-click on Data Collection, not the System Data Collection Set.

Here is a link to find all the information you will need on what these reports will show you.

You can see how easy this was to set up and start using. Keep in mind I would expect a performance hit on the server you are collecting data from (target server). Though minimal, keeping impact in mind is always important. SQL Server Data Collection is a great way to get important system information on your servers and is part of your STANDARD edition of SQL Server.