1 / 4

Change Data Capture (CDC) for SQL Server

A very common occurrence in todayu2019s data-driven business environment is data breaches. One of the ways that organizations seek to get around this problem is to ramp up data security to preserve and protect historical data. Keeping this in mind, the top database platforms have introduced various solutions like timestamps, complex queries, triggers, and data audits. Both Microsoft SQL Server and Oracle database have launched solutions to this end.

johnsonsmiths262
Download Presentation

Change Data Capture (CDC) for SQL Server

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Change Data Capture (CDC) for SQL Server

  2. A very common occurrence in today’s data-driven business environment is data breaches. One of the ways that organizations seek to get around this problem is to ramp up data security to preserve and protect historical data. Keeping this in mind, the top database platforms have introduced various solutions like timestamps, complex queries, triggers, and data audits. Both Microsoft SQL Server and Oracle database have launched solutions to this end. In 2005, SQL Server was off the mark with “after date”, “after delete”, and “after insert” features to enable SQL Server Change Data Capture (CDC). But that did not solve the issue until in 2008 SQL Server introduced a modified version of change data capture that turned out to be very effective. CDC helps to track and capture all the changes that are made on the SQL Server database tables without extra programming. In the initial stages up to 2016, SQL Server Change Data Capture was enabled only in the Enterprise Edition of the SQL Server but now, it is a universal feature in the SQL Server. 

  3. Change Data Capture keeps a track of the Insert, Update, and Delete operations on the table of a database. All these changes are recorded in detail in a mirrored table that has the same column structure as the source tables. There are additional columns too that records the descriptions of the changes made. It goes like this. · 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. 

  4. The additional columns have the following data. · __$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 sametransaction, __$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 With this detailed information, users can easily monitor the SQL Server Change Data Capture changes made for ensuring data security and carrying out data audits. These changes can also be loaded from the OLTP source to the data warehouse OLAP source with T-SQL or ETL processes.  For SQL Server Change Data Capture it is required that a SQL Server Agent runs on a SQL Server instance. Two jobs are created – one ensures populating database change tables with the changed information and the other cleans up the change tables by deleting records that are older than the configurable retention. 

More Related