SQL Server 2008 provides a data collector that you can use to obtain and save data that is gathered from several sources. The data collector enables you to use data collection containers, which enable you to determine the scope and frequency of data collection on a SQL Server server system.
The data collector is a component installed on a SQL Server server, running all the time or on a user-defined schedule, and collecting different sets of data. The data collector then stores the collected data in a relational database known as the management data warehouse.
A small Video, How to configure SQL Server 2008 Data Capture
Demonstration: Configuring Data Collector
The data collector is a core component of the data collection platform for SQL Server 2008 and the tools that are provided by SQL Server. The data collector provides one central point for data collection across your database servers and applications. This collection point can obtain data from a variety of sources and is not limited solely to performance data, unlike SQL Trace.
The data collector enables you to adjust the scope of data collection to suit your test and production environments. The data collector also uses a data warehouse, a relational database that enables you to manage the data you collect by setting different retention periods for your data.
Data Collector Concepts
Before you configure, use, or extend the data collector, you should understand the terminology that describes this feature.
- target: An instance of the Database Engine, or one or more database objects managed by the Policy-Based Management.
- target type: The type of target, which has certain characteristics and behavior. For example, a SQL Server instance target has different characteristics than a SQL Server database target.
- data provider: A known data source, specific to a target type, that provides data to a collector type.
- collector type: A logical wrapper around the SSIS packages that provide the actual mechanism for collecting data and uploading it to the management data warehouse.
- collection item: An instance of a collector type. A collection item is created with a specific set of input properties and collection frequency.
- collection set: A group of collection items. A collection set is a unit of data collection that a user can interact with through the user interface.
- collection mode: The manner in which the data is collected and stored. Collection mode can be cached or non-cached. Cached mode supports continuous collection, whereas non-cached mode is intended for ad hoc collection or a collection snapshot.
- management data warehouse: A relational database used to store collected data.Configuring Data CollectorThe data collector component is installed during SQL Server 2008 setup along with the security roles and tools that you need to configure and use the data collector.
Before you can use the data collector, you must also complete several tasks:
Create Logins and Map Them to Data Collector Roles
The data collector has specific roles for data collection and management data warehouse tasks.
- Data collection. These logins and roles are created on the server that is doing the data collection.
- Management data warehouse. These logins and roles are created on the server that hosts the management data warehouse. Use the Configure Management Data Warehouse wizard to create these logins and map them to specific roles.
Configure the Management Data Warehouse
Use the Configure Management Data Warehouse wizard to configure storage for collected data. The wizard provides an easy way to:
- Create the management data warehouse. You can install this data warehouse on the same instance of SQL Server that runs the data collector. However, if server resources or performance are an issue on the server that is being monitored, you can install the management data warehouse on a different computer.
- Install the predefined system data collection sets.
- Map logins to management data warehouse roles.
- Enable the data collection.
- Start the system collection sets.
If you intend to use proxies for data collection and upload create them before starting data collection. Proxies can be configured for a collection set using the General tab of the Collection Set Properties page.
You can use SQL Server Management Studio for data collection tasks such as enabling the data collector, starting a collection set, viewing logs, and viewing the custom reports that are provided.
You can also use the stored procedures that are provided for any data collector task that does not require SQL Server Management Studio, such as viewing custom reports.
Share your comment, queries and experience to know more about this…..