Skip to main content

The Working of Microsoft SQL Server CDC

 Modern-day businesses have to preserve historical data and take measures to prevent data breaches. In this regard, Microsoft took the lead in 2005 when it launched the SQL Server CDC.

The 2005 version of SQL Server CDC had certain flaws which were ironed out in an updated release in 2008. Some of the functionalities included tracking and capturing all changes that take place in the SQL Server database tables without taking the help of additional programs and applications. Till 2016, SQL Server CDC was offered by Microsoft in its high-end Enterprise editions but later was available in the Standard version too.

SQL Server CDC captures and records all activities like Insert, Update, and Delete applied to a SQL Server. Column information and metadata required for posting changes to the target database are recorded in modified rows that are then stored in change tables representing the architecture of the columns in the tracked source tables. SQL Server CDC also tracks all changes recorded in the mirrored tables. 



There are additional columns tracking changes at the source tables and the data present is in the following format.

·        __$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

SQL Server CDC is an excellent method to preserve historical data. 

Comments