Performance Data Collection and Warehouse Feature of SQL Server 2008

http://www.mssqltips.com/tip.asp?tip=1746

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