Configure the management data warehouse (SQL Server Management Studio)
Applies to: SQL Server
This article describes how to configure the management data warehouse to support data storage on a single instance, or multiple instances, of SQL Server that use the data collector. These instances can be on the same server or on different servers. This article also provides descriptions of the user interface for the Configure Data Management Warehouse Wizard dialog box. For information about configuring a data collector, see Configure properties of a data collector.
If SQL Server Agent is configured to run using one of the System service accounts (Local System, Network Service, or Local Service), and the management data warehouse is created on a different instance from the data collector, you must configure collection sets to use a proxy for uploading data to the management data warehouse.
Configure the management data warehouse on a single instance or multiple instances of SQL Server
Ensure that SQL Server Agent is running.
In Object Explorer, expand the Management node.
Right-click Data Collection, expand Tasks, and then select Configure Management Data Warehouse.
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.
It's 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.
Open SQL Server Management Studio on another instance and do either of the following actions:
Use the Configure Management Data Warehouse wizard to configure data collection for the existing management data warehouse.
Right-click Data Collection, and then select Properties. On the General tab, specify the existing management data warehouse and the server it's installed on.
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
The Welcome page is the starting page of the Configure Data Collection Wizard. Displaying this page is optional.
Don't show this starting page again.
Select to suppress this page the next time you launch the Configure Data Collection Wizard.
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.
You must have the appropriate level of permissions in order to create the management data warehouse on the server. For more information, see CREATE DATABASE. You also must have the appropriate level of permissions to create logins for management data warehouse roles.
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 can't be modified.
Specifies the relational database that will store collected data. Use the list to select an existing database or select New to create a new database using the New Database dialog.
The New 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 selecting the checkbox by one or more of the following options:
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 be applied, and which actions take place, when you select Finish.
Configure Data Collection Wizard Progress page
Use this page to view the results of each configuration step.
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's an error, a message appears in the Message column.
Stop wizard processing.
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 the wizard.