In the modern data driven environment, the most critical task for any organization is to keep data safe and secure. This is necessary to protect and preserve historical data. With this in view, the leading database solution providers like Oracle database and Microsoft SQL Server have from time-to-time launched various features that achieve stringent database security. These include complex queries, timestamps, data audits, and triggers.

The first off the mark in 2005 was SQL Server. It launched the “after date”, “after delete”, and “after insert” features to enable SQL Server Change Data Capture (CDC). But this system had its flaws and it was not until 2008 that the issue was solved after SQL Server introduced a modified version of Change Data Capture. It proved to be very effective. Without any additional programming, SQL Server Change Data Capturemonitors, tracks, and captures all changes made on the SQL Server databases.

Till 2016, SQL Server Change Data Capturewas available only in the Enterprise Edition of SQL Server but later it came built-in with all the versions.

How does the SQL Server Change Data Capturework?

All the Insert, Update, and Delete operations on the table of a database are tracked by the Change Data Capture. These changes are recorded comprehensively in a mirrored table with a similar column structure as the source tables.

Additional columns in the SQL Server record the descriptions of the changes made in detail in the following format.

• For every Insert statement, the SQL Server writes one record showing the inserted values.

• For every Delete statement, the SQL Server writes one record showing the deleted values.

• For every Update statement, the SQL Server writes two records. The first shows the data before the change is made and the second shows the value after the change has been made.

The additional columns have the following data 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 bit mask defined for each captured column, identifying the updating columns

This detailed information helps users to quickly track the SQL Server Change Data Capture changes for carrying out data audits and ensuring that no data breaches have occurred. It is possible to load these changes from the OLTP source to the OLAP data warehouse source with T-SQL or ETL processes.

There are two jobs created with the SQL Server Change Data Capture. The first populates database change tables with the changed information and the other cleans the change tables by deleting records that are older than the configurable retention.