Microsoft SQL Server and the Change Data Capture Capabilities

 

In 2005, Microsoft was the first database platform to introduce Change Data Capture with “after date”, “after delete”, and “after insert” features. However, it had certain flaws and it was only in 2008 that an updated CDC was launched. It was very effective with several optimized functionalities. Till 2016, SQL Server Change Data Capture was available only in the high-end Enterprise Edition after which it was offered as standard for all versions.

SQL Server Change Data Capture tracks and captures changes that occur in the SQL Server database tables without any additional applications or programs. After all insert, update, and delete activities applied to SQL Server tables are captured and recorded by SQL Server Change Data Capture, the details are available in a user-friendly relational format. Metadata and column information required for entering changes to the target database are captured in modified rows and stored in changed tables that replicate the structure of the columns in tracked source tables.

The data that is present after SQL Server Change Data Capture in additional columns are in the following structure.

·        __$start_lsn and __$end_lsn that show the commit log sequence number (LSN) assigned by the SQL Server Engine to the recorded change

·        __$seqval that shows the order of that change related to other changes in the same transaction, __$operation that shows the operation type of the change, where 1 = delete, 2 = insert, 3 = update (before change), and 4 = update (after change)

·        __$update_mask that is a bitmask defined for each captured column, identifying updating columns

These are some features of the SQL Server Change Data Capture.