Professional Microsoft® SQL Server® 2008 Administration

13.8. Management Data Warehouse

New to SQL Server 2008 is the Management Data Warehouse and Data Collection. This is a new framework for data collection, storage, and reporting that for SQL Server 2008 will be the start of an entirely new architecture due to roll out over the next few releases of SQL Server.

For SQL Server 2008, you get the basics of Data Collection, a few Data Collection sets, the Management Data Warehouse, and some reports.

Now for some of the basic concepts:

• Data provider: A data provider is a source of data to be captured. SQL Server 2008 has four data providers:

  • SQL Trace

  • Performance Monitor Counters

  • T-SQL

  • Query Activity

• Collection item: A collection item is a specific item of data to be collected. This might be a single performance monitor counter, a SQL Trace event, or a T-SQL query of a DMV.

• Collection set: A collection set is a logical grouping of collection items that are collected together. This might be all the performance counters monitoring disk I/O, or all the SQL trace events to look for long-running queries.

• Management Data Warehouse (MDW): The Management Data Warehouse is where the items in each collection set are stored. It is the repository of historical data that you have collected.

• Target servers: The target servers are the systems that you want to monitor. Ideally, the MDW should be on a separate server. If it's on one of the target servers, you run the risk of recording activity about the data collection, rather than the target server you are really ...