O'Reilly logo

Professional Microsoft® SQL Server® 2008 Administration by Steven Wort, Ross LoForte, Wayne Snyder, Ketan Patel, Brian Knight

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required