Configure the Management Data Warehouse (SQL Server Management Studio)

This topic describes how to configure the management data warehouse to support data storage on a single instance or multiple instances of SQL Server that are using the data collector. These instances can be on the same server or on different servers. This topic also provides descriptions of the user interface for the Configure Data Management Warehouse Wizard and the Data Collection Properties dialog box.

Configure the management data warehouse on a single instance or multiple instances of SQL Server

1. Ensure that SQL Server Agent is running.

2. In Object Explorer, expand the Management node.

3. Right-click Data Collection, and then click Configure Management Data Warehouse.

4. Use the Configure Management Data Warehouse Wizard to create a management data warehouse, configure logins, enable data collection, and start the System Data Collection Sets.

   To configure multiple instances, continue with step 5.

   Note

   It is considered best practice to use proxies in deployments where the data collector is installed on multiple instances of SQL Server that are using the same management data warehouse.

5. Open SQL Server Management Studio on another instance and do either of the following:

   • Use the Configure Management Data Warehouse wizard to configure data collection for the existing management data warehouse.

   • Right-click Data Collection, and then click Properties. On the General tab, specify the existing management data warehouse and the server that it is installed on.

6. Repeat step 5 until all the database instances that use the data collector are configured to upload data to the shared management data warehouse.

Configure Management Data Warehouse Wizard

Welcome Page

The Welcome page is the starting page of the Configure Data Collection Wizard. Displaying this page is optional.

• Do not show this starting page again.
  Select to suppress this page the next time you launch the Configure Data Collection Wizard.

Select Configuration Task Page

Use this page to choose to configure a data collection set or to configure a management data warehouse.

• Create or upgrade a management data warehouse
  Configure a management data warehouse database.

  You can specify or create a relational database that will store collected data on the local server.

• Set up data collection
  Configure a data collection set.

  You can specify a relational database that will store collected data on the local server, or on a remote server. In addition, you can specify a cache location where collected data is stored locally before it is uploaded to the management data warehouse.

Configure Management Data Warehouse Storage Page

Use this page to select a SQL Server database server and management data warehouse. The management data warehouse is a relational database that will store collected data.

• Server name
  Specifies the name of the server that will host the management data warehouse.

  When configuring a management data warehouse, Server name is always the name of the local server and cannot be modified.

• …
  Open the Connect to Server dialog box, where you can select a server that will host the management data warehouse.

  This option is available only when configuring a data collection set.

• Database name
  Specifies the relational database that will store collected data. Use the list to select an existing database or click New to create a new database using the New Database dialog.

  The New option is available only when configuring a data collection set

• Cache directory
  Specifies the location where collected data is cached locally before it is uploaded to the management data warehouse. By default, the data collector uses the %Temp% or %Tmp% directory if it is configured on the computer where the data collector is installed.

  This option is available only when configuring a data collection set.

• …
  Open the Find cache directory browser. This is only necessary if the %Temp% or %Tmp% directory is not configured for the host computer.

  This option is available only when configuring a data collection set.

Map Logins and Users Page

Use this page to map logins to database user roles for the management data warehouse.

• Users mapped to this login
  Displays the existing logins on the server that will host the management data warehouse. Each row contains an editable Map check box, a Login name, and a Type of login.

  Specify a login by selecting the Map checkbox for the login.

• Database role membership for: <data warehouse name>
  Select the management data warehouse role that the login is mapped to by clicking the checkbox by one or more of the following options:

  • mdw_admin

  • mdw_reader

  • mdw_writer

• New Login
  Open the Login - New dialog box and create a new login for the management data warehouse.

Complete the Wizard Page

Use this page to verify and complete data collection configuration. The tree displayed in the viewing window shows what configurations will applied as well as what actions will take place when you click Finish.

Configure Data Collection Wizard Progress Page

Use this page to view the results of each configuration step.

• Details
  Displays each configuration step as a row in the Details grid. Each row contains an Action column that describes the step, and a Status column that indicates the success or failure of the step. If there is an error, a message appears in the Message column.

• Stop
  Stop wizard processing.

• Report
  View a report of the data collection configuration. The following report options are provided:

  • View Report

  • Save Report to File

  • Copy Report to Clipboard

  • Send Report as E-mail

• Close
  Close the wizard.

[Top]

Data Collection Properties Dialog Box

Data Collection Properties (General Tab)

Use this page to configure settings for the management data warehouse and specify where collected data should be stored before it is uploaded to the data warehouse.

• Enable Data Collection
  Select to enable data collection. This has the same effect as running the sp_syscollector_enable_collector stored procedure. Clearing this check box disables data collection and has the same effect as running the sp_syscollector_disable_collector stored procedure.

• Server
  Displays the name of the server that will host the management data warehouse

• Database name
  Displays the name of the relational database that is used for the management data warehouse.

• Authentication
  Specify the type of authentication that is used to connect to the management data warehouse.

  Note

  Windows Authentication is the only option for this release of the data collector.

• User
  Inactive for this release of the data collector.

• Password
  Inactive for this release of the data collector.

• Test Connection
  Test the connection to the specified Server using information provided when data collection is configured.

• Cache directory
  Specifies the directory where collected data is stored on the system collecting the data before it is uploaded to the management data warehouse. If Cache directory is not specified, the data collector attempts to locate the %TEMP% and %TMP% environment variables and use one these locations as the default location for temporary storage. If these environment variables are not configured, an error occurs and you are prompted to create a cache directory.

Data Collection Properties (Advanced Tab)

Use this page to configure the retry settings for the connection to the management data warehouse.

• Number of times to retry if upload fails
  Specifies the number of times to retry an upload to the management data warehouse if an upload fails. The default is 1.

[Top]

See Also

Reference

sp_syscollector_enable_collector (Transact-SQL)

sp_syscollector_disable_collector (Transact-SQL)

Concepts

Data Collection

Manage Data Collection