Data Collector in SQL Server 2008
Posted by decipherinfosys on January 18, 2008
This is a wonderful new feature in SQL Server 2008. Data Collector in SQL Server 2008 is a component that is installed in a SQL instance and which can either be configured to run on your defined schedule or can run all the time and collect different sets of data pertaining to performance diagnostics, historical data for baseline comparisons, policy based data etc.. It can then be used to store this data into a relational database that is called the management data warehouse. So, how is it different than just using SQL Trace and perfmon? It actually provides a central point for data collection across servers and across applications and allows you to control what gets collected, allows you to report over it, allows you to create a data warehouse of this data for baseline and trend analysis, it allows you to extend it further using an API.
Let’s first see where it appears in SSMS and then how do we configure it. After that, in subsequent posts, we will cover the different terminologies and different usage bits for this wonderful feature. In the image below, you can see that once you connect to the instance, you will see “Data Collection” icon under the Management tree:
Since this is the first time we doing it, we will need to configure the management data warehouse. So, right click and select “Configure Management Data Warehouse”. It will bring up the wizard and on the second image below you will see that you will need to mention the instance, the new database (we are calling it CMDB) and then a cache directory location.
The third image below shows the place where you will need to map an existing login (you can create a new one from this screen) to the roles within the database. You can see the descriptions of those roles in the image below.
Once that is done, the wizard will do the configuration and also provide you with the output of the work done. In subsequent posts, we will start looking at the data collection sets, reporting and other features of Data Collector.
2 Responses to “Data Collector in SQL Server 2008”
Sorry, the comment form is closed at this time.