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

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

Create MDW Database

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.

This section describes how to create the MDW database.

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.

1) 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

2) 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 must 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.
DrvProg:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Install\Instmdw.sql
3) 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 [ROOT\GBGGDBAS01] FOR LOGIN [ROOT\GBGGDBAS01]
EXEC SP_ADDROLEMENBER @ROLE='mdw_admin', @MEMBERNAME='ROOT\GBGGDBAS01'
EXEC SP_ADDROLEMENBER @ROLE='mdw_Writer', @MEMBERNAME='ROOT\GBGGDBAS01'
EXEC SP_ADDROLEMENBER @ROLE='mdw_reader', @MEMBERNAME='ROOT\GBGGDBAS01'

The following authorities need to be set up in the MDW database for the DBA Non-Admin group.
CREATE USER [ROOT\GBGGDBAN01] FOR LOGIN [ROOT\GBGGDBAN01]
EXEC SP_ADDROLEMENBER @ROLE='mdw_reader', @MEMBERNAME='ROOT\GBGGDBAN01'

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[ROOT\Servgb_SQLDB_0001] FOR LOGIN [ROOT\Servgb_SQLDB_0001]
EXEC SP_ADDROLEMENBER @ROLE='mdw_writer', @MEMBERNAME='ROOT\Servgb_SQLDB_0001'
EXEC SP_ADDROLEMENBER @ROLE='mdw_reader', @MEMBERNAME='ROOT\Servgb_SQLDB_0001'
GRANT ADMINISTER BULK OPERATIONS TO 'ROOT\Servgb_SQLDB_0001'

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]


4) 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 [ROOT\GBGGDBAS01] FOR LOGIN [ROOT\GBGGDBAS01]
EXEC SP_ADDROLEMEMBER @ROLENAME='dc_report_reader', @MEMBERNAME=' ROOT\GBGGDBAS01'

The following authorities need to be set up in the MSDB database for the DBA Non-Sysadmin group.
CREATE USER [ROOT\GBGGDBAS01] FOR LOGIN [ROOT\GBGGDBAN01]
EXEC SP_ADDROLEMEMBER @ROLENAME='dc_report_reader', @MEMBERNAME=' ROOT\GBGGDBAN01'

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.

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) Select Create or upgrade a management data warehouse, then click Next to continue.
SelectTask.png
4) The Configure Management Data Warehouse Storage window is displayed. Click New to create the MDW database.
Storage.png
5) 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.
Database Name ManagementDW

DBName.png
6) The map logins and Users window is displayed. Three logins must be mapped to roles in the MDW.
MapLogins.png
7) Locate the DBA Sysadmin group and grant use of the mdw_admin role. This will automatically enable the remaining roles.
DBALogin.png
8) Locate the DBA Non-sysadmin group and grant use of the mdw_reader role.
NonsaLogin.png
9) Locate the SQL Server service account and grant use of the mdw_reader and mdw_writer roles. Click Next to continue.
SQLLogin.png
10) The Complete the Wizard window is displayed. Click Finish to continue.
Complete.png
11) The Configure Data Collection Wizard progress window is displayed. Click Close to end.
Close.png
Copyright © 2014 Edward Vassie. License and Acknowledgements
Previous Configure Management Data Warehouse Top Configure MDW Data Collectors Next

Last edited Jun 25, 2014 at 11:28 AM by EdVassie, version 3