The data collector uses the msdb database for storing configuration information, run-time information, auditing, and collection history information. This database must be present on the instance of SQL Server that runs the data collector. Using msdb provides the following benefits:

• All the data needed to configure and run the data collector is in one location.
  
• Data collection configuration can be deployed across several servers without having to use the file system.
  
• The data collector can use existing SQL Server security mechanisms to protect the data. In addition, database roles can provide granular security, and there is no need to implement cross-database chaining.
  
• Since msdb is a relational database, it is possible to ensure the referential integrity of the configuration and run-time data.
  

In addition to storing collector-specific information, msdb is also used to store SQL Server Agent job information and SSIS package information.

Data Collection Configuration

Data collection configuration-related tables and views include information about collection sets, collector types, and collection items. The tables and views also contain global data collector parameters, such as the location of the management data warehouse. Database roles for the data collector and management data warehouse are also stored here. For more information about the columns for each data collection configuration view, see Data Collector Views (Transact-SQL).

SQL Server Agent Jobs

SQL Server Agent job information is stored in msdb using a separate schema. This job information is created or updated whenever collection sets are added, removed, or changed. When a collection set is started, SQL Server Agent uses this information to execute data collector jobs.

SQL Server Integration Services Packages

SSIS packages are stored in msdb using existing SSIS storage tables. The data collector uses these packages to collect data and upload it to the management data warehouse.

Auditing and History

Dedicated storage is provided for saving detailed audit and historical records of data collection runs. The data collector run-time component and the SSIS packages generate events during data collection. This information is used to monitor and troubleshoot the collection process.

The management data warehouse is a relational database that contains all the data that is retained. This database can be on the same system as the data collector, or it can be on another computer.

SQL Server Agent hosts the data collector run-time component. Data collection is scheduled as a SQL Server Agent job. When the job is run, it invokes the data collector run-time component using the parameters required to create and execute a collection set.

The data collector run-time component is hosted by a stand-alone process called Dcexec.exe. This component manages data collection based on the definitions provided in a collection set, and can accept any collection set as input. The data collector run-time component is responsible for loading and executing the SSIS packages that are part of a collection set.

A collection set is run in one of the following collection and upload modes:

• Non-cached mode. Data collection and upload are on the same schedule. The packages start, collect and upload data at their configured frequency, and run until they are finished. After the packages finish, they are unloaded from memory.
  
• Cached mode. Data collection and upload are on different schedules. The packages collect and cache data until they receive a signal to exit from a loop control-flow task. This ensures that the data flow can be executed repeatedly, which enables continuous data collection.
  

The data collector run-time component invokes the SSIS packages that the data collector uses. The packages are run in-process.

The two most important tasks for the SSIS packages are data collection and data upload, which are carried out by separate packages.

• The collection package gathers data from a data provider and keeps it in temporary storage. If configured to do so, this package also appends additional information to the data it collects, such as the snapshot time or information about the source of the data.
  
• The upload package reads the data in temporary storage, processes the data as required (for example, removing unnecessary data points, normalizing the data, and data aggregation), and then uploads the data to the management data warehouse. The upload is done as a bulk insert to minimize the impact on server performance.
  

Separating data collection and data upload provides more flexibility and efficiency. This design supports scenarios where snapshots of the data are captured at frequent intervals (for example, every 15 seconds), but the collected data only needs to be uploaded every hour. Data collection and upload frequency should be determined by the monitoring requirements of a particular SQL Server installation.

Temporary storage is used during data collection to store property values exposed by collection sets or calculated dynamically. These values can be raw data obtained during the collection process, or processed data, such as aggregated data.

Example 1

This example collection set, "Base Performance Data," contains separate collection and upload packages to support different schedules for the collection and upload packages.

Collection Set: Name = "Base Performance Data"

Collection Item: Name = "SQL_RE_Active_Requests_History"

Collection package: ARH_Collect.dtsx

Upload package: ARH_Upload.dtsx

Collection Frequency: 1 second

Collection Item: Name = "SQL_RE_Wait_Statistics"

Collection package: WS_Collect.dtsx

Upload Package: WS_Upload.dtsx

Collection Frequency: 15 seconds

Collection Item: Name = "SQL_RE_Performance_Counters"

Collection package: PC_Collect.dtsx

Upload package: PC_Upload.dtsx

Input: Counter – Locks: Current Latch Waits

Collection Frequency: 15 seconds

Collection Item: Name = "WIN_OS_Performance_Counters"

Collection package: OS_PC_Collect.dtsx

Upload package: OS_PC_Upload.dts

Input: Counter – Process: % Processor Time

Collection Frequency: 15 seconds

Upload Schedule: Every 30 minutes

Example 2

This example collection set, "Server Configuration Data," contains a single package that gets Transact-SQLbased snapshots and uploads them to the management data warehouse as they are obtained.

Collection Set: Name = "Server Configuration Data"

Collection Item: Name = "SQL_RE_TSQL"

Collection and upload package: TSQL_Upload.dtsx

Input: Transact-SQL query for database sizes

Collection Frequency: <none>

Collection Item: Name = "SQL_RE_TSQL"

Collection and upload package: TSQL_Upload.dtsx

Input: Transact-SQL query for configuration options

Collection Frequency: <none>

Upload Schedule: Every 24 hours

When the collection sets shown in the preceding examples are created, SQL Server Agent jobs are created to perform the data collection. The following rules govern job creation:

• Data collection by different collection sets are executed as separate jobs.
  
• Collection sets that use cached collection mode are scheduled as jobs that start when the SQL Server Agent starts. These jobs run continuously and are controlled by the data collector run-time component.
  
• Collection sets that use non-cached collection mode are scheduled as normal SQL Server Agent jobs. These jobs use a SQL Server Agent schedule that matches the schedule defined in the collection set. The actual duration of execution can be customized by the user.
  

The following examples illustrate jobs that use the preceding rules. These jobs can run the collection set examples provided.

Jobs for Collection Set Example 1

Two jobs are used to handle this collection set: one for the collection and one for the upload.

Job 1,Name: "Base Performance Data Collection"

Category: "Data Collector"

Job Step 1:

Collection Set: Name = "Base Performance Data"

Collection Mode: Collection Only, Continuous Run

Packages run: ARH_Collect, WS_Collect, PC_Collect, OS_PC_Collect

Schedule: At Agent start-up

Job 2,Name: "Base Performance Data Upload"

Category: "Data Collector"

Job Step 1:

Collection Set: Name = "Base Performance Data"

Collection Mode: Upload Only, Single Run

Packages run: ARH_Upload, WS_Upload, PC_Upload, OS_PC_Upload

Schedule: Every 30 minutes, Starting at 12:00 AM PST

Job for Collection Set Example 2

One job is created to handle the collection set.

Job 3,Name = "Server Configuration Data"

Category = "Data Collector"

Job Step 1:

Collection Set, Name = "Server Configuration Data"

Collection Mode: Collection and Upload, Single Run

Packages run: TSQL_Upload, TSQL_Upload

Schedule: Every Day at 12:00 AM PST

Because a data collector and a collection set are the elements of data collection that you interact with, you should understand their valid states and the concept of a collection set lifetime.

Valid States

To turn data collection on and off, you can enable or disable data collection. If you want to make any change that affects collector-wide parameters, such as changes to the management data warehouse connection string, you must first disable data collection.

If you want to make any collection set configuration changes, you must first stop the collection set.

If you disable data collection when collection sets are running, these collections sets enter a paused state until you re-enable data collection. At that point, the collection sets resume execution. Any collection set that was not running when you disabled data collection will remain stopped. You can start any collection set while data collection is disabled. However, the collection set will not start execution until you enable data collection.

Collection Set Lifetime

After a collection set starts, it can undergo several transitions in its lifetime. These possible transitions, and the actions that occur during them, include the following:

1. Construct
   
   • A collection set object is created in memory.
     
   • All properties can be freely set and modified.
     
2. Add collector type
   
   • A new collection item is added to the collection set.
     
   • The collection set verifies that all collection items reference the same target root.
     
3. Remove collector type
   
   • An existing collection item is removed from the collection set.
     
4. Create
   
   • The collection set verifies that it contains at least one collection item.
     
   • Information about all collection items and their properties, collection frequencies, and all the properties of the collection set is inserted into data collector configuration storage.
     
   • The initial state of the collection set is set to inactive.
     
   • The SQL Server Agent job or jobs that will run the collection set are created and linked to the collection set.
     
   • The initial state of the SQL Server Agent jobs is set to inactive.
     
5. Start
   
   • The state of the collection set changes to running.
     
   • Associated SQL Server Agent jobs are enabled.
     
   • If a job is flagged to run on Agent startup, the job starts immediately.
     
6. Stop
   
   • The state of the collection set changes to stopped.
     
   • All running jobs for the collection set are stopped and disabled.
     
   • All the jobs associated with the collection set are set to inactive.
     
7. Delete
   
   • All the jobs associated with the collection set are deleted from memory.
     
   • All the objects (collection items, collection set) in data collector configuration that are associated with the collection set are deleted.