Data security and safety is the prime concern of businesses in today’s data-driven environment. The focus is heavily tilted towards ramping up existing measures to preserve and protect historical data as well as attempts to prevent any form of hacking or data breaches. Database platforms like Oracle and Microsoft SQL Server have over time given this aspect considerable thought and introduced various features like triggers, complex queries, data audits, and timestamps to fight the menace.
First off the mark was Microsoft in 2005 with “after date”, “after delete”, and “after insert” features that kickstarted its SQL Server Change Data Capture. However, it was only in 2008 that Microsoft came up with an updated SQL Server Change Data Capture that has proved to be very effective.
The functioning of SQL Server Change Data Capture is as follows – it monitors and captures all changes that are made in the tables of the SQL Server database without the use of any additional programming or applications. In the initial stages till 2016, this feature was available out of the box in the most expensive Enterprise edition of SQL Server but later was standard across all versions.
All activities like insert, update, and delete that take place to a SQL Server table are recorded by SQL Server Change Data Capture and these are available in a user-friendly format. Metadata and column information necessary for applying changes to the target database is captured. Alternately, the modified rows are stored in the change tables that duplicate the column structure in the tracked source tables.
In addition to SQL Server Change Data Capture that tracks Insert, Update, and Delete operations, all changes are also recorded in a mirrored table that has similar column structure as the source tables.
Extra columns having the following format also track the changes made at the source database.
· SQL Server writes a record showing the inserted value for every Insert statement
· SQL Server writes a record showing the delete value for every Delete statement
· SQL Server writes two records for every Update statement where one shows the data before the change and one after the change has been made.
In the additional columns, the following data is present in the SQL Server Change Data Capture.
· __$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 the updating columns
This is the functioning of SQL Server Change Data Capture in Microsoft SQL Server.