Several issues are faced by organizations today in the areas of data security and safety and ramping up systems for preservation of historical data. Leading database platforms took steps in this regard by launching data audits, timestamps, complex queries, and triggers, one of them being Microsoft. It led the innovation when in 2005, it introduced the SQL Server CDC with the “after date”, “after delete”, and “after insert” features.
SQL
Server CDC captures and records all
activities like insert, update, or delete that are applied to a SQL Server
table. Changes made are available in a user-friendly relational format and
metadata and information that are required for posting changes to the target
databases are captured in modified rows. These are stored in change tables with
the same structure as the columns in the tracked source tables. SQL Server CDC also tracks and records changes in the mirrored
tables with column structures that are the same as the source tables. This is
excluding the Insert, Update, and Delete activities.
The following data is
present in the additional columns that also track changes made at the source
tables.
·
__$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 attributes
of SQL Server CDC.
Comments
Post a Comment