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
Post a Comment