SQL Server Data Collection and Management Data Warehouse

We all have the 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 in with 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 definition 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 there or how to use it.

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

First steps to setting 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.

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

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

Once you have set up your DW now it’s time to setup your data collecting.

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 you collecting data you can see what’s being collected easy in Reports. To get to these reports you 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 setup and start using. Keep in mind I would expect a performance hit on the server are 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.