MS SQL Srver 2008 :Using the Performance Data Collector -Exercise2

                            Using the Performance Data Collector
Scenario

In this exercise, you will use the Performance Data Collector to gather data from a SQL Server instance and store it
in a central management data warehouse. The Performance Data Collector enables you to collect, analyze,troubleshoot, and store SQL Server diagnostics information through low overhead collection, centralized storage,and built-in analytical reporting capabilities.
Tasks Detailed Steps Complete the following task on:

1. Configure a management data warehouse

a. In SQL Server Management Studio, in Object Explorer, verify that the SQL
Server Agent is running. If it is not running, start it.
Note: SQL Server Agent is required to run scheduled data collection jobs for the Data
Collector
b. In Object Explorer, expand the Management node, right-click Data Collection,
and then click Configure Management Data Warehouse.
Note: The Data Collector uploads the performance data it captures to a management
data warehouse. You can configure multiple SQL Server instances to upload their data
to a single, central management data warehouse, making it easy to monitor database
server activity and performance across the enterprise. In the rest of this procedure,
you will create a database for the management data warehouse and map the local
Administrator login to the administrative roles in the management data warehouse
database.
c. On the Welcome to the Configure Management Data Warehouse Wizard page,
click Next.
d. On the Select configuration task page, click Create or upgrade a management
data warehouse, and then click Next.
e. On the Configure Management Data Warehouse Storage page, click New. Then
in the New Database dialog box, in the Database name box, type MgmtDW and
then click OK.
f. On the Configure Management Data Warehouse Storage page, click Next.
g. On the Map Logins and Users page, in the Users mapped to this login list, select
the MIAMI\Administrator check box. Then in the Database role membership
for: MgmtDW list, select the mdw_admin check box. Then click Next.
h. On the Complete the Wizard page, review the configuration, and then click
Finish. When configuration is complete, click Close.
i. In Object Explorer, right-click Data Collection, and then click Configure
Management Data Warehouse.
j. On the Welcome to the Configure Management Data Warehouse Wizard page,
click Next.
k. On the Select configuration task page, click Set up data collection, and then
click Next.
l. On the Configure Management Data Warehouse Storage page, click the ellipsis
(…) next to the Server name box, and then click Connect to connect to the (local)
SQL Server instance.
m. In the Database name box, click MgmtDW, in the Cache directory box, type
C:\SQLHOLS\WhatsNewForDBAs\Starter\Mgmt and then click Next.
What's New in Microsoft® SQL Server® 2008 for Database Administrators
Tasks Detailed Steps
n. On the Complete the Wizard page, review the configuration, and then click
Finish. When configuration is complete, click Close.

2. Modify the configuration of the System Data collection sets.

a. In Object Explorer, under the Management node, expand Data Collection, and
then expand System Data Collection Sets.
Note: SQL Server 2008 includes a number of built-in data collection sets. You can
configure each of these to suit your specific needs. For example, in the remaining
steps of this procedure you will configure the Disk Usage collector set to retain data
for 90 days.
b. Right-click Disk Usage, and then click Properties.
c. In the Data Collection Set Properties dialog box, in the Retain data for box,
type 90 and then click OK.

3. View collected data
a. In Object Explorer, right-click Disk Usage, and then click Collect and Upload
Now.
b. When the collection and uploading is complete, click Close.
c. Right-click Data Collection, point to Reports, point to Management Data
Warehouse, and then click Disk Usage Summary.
d. Examine the report, and note that the trend for both the database and log disk
usage for the AdventureWorks database is flat (because the size has not changed
significantly since data collection started).
e. Click AdventureWorks, and then examine the graphical view of disk usage.
f. Close the Disk Usage report window.
Note: In a live environment, you would need to wait for several hours for the Data
Collector to collect meaningful data. For the purposes of this exercise, you will shrink
the AdventureWorks database so that a trend can be seen in the Disk Usage report.
g. In Object Explorer, expand Databases, and then right-click the AdventureWorks
database, point to Tasks, point to Shrink, and click Database.
h. Select Reorganize files before releasing unused space, and click OK.
i. In Object Explorer, right-click Disk Usage, and then click Collect and Upload
Now.
j. When the collection and uploading is complete, click Close.
k. Right-click Data Collection, point to Reports, point to Management Data
Warehouse, and then click Disk Usage Summary.
l. Examine the report, and note the trend for the database disk usage for the
AdventureWorks database is shows a downward trend.
m. Click the trend line, and examine the graphical view of disk usage trend.
n. Close the Disk Usage report window.
o. Keep SQL Server Management Studio open for the next exercise.