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

This completes the set up of System Data Collections Sets to store the performance
information in the 1445_MDW database, which in turn completes the steps to implement
management data warehouse (MDW) features.
As best practice, the definitions of system collection sets can be
scripted, for instance to script a Server Activity collection set using
Object Explorer and right-click on Server Activity choose Script
Data Collection as and select CREATE To | New Query window.
How it works...
The data collector configuration and components are stored in the MSDB system database
and data collection information is stored on the user defined management data warehouse
database. The storage components are the database where the configuration and collected
data is stored, and execution components are used for data collection and storage. The
required schemas and their objects for pre-defined system collection sets are created when
we create management data warehouse; all the schemas that are created are classified as
core and snapshots. When any user-defined data collection sets are created, a custom_
snapshots that include collection items that use the generic TSQL query collector type.
Internally, API components are used to enable interaction between the user interface and
data collector.
When the MDW is configured, the dcexec.exe file is used by the data collector runtime
component process. The data collection management is based on definitions provided in a
collection set and the runtime component is responsible for loading and executing the SSIS
package that are part of this collection set. There are two modes used by a collection set:
non-cached mode and cached mode.
F Non-cached mode: It uses the same schedule for data collection and upload. The SSIS
packages perform collection and upload of data as per the configured frequency; once
the package execution is completed, they are unloaded from memory.
F Cached mode: It uses different schedules for data collection and upload. The
package execution will collect and cache data until an exit from a loop control-
flow task in the packages. This will perform the data flow repeatedly to enable the
continuous data collection.
The important tasks for the SSIS packages are data collection and data upload, which
are performed as a separate package execution. The data collector runtime component
can only run data collection or data upload as it is not possible to run both of these
tasks simultaneously.
The collection package gathers data and stores it in Cache Directory or in a temporary
storage folder. The snapshot time and source of the data is appended by the SSIS package to
the collected data.