Microsoft SQL Server 2008 R2 Unleashed- P150:SQL Server 2005 provided a number of significant new features and enhancements over what was available in SQL Server 2000. This is not too surprising considering there was a five-year gap between these major SQL Server 2008 is not as much of a quantum leap forward from SQL Server 2005 | 1434 CHAPTER 39 Monitoring SQL Server Performance The Data Collector has specific roles for data collection and management data warehouse tasks. The logins and roles required for data collection need to be created on the server that performs the data collection. Logins and roles for the MDW need to be created in the server that hosts the MDW. These logins and the MDW are created using the Configure Management Data Warehouse Wizard which performs the following tasks Creates the management data warehouse Installs the predefined System Data collection sets Maps logins to management data warehouse roles Enables data collection Starts the System Data collection sets To invoke the Configure Management Data Warehouse Wizard perform the following tasks on the SQL Server instance where you want to host the MDW 1. Ensure that SQL Server Agent is running for information on starting SQL Server Agent see Chapter 16 SQL Server Scheduling and Notification . 2. In Object Explorer in SSMS expand the server instance that will host the MDW and expand the Management node for that server. 3. Right-click Data Collection and then click Configure Management Data Warehouse. This starts the Configure Management Data Warehouse Wizard. 4. Click on Next to display the Select Configuration Task window as shown in Figure . FIGURE The Configure Management Data Warehouse Wizard s Select Configuration Task window. Performance Monitoring Tools 1435 TIP If you ve already created a repository database for the SQL Server Utility see the SQL Server Utility section later in this chapter you must use this same database as the MDW for the Data Collector. You can skip the process of creating the MDW and jump right to the configuration of the Data Collector. On the Configure Management Data Warehouse Storage screen look ahead to Figure you specify the name of the server that was set up as the utility control point UCP and specify the name of the utility data warehouse database that was set up .
