MSSQL Change Data Capture

Newly introduced in SQL Server 2008. CDC can track any data from the Transaction-Log, including any DML INSERT, UPDATE, DELETE, and MERGE command, and DDL CREATE, ALTER, and DROP and makes a record available of what changed, where, and when, in simple relational ‘change tables’. These change tables contain columns that reflect the column structure of the source table you have chosen to track, along with metadata needed to understand changes that have been made. CDC only tracks changes in user-created tables. When you apply Change Data Capture features on a database table, a mirror of the tracked table is created with the same column structure of the original table, but with additional columns that include the metadata used to summarize the nature of change in the database table row. Change Data Capture is enabled at the database level first, and then for every table that needs to be tracked. Because change data capture reads from the transaction log, one might think that CDC requires database to be set to full recovery model so that the transaction log is kept.

Leave a Reply

Your email address will not be published. Required fields are marked *