Creating a Central Management Datawarehouse in SQL Server 2008

What is the Management Data Warehouse?
The Management Data Warehouse(MDW) is a single database/Data Warehouse created inside of a SQL Server 2008 instance, which holds various performance related statistics. The performance statistics in the MDW are gathered via special data gathering routines, known as Data Collections. The MDW can contain Data Collection information from a single instance, or it can hold data collected from multiple instances(Steps shown at the end). The Data Collection process uses SSIS and SQL Agent jobs to gather, schedule and purge information in the MDW.

SQL Server 2008 provides three different system Data Collection definitions. These Data Collections are Disk Usage, Query Activity, and Server Activity. Each of these Data Collection definitions identifies the data that is collected, how often should they be collected and how long it should be stored in the MDW. Information for these system Data Collections can be found in the snapshots schema within the MDW. Microsoft has also provided standard report(really good) to allow you to drill down into data collected for each of these collections using SSMS.
If you want to collect your own performance data periodically you are allowed to define your own data collection definitions to feed the MDW database. User defined Data Collection information will be stored in the custom_snapshots schema. For your own data collections you will need to build your own reporting process. You can do that with an application, or building custom reports that can be run directly from SQL Server Management Studio (SSMS) that references your data collected in the MDW.

What is a Data Collection?
A data collection is a container that allows you to specify what data to collect, when to collect it, and how long to keep it. It does this by using Data Collector. Data Collector collects various items from a source using any specific method and then depositing the collected items in target location somewhere. A given Data Collector collects a specific type of data. They are: T-SQL Query, SQL Trace, Performance Counters, and Query Activity. Each data collection is defined against the source where you want to collect the data, and then associated with a collector type to identify the type of data that will be collected.

The data can be collected continuously, on a predetermined schedule, or manually. A data collection also identifies how long the collected data will be retained in the MDW. The Data Collection process handles the loading, and purging of data in the MDW. When data is collected continuously SQL Server caches the information. By caching the information SQL Server 2008 optimize the performance impact of collecting data continuously.

The Data Collection process requires SSIS to transform and store the data collection information and SQL Server Agent schedule to execute the data gathering process. Therefore if you plan to use the MDW then you will need to install SSIS and make sure both SSIS and SQL Server Agent services are running.

Step by Step Setup of Management Data Warehouse :
To set up the MDW you need to run through a wizard. The wizard is started by clicking on “Configure Management Data Warehouse” in “Data Collection” item as below(Just follow the GUI):

< If you are setting up a new MDW database then you need to make sure the login you are running the wizard under has rights to create a database. If you are only using the wizard to set up a new SQL Server instance to use an existing MDW then the login only needs the rights to define users in the MDW database and associate the database users with appropriate roles.

There are two modes for a Data Collection process which are

1.collect data based on a snapshot in time
2.Constantly collect data.

The Data Collections that are constantly collecting data use the “Cache directory” to store collected data between uploads to the MDW. To specify a cache location, either type in the name of the directory, or browse for it by using the ellipse (…) button. If the directory doesn’t exist the system Data Collection processes that require a cache will fail until you create the cache folder specified.

Read More »Creating a Central Management Datawarehouse in SQL Server 2008