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.

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 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's there or how to use it.

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

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.

SQL Server Data Collection And Management Data Warehouse 
 
SQL Server Data Collection And Management Data Warehouse 

Here, you need to choose your 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 and name it MgtDW. Also, one Management Data Warehouse can act as a central collection store to house all collection sets for multiple servers.

SQL Server Data Collection And Management Data Warehouse 

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

SQL Server Data Collection And Management Data Warehouse 
 
 SQL Server Data Collection And Management Data Warehouse
 
 SQL Server Data Collection And Management Data Warehouse

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

SQL Server Data Collection And Management Data Warehouse 
 
 SQL Server Data Collection And Management Data Warehouse
 
 SQL Server Data Collection And Management Data Warehouse

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 set up, you will see new jobs.

SQL Server Data Collection And Management Data Warehouse 

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

SQL Server Data Collection And Management Data Warehouse 

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

SQL Server Data Collection And Management Data Warehouse 

Now that you are you 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.

SQL Server Data Collection And Management Data Warehouse 

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.