Microsoft launched Change Data Capture (CDC) in 2005 but it was not until 2008 that the present form was introduced with “after date”, “after delete”, and “after insert” features. All changes made on the SQL Server database tables are tracked and captured by the SQL Server CDC without any additional programming or applications. Till 2016, Microsoft o0provided SQL Server CDC in the Enterprise edition only but was later made standard across all versions.

How does SQL Server CDC work?

All activities like insert, update, and delete that are applied to a SQL Server table are recorded by the SQL Server CDC and provided in an easy relational format. Metadata and column information that is necessary for making changes to the target database are captured and stored in the change tables. These replicate the structure of the columns in the monitored source tables.

All changes apart from the Insert, Update and Delete operations that are tracked by SQL Server CDC are recorded in a mirrored table that has a similar column structure as the source tables.

The following columns in the given format track change at the source database also.

• SQL Server writes one record showing the inserted values for every Insert statement

• SQL Server writes one record showing the deleted values for every Delete statement

• SQL Server writes two records for every Update statement. One shows data before the changes and the second after the changes are made.

SQL Server CDC primarily creates two jobs. The first one populates database change tables with the changed information. The second clean change tables by deleting records older than the configurable retention.

This is how the CDC feature in Microsoft SQL Server functions.