Create MDW Database

Previous Configure Management Data Warehouse	Manual Configuration	Configure MDW Data Collectors Next

FineBuild can create the Management Data Warehouse database.

The MDW database hosts the performance-related information collected by the MDW data collectors.

If a centalised MDW is used, the Create MDW Database process should only be performed on the central Mangement Server. If local MDW databases are used, the Create MDW Database process must be performed on all servers where MDW statistics collection is required.

FineBuild Create MDW Database

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.

FineBuild uses the following parameters to help create the MDW Database:

Parameter	Default Value	Description
/GroupDBA:	(none)	DBA Group with sysadmin authority
/GroupDBANonSA:	(none)	DBA Group without sysadmin authority
/ManagementDW:	ManagementDW	Name of Management Data Warehouse Database
/SQLSvcAccount:	(none)	SQL Server Service Account

The MDW database is created with an initial size of 100MB. This may need to be adjusted depending on the volume of data collected and the duration for which it is kept. All of the following processes are executed.

Create the MDW database

The MDW database is created by Process Id 5EDA, which runs the following script. Edit the script to ensure the path names are compatible with the instance on which the database is being installed.
```
SQL…\FineBuild\Build Scripts\DB Create ManagementDW.sql
```
Setup the MDW Schema

The setup of the MDW schema is performed by Process Id 5EDB, which runs a standard script provided with SQL Server. Prior to running the script, a database option Snapshot Isolation should be set.

Using SQL Server Management Studio, run the following statement in a query window:
```
ALTER DATABASE ManagementDW SET READ_COMMITTED_SNAPSHOT ON
```
Set the database context to the MDW database, and then run the following script to setup the MDW schema. The script is found in the folder below. For a named instance, use the instance name in place of the .MSSQLSERVER\ portion of the folder name:

/VolProg:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Install\Instmdw.sql

Setup MDW Authorities

The authorities needed to run the Data Collectoes are set up by Process Id 5EDC. The Data Collectors capture the data that is stored in the MDW

The following authorities need to be set up in the MDW database for the DBA sysadmin group:

CREATE USER [/GroupDBA:] FOR LOGIN [/GroupDBA:]
EXEC SP_ADDROLEMENBER @ROLE='mdw_admin', @MEMBERNAME='/GroupDBA:'
EXEC SP_ADDROLEMENBER @ROLE='mdw_Writer', @MEMBERNAME='/GroupDBA:'
EXEC SP_ADDROLEMENBER @ROLE='mdw_reader', @MEMBERNAME='/GroupDBA:'

The following authorities need to be set up in the MDW database for the DBA Non-Admin group:

CREATE USER [/GroupDBANonSA:] FOR LOGIN [/GroupDBANonSA:]
EXEC SP_ADDROLEMENBER @ROLE='mdw_reader', @MEMBERNAME='/GroupDBANonSA:'

The following authorities need to be set up for the account that runs the MDW Data Collector jobs. In the current version of FineBuild this is always the SQL Server service account:

CREATE USER[/SQLSvcAccount:] FOR LOGIN [/SQLSvcAccount:]
EXEC SP_ADDROLEMENBER @ROLE='mdw_writer', @MEMBERNAME='/SQLSvcAccount:'
EXEC SP_ADDROLEMENBER @ROLE='mdw_reader', @MEMBERNAME='/SQLSvcAccount:'
GRANT ADMINISTER BULK OPERATIONS TO '/SQLSvcAccount:'

The following authorities need to be set up to allow low-priviledge accounts to view the MDW reports:

GRANT VIEW DEFINITION TO [mdw_admin]
GRANT VIEW DEFINITION TO [mdw_reader]

Setup MSDB Authorities

Certain authorities need to be set up in the msdb database, as described in Connect item 558417

The MSDB authorities setup is performed by Process Id 5EDD. The following role needs to be set up in the MSDB database to provide a framework for granting permission to view the MDW reports:

CREATE ROLE [dc_report_reader] AUTHORIZATION [dbo]
GRANT SELECT ON [dbo].[syscollector_collection_sets] TO [dc_report_reader]
GRANT SELECT ON [dbo].[syscollector_execution_log] TO [dc_report_reader]
GRANT SELECT ON [dbo].[syscollector_config_store] TO [dc_report_reader]

The following authorities need to be set up in the MSDB database for the DBA Sysadmin group:

CREATE USER [/GroupDBA:] FOR LOGIN [/GroupDBA:]
EXEC SP_ADDROLEMEMBER @ROLENAME='dc_report_reader', @MEMBERNAME='/GroupDBA:'

The following authorities need to be set up in the MSDB database for the DBA Non-Sysadmin group:

CREATE USER [/GroupDBANonSA:] FOR LOGIN [/GroupDBANonSA:]
EXEC SP_ADDROLEMEMBER @ROLENAME='dc_report_reader', @MEMBERNAME='/GroupDBANonSA:'

The following authorities need to be set up in the MSDB database for the MDW Job Proxy role:

GRANT EXECUTE ON [dbo].[sp_syscollector_sql_text_lookup] TO [dc_proxy]
GRANT EXECUTE ON [dbo].[sp_syscollector_text_query_plan_lookpup] TO [dc_proxy]

Top

Manual Create MDW Database

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

Using SQL Server Management Studio, navigate to Data Collection

Right-click and select Configure Management Data Warehouse
The Welcome screen is displayed

Click Next to continue
Select Create or upgrade a management data warehouse

Click Next to continue
The Configure Management Data Warehouse Storage window is displayed

Click New to create the MDW database
Enter the following information then click OK to create the database

On return to the Configure Management Data Warehouse Storage window, click Next to continue

Item Value

Database Name ManagementDW
The map logins and Users window is displayed

Three logins must be mapped to roles in the MDW:
Locate the DBA Sysadmin group and grant use of the mdw_admin role

This will automatically enable the remaining roles
Locate the DBA Non-sysadmin group and grant use of the mdw_reader role
Locate the SQL Server service account and grant use of the mdw_reader and mdw_writer roles

Click Next to continue
The Complete the Wizard window is displayed

Click Finish to continue
The Configure Data Collection Wizard progress window is displayed

Click Close to end