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.
On this screen you identify the different rights each login will have in the MDW. This is more related to Administration of MDW. There are three different roles identified: mdw_admin, mdw_reader, and , mdw_writer.
1.The mdw_admin role will be allowed to read, write and update data, as well as run purge and cleanup jobs against the MDW.
2.The mdw_reader role only has access to read data in the MDW.
3.The mdw_writer role can write and upload data to the MDW.
If you are a DBA and you want to completely manage the MDW then make sure you give yourself access to the “mdw_admin” role, so that you can add users later if needed.
Once you have completed the wizard the MDW database will have been created, as well as three system Data Collections, and a number of SQL Server agent jobs and SSIS packages.
If you expand the “Data Collection” item in Object Explore within SSMS you will be able to see the three different system Data Collections under the “System Data Collection Set” item.
In addition to the three different system Data Collections a number of SQL Agent jobs and SSIS packages are also created. These SQL Agent jobs and SSIS packages are used to automate the extraction and load process of the MDW for each Data Collection.
System Data Collections
The three different Data Collection items created are: Disk Usage, Query Statistics, and the System Statistics.
1.The Disk Usage Data Collection collects disk space usage information related to Data and Log files associated with each database. This collected information can be used to track the growth rate <>of your databases over time. This data is useful for performing capacity management.
2.The Query Statistics Data Collection collects information about any queries that are run against the SQL Server instance. Since Query Statistics Data Collection is disabled by default you will need to enable it if you want to collect query statistics in SQL 2008.
3.The System Statistics Data Collection gathers performance counter information, like CPU, Memory, etc. This Data Collection allows you to monitor the resource usages of various counters over time, so you can identify resource bottlenecks and trends over time.
SQL Server 2008 Performance Monitoring
The MDW and the Data Collection containers provide DBAs with an easy method to gather performance monitoring data for SQL Server 2008. Having the performance data collection process built into SQL Server 2008 reduces the need to build your own data collection routines. By using the MDW and the Data Collection process DBAs now have the tools necessary to provide them with data that they can use to track the performance of their SQL Server environment over time and perform capacity planning when new hardware needs to be acquired.
Steps to follow for configuring the management data warehouse for multiple instances
1. Open SQL Server Management Studio on the first instance to create and configure the management data warehouse(As shown above).
2. In Object Explorer, expand the Management node.
3. Right-click Data Collection, and then click Configure Management Data Warehouse.
4. Use the Configure Management Data Warehouse Wizard to create a management data warehouse, configure logins, enable data collection, and start the System Data Collection Sets.
5. Open SQL Server Management Studio on other instances and do either of the following:
• Use the Configure Management Data Warehouse wizard to configure data collection for the existing management data warehouse.
• Right-click Data Collection, and then click Properties. On the General tab, specify the existing management data warehouse and the server that it is installed on.
6. Repeat step 5 until all the database instances that use the data collector are configured to upload data to the shared management data warehouse.
Now we can run reports as shown below when needed: