Maintenance and Monitoring - Microsoft SQL Server 2008 R2 Administration - page 315

In this recipe, we will implement the management data warehouse features on existing SQL
Server 2008 R2 instance using SQL Server Management Studio tool. Before getting started
with the recipe, it is essential to highlight the important components within Data Collector to
store the management data, in conjunction to with the MSDB system database. They are:
F Data collection sets: They are definitions that are stored in the MSDB system
database and use scheduled jobs for collecting performance data
F Data Collector runtime component: This is a standalone process that is used for
loading and executing SSIS packages that are part of the collection set
F Management data warehouse database: This is a relational database where the
collected data is stored having the pre-installed views and stored procedures for
collection management
F SSIS packages: They are used to collect and update the data to the data warehouse
database
F MDW Reports: These are pre-defined RDL files that are used to produce reports from
SSMS for viewing the collected performance data and system information
Getting ready
The performance data collector feature is available in all server and specialized editions of
SQL Server 2008 and 2008 R2 except SQL Server Express. For testing and development
purposes, the Developer and Evaluation Enterprise editions can be used.
In order to use the data collector, we must ensure that the following tasks are completed:
F The SQL Server instance to host data collector data warehouse must be the 2008
version or higher
F Ensure that the SQL Server Agent is started and running with no errors reported
F Create and configure the management data warehouse
F Create the data collector login name MDW_Collector from SSMS Object Explorer
to map them to data collector roles: dc_admin , dc_operator , and dc_proxy
These data collector roles are stored in the msdb system database, and no user
is a member of these roles except members of sysadmin . Fixed server role will
have full access to SQL Server Agent objects and data collector views.
F Enable the data collection
F The data collector specific roles are required for the collection and storage of data for
a single instance to a data warehouse