This project has moved. For the latest updates, please go here.

Previous Create MDW Database Manual Configuration Set Data Collector Job Names Next

Configure MDW Data Collectors

FineBuild can configure the Management Data Warehouse data collectors.

The MDW data collectors perform the task of collecting performance-related information on the host server and posting it for storage on the MDW database. Each collector gathers information for a specific aspect of performance.

FineBuild Configure MDW Data Collectors

The configuration of the MDW database within FineBuild is broken down into a number of stages, in order to simplify restart processing if any of them fail.

This section describes how to configure the MDW data collectors. The MDW Data Collector configuration relates to Process Id 5EDE.

1) Disable Collector Process

The collector process must be disabled before the configuration of Data Collectors can be done. Using SQL Server Management Studio, run the following statement in a query window using the msdb database context.
EXEC dbo.sp_syscollector_disable_collector

2) Select the MDW database

The server and SQL instance name that hosts the MDW database must be specified. Using SQL Server Management Studio, run the following statement in a query window. Use the relevant server name in place of server. If the MDW is hosted on the default instance of SQL Server, do not specify the instance name. If the MDW is hosted on a named instance, add the instance name to the server name.
EXEC dbo.sp_syscollector_set_warehouse_instance_name @instance_name='server'

The database name that hosts the MDW database must be specified. Using SQL Server Management Studio, run the following statement in a query window. Use the relevant database name in place of database.
EXEC dbo.sp_syscollector_set_warehouse_database_name @database_name='database'

3) Start Data Collectors

The data collectors can now be started. Three data collectors are included in the standard SQL Server installation, and all these will be started. Using SQL Server Management Studio, run the following statements in a query window. Running these commands will also cause the required jobs to be created that run the collection process.
EXEC dbo.sp_syscollector_start_collection_set @collection_set_id=1
EXEC dbo.sp_syscollector_start_collection_set @collection_set_id=2
EXEC dbo.sp_syscollector_start_collection_set @collection_set_id=3

4) Enable Collection Process

All configuration is complete and the collector process can be enabled. Using SQL Server Management Studio, run the following statement in a query window:
EXEC dbo.sp_syscollector_enable_collector

Top

Manual Configure MDW Data Collectors

The following steps show what you would have to do for manual Configure MDW Data Collectors. FineBuild does all of this work for you automatically.

Following completion of configuring the MDW, the Data Collectors can now be configured.

1) Using SQL Server Management Studio, navigate to Data Collection. Right-click and select Configure Management Data Warehouse.
SSMS.png
2) The Welcome screen is displayed. Click Next to continue.
Welcome.png
3) The Select configuration task window is displayed. Select Set up data collection, and then click Next to continue.
SelectTask.png
4) The Configure Management Data Warehouse Storage window is displayed. Click on the ellipses on the Server name line to select the server that will host the data that is collected.
Storage.png
5) Ensure the correct server and instance are shown in the Server name box, then click Connect.
Server.png
6) Set the Database name value to the MDW database. Click Next to continue.
DBName.png
7) The Complete the Wizard window is displayed. Click Finish to continue.
Complete.png
8) The Configure Data Collection Wizard progress window is displayed. Click Close to end.
Close.png
Copyright © 2014 Edward Vassie. License and Acknowledgements
Previous Create MDW Database Top Set Data Collector Job Names Next

Last edited Jun 25, 2014 at 11:32 AM by EdVassie, version 2