SQL Server 2008 - Change Data Capture: new feature
Let us talk briefly about the new feature that is introduced within SQL Server 2008 aka Katmai.
Change Data Capture (CDC) is (similar to triggers: INSERTED & DELETED tables) the 'new boy in SQL Server town' that records the DML activities applied to the database objects with an easy relational format. Further the column information that mirrors the column structure of a tracked source table is captured for the modified rows, along with the metadata needed to apply the changes to a target environment. Creating a capture instance also creates a change table that corresponds to the source table. You can specify up to two capture instances for a source table.
The process is each insert or delete operation that is applied to a source table appears as a single row within the change table. The data columns of the row resulting from an insert operations contain the column values after the insert; the data columns of the row resulting from a delete operation contain the column values prior to the delete. An update operation, however, requires two row entries: one to identify the column values before the update and a second to identify the column values after the update.
The database must be enabled to use CDC, in such a way schema, user, metadata and other system objects are created for that enabled database, this contains the change data capture metadata tables and, after source tables are enabled for change data capture, the individual change tables that serve as a repository for change data. Associated system functions used to query change data are also contained in this schema.
Katmail BOL documented more topics for CDC and for a reference:
A good example of a data consumer targeted by this technology is an extraction, transformation, and loading (ETL) application that incrementally loads change data from SQL Server source tables to a data warehouse or data mart. Although the representation of the source tables within the data warehouse must reflect changes in those tables, an end-to-end technology that refreshes a replica of the source is not appropriate. What is needed is a reliable stream of change data, structured so that consumers can apply it to dissimilar target representations of the data. SQL Server change data capture provides this technology.
When the first table in the database is enabled, a capture process automatically gathers change data from the transaction log and inserts the change information in the associated change table. Additional metadata about each transaction is inserted in a metadata table that allows the captured changes to be placed on a conventional timeline.
Only member of SYSADMIN server role will be able to enable the CDC for a database, then only DBO for that database can enable down to the table level for change actiivity to be captured. Here Log Sequence Numbers (LSN) plays important role as the mapping table allows a conventional interval expressed as date-time values to be expressed as a comparable LSN range.
As per the BOL: At the moment this CDC feature is available only in SQL Server "Katmai" Enterprise, Developer, and Evaluation editions.
LiveMeeting show on CDC.