Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Performance Data Collection and Warehouse Feature of SQL Server 2008 Part 1


By:   |   Read Comments (3)   |   Related Tips: 1 | 2 | More > Performance Data Warehouse

Attend these FREE MSSQLTips webcasts >> click to register


Problem

As we are looking through the new features in SQL Server 2008 we found a potentially interesting one called Performance Data Collection and Warehouse feature which we can use to store performance data from multiple SQL Servers Instances within an organization into a single central location. Can you give us a detailed explanation of how we go about using this new feature?

Solution

Performance Data Collection and Warehouse is a new feature in SQL Server 2008 which can be used by database administrators to gather performance related data using the built-in data collectors which are also known as data collection containers. Using data collectors you can gather performance data from multiple SQL Servers and store it within a Management Data Warehouse (MDW).

Management Data Warehouse is a SQL Server database which gets created once you have configured the Data Collection feature. The MDW database can store the data from a single SQL Server instance or from multiple instances. The Performance Data Collection process requires SQL Server Integration Services (SSIS) to be installed as it is required to transform and load the data collected with the help of SQL Server Agent Jobs to the MDW database.

The Performance Data Collection and Warehouse feature of SQL Server 2008 is supported in Enterprise, Standard, Workgroup, Web & Developer editions. However, this feature only works on SQL Server 2008 instances and it is not backward compatible with previous versions of SQL Server.

Configuring Performance Data Collection Feature in SQL Server 2008

The initial step is to "Configure Management Data Warehouse"; this can be done by expanding the "Management" node in Object Explorer, and then right click "Data Collection" and choose the "Configure Management Data Warehouse" option from the dropdown as shown in the below snippet.

This will open up "Welcome to the Configure Management Data Warehouse Wizard" screen; click Next to continue with the wizard. It is recommended to use a system admin account while configuring Performance Data Collection.

In the "Select configuration task" wizard screen you can configure a management data warehouse or a data collection set. You need to choose the first option which is "Create or upgrade a management data warehouse" as you need to first create a management data warehouse for storing the data collection set results. The second option which is "Set up data collection" needs to be used when you want to configure an instance of SQL Server to start collecting data on an existing management data warehouse. Click Next to continue with the wizard.

In "Configure Management Data Warehouse Storage" wizard screen you need to choose either an existing database or create a new database by clicking the "New" button. In this example I have selected ManagementDB as my MDW database. Click Next to continue with the wizard.

In "Map Logins and Users" wizard screen, you can map existing logins to different database roles on the MDW database. There are basically three different database roles namely mdw_admin, mdw_reader and mdw_writer available by default within a MDW database.

  • The mdw_adminrole will allow users to read, write, update and execute SQL Server Agent which can purge and cleanup data available within a MDW database.
  • The mdw_reader role will allow users to read data which is available within a MDW database.
  • The mdw_writer role will allow users to write and upload data to a MDW database. Once you have assigned appropriate permissions then click Next to continue with the wizard.

In "Complete the Wizard" screen you can verify the configuration details which you have selected so far, if you are fine with the configuration information click Finish to complete the configuration of Management Data Warehouse (MDW) database.

Once the "Configuration Management Data Warehouse Wizard" is successful you will get the below screen. Click Close to exit the wizard.


As of now, you have successfully configured your Management Data Warehouse database. However, you haven't started collecting the performance data. This can be done by configuring the current instance of SQL Server 2008 as a Data Collection.

Set up Data Collection

Next step will be to set up the current instance of SQL Server 2008 as a Data Collection, you need to once again start "Configuration Management Data Warehouse Wizard" and choose "Set up data collection" option as shown in the below snippet. Click Next to continue with the wizard.

In "Configure Management Data Warehouse Storage" screen you need to choose the Server Name and Database Name where Management Data Warehouse (MDM) database resides. Next, you need to provide the custom "Cache Directory" path. The cache directory will be used to store the data locally before it is uploaded to the management data warehouse. A blank value uses the TEMP directory for the collection process. Click Next to continue with the wizard.

In "Complete the Wizard" screen you can verify the configuration details which you have selected so far, if you are fine with the configuration information click Finish to complete the configuration of Data Collection Wizard. Finally Click Close to exit the "Configure Management Data Warehouse Wizard".

Conclusion

In this tip you have seen how easily you can configure Performance Data Collection and Warehouse feature of SQL Server 2008. In my next tip you will learn more about different types of Data Collection Reports and I will also cover the steps which you can use to manually alter Data Collection Set properties.

Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Ashish Kumar Mehta Ashish Kumar Mehta has been contributing to the MSSQLTips.com community since 2009 with over 60 tips.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Thursday, November 13, 2014 - 11:53:50 AM - Cibi John Back To Top

 

Its stated that "Using data collectors you can gather performance data from multiple SQL Servers and store it within a Management Data Warehouse (MDW)". Can you please explain how to collect data from more than one instance ?

 

I have noticed that somebody else raised the same query, but doesn't see any response. Kindly assist.


Friday, January 03, 2014 - 7:43:44 AM - Anup Kaluskar Back To Top

Hi Ashish

The article you have written on SQL Data Collector is very good !

I would like to know how to connect multiple servers to my MDW db on a Dev server. I am running SQL Server 2008 R2 Ent. Edition.
So basically I want to have a repository of production peformance data on a Dev server.

I want to avoid at all costs to configure MDW on production due to performance overheads.

If you have any tips or suggestions on how to perform this please let me know as it may help others.

Many Thanks

Anup Kaluskar
SQL DBA


Friday, October 04, 2013 - 8:15:12 PM - SQLDBA Back To Top

how to see report  for server activity ?

 


Learn more about SQL Server tools