SQL Server Data Collection and Management Data Warehouse

, 2018-10-17 (first published: )

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.






Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.


1,567 reads

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...


1,530 reads

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.


360 reads