Introduction to Data Collection, Data Collection Terminology

The data collector is a component of SQL Server 2008 that collects different sets of data. Data collection runs continuously or on a user-defined schedule. The data collector stores the collected data in a relational database called the management data warehouse.

The Data Collector is the core component of the SQL Server 2008 data collection platform and is a tool provided by SQL Server. The Data Collector provides a central point for data collection across the database server and application. Unlike SQL Trace, this collection point can obtain data from a variety of sources, not just performance data.

You can use the data collector to adjust the scope of data collection according to your test and production environments. The data collector also uses the data warehouse, which is a relational database that allows you to manage the collected data by setting different retention periods for the data.

The data collector supports dynamic optimization of data collection and is extensible through its API. For more information, see Data Collector Programming.

The following diagram shows how the data collector fits into the overall strategy of SQL Server 2008 data collection and data management.

An instance of the Database Engine in an edition of SQL Server that supports data collection. For more information about supported editions, see the Manageability section of Features Supported by the Editions of SQL Server 2008.

A Target Root defines a subtree in the target hierarchy. A Target Set is the group of targets that results from applying filters to the subtree defined by a Target Root. A Target Root can be a database, SQL Server instance, or computer instance.

The type of target, which has certain characteristics and behaviors. For example, a SQL Server instance target has different characteristics than a SQL Server database target.

A well-known data source specific to a target type that provides data to a collector type.

A logical wrapper for an SSIS package that provides the actual mechanism for collecting and uploading data to the management data warehouse.

An instance of a collector type. A collection item is created from a specific set of input properties and at a specific collection frequency.

A set of collection items. A collection set is a unit of data collection that a user can interact with through a user interface.

The way data is collected and stored. Collection mode can be cached or non-cached. Cached mode supports continuous collection, while non-cached mode is suitable for on-demand collection or collection snapshots.

A relational database used to store the collected data.

The following diagram illustrates the dependencies between the data collector components.

As shown in the figure, a data provider is not part of a data collector and, by definition, has an implicit relationship with a target. A data provider is specific to a particular target (for example, a SQL Server service such as the relational engine) and provides data that can be consumed by a data collector, such as system views in SQL Server, performance monitor counters, and WMI providers.

Collector types are specific to target types, based on the logical association between a data provider and a target type. Collector types define how data is collected from a specific data provider (by using schematized parameters) and specify the data storage schema. To store the collected data, a data provider schema and a storage schema are required. Collector types also provide the location of the management data warehouse, which can reside on the computer running the data collection or on another computer.

As shown in the figure, a collection item is an instance of a specific collector type that is parameterized with input parameters, such as the XML schema of the collector type. All collection items must operate on the same target root, or on an empty target root. This will cause the data collector to combine collector types from the operating system or a specific target root, but not combine collector types from different target roots.

A collection item has a defined collection frequency, which determines how often a snapshot of the values ​​is taken. Although a collection item is the building block of a collection set, it cannot exist independently.

Collection sets are defined and deployed on server instances, and each set can be run independently. Each collection set can be applied to targets that match the target type of all collector types included in the collection set. Collection sets are run by a SQL Server Agent job or multiple agent jobs, and data is uploaded to the management data warehouse on a predefined schedule.

All data collected by different instances within a collection set will be uploaded to the management data warehouse on the same schedule. This schedule is defined as a shared SQL Server Agent schedule and can be used by multiple collection sets. Collection sets are turned on or off as a single entity, and collection items cannot be turned on or off individually.

When you create or update a collection set, you can configure the collection mode used to collect and upload data to the management data warehouse. The type of schedule is determined by the type of collection (that is, cached or non-cached). If the collection is cached, data collection and upload are run in separate jobs. Collection runs on a schedule that is started synchronously with SQL Server Agent and at the frequency specified in the collection item. Uploads run on a user-specified schedule.

In the case of a non-cached collection, data collection and upload are run in the same job, but in two steps. The first step is the collection, and the second step is the upload. On-demand collections do not require scheduling.

After a collection set is enabled, data collection can begin on a schedule or on demand. When data collection begins, SQL Server Agent spawns a process for the data collector, which in turn uploads an Integration Services package for the collection set. The collection items that represent the collection type collect data from the appropriate data provider for a specific target. At the end of the collection cycle, this data is uploaded to the management data warehouse. For more information, see Data Collector Architecture and Processing.