Microsoft SQL Server and the Change Data Capture Feature

In the modern data-driven environment, enterprises face unique issues, in that they have to focus on data security and safety, ramp up systems to preserve and protect historical data, and prevent data breaches and hacking. Towards this end, some of the leading database platforms like Oracle and Microsoft SQL Server have provided various solutions like timestamps, data audits, complex queries, and triggers. In this respect, Microsoft led the revolution when in 2005 it launched the SQL Server CDC with the “after date”, “after delete”, and “after insert” features.

The 2005 version, though, of SQL Server CDC had certain flaws. These were rectified and Microsoft launched an updated version in 2008 that was very effective and had highly optimized functionalities. SQL Server CDC works by tracking and capturing all changes that take place on the SQL Server database tables without the use of any other additional programs or applications. Microsoft offered the CDC feature till 2016 in its high-end Enterprise edition only but later it came out of the box in all versions.

All activities like insert, update, or delete that is applied to a SQL Server table are captured and recorded by SQL Server CDC. The details of the changes made are present in a user-friendly relational format. Column information and metadata that are required for posting changes to the target database are captured in modified rows. These are stored in change tables that represent the architecture of the columns in the tracked source tables. All changes that are recorded in the mirrored tables with column structures that are the same as the source tables (apart from the Insert, Update, and Delete operations) are also tracked by the SQL Server CDC.

Some of the additional columns that likewise track changes made at the source tables are in the following format.

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

· SQL Server writes one record for every Delete statement showing deleted values.

· SQL Server writes two records for every Update statement showing updated values. One record shows data before the changes are made and the second after changes.

The data that is present in the additional columns are as follows.

· __$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 CDCprimarily creates two jobs – populating database change tables with the changed information and the other cleaning the change tables by deleting records that are older than the configurable retention.