By Ronald Barrett
The dynamic nature of databases, and in particular data warehouses, calls for a method of updating, inserting and deleting activity in a SQL server table. But change data capture is not only about the physical change of the database, it is about the ability to report on and track these changes. Change Data Capture is part of a suite of tools used in SQL Server 2008 to accomplish that goal.
The Microsoft SQL Server TechNet Virtual Lab: Change Data Capture (CDC) takes the user through the process of simulating an Integration Services package. The lab manual explains the scenario of the CDC exercise and lets you know before you begin what each element is accomplishing during the lab.
The virtual lab begins by launching the Change Data Capture file via Windows explorer. The files resides in C:\SQL Server 2008 Demo Files\Intelligent Platform\Change Data Capture\CDCSample2.sln
TIP: Although this tool is used to track changes in SQL Server, this exercise takes place with Microsoft Visual Studio. It is important to make sure you completely read the scenario to understand what is happening in the exercise.
Once you double click the file, Microsoft Visual Studio opens up in the solutions explorer. You want to be sure to choose "SetupCDCSample.dtsx".
Once you choose the debug file, the system will being the debug process. It takes about 30- 40 seconds for the first Data Viewer to appear.
Finally you click the detach button and the process will run through some changes. When the next data viewer appears, you can see the changes made to the Customer ID 696.
Overall this was a quick and interesting lab. Although it took only 30 minutes (between reading the manual and running the tests twice), the Change Data Capture lab certainly gave a good illustration of what a live scenario could look like and effectively showed how the CDC process works. I particularly enjoyed working within Visual Studio on this lab, which I don’t get to do very often. Bottom-line, this is a great way to start learning about Change Data Capture.