1 / 7

SQL Server Change Data Capture

Contact BryteFlow to know more about replicating, merging, and transforming data to Snowflake, Amazon S3, and Amazon Redshift. Large companies trust us with data management solutions like reducing data deployment times or getting market insights delivered faster. Unlock large volumes of complex enterprise data including SAP across your organization with BryteFlow.

johnsonsmiths262
Download Presentation

SQL Server Change Data Capture

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 and SQL Server – An Overview

  2. Data security is one of the primary concerns of organizations around the world, regardless of the size and scale. Data safety in this context is not only about data leak or breach or even being hacked, it has a host of other implications too. A critical aspect of data security is saving changed data. The value of data has to be saved in the database before the change so that its history is not compromised. To make sure that data changes are preserved, there have been many solutions in the past ranging from triggers, timestamps, complex queries, and even carrying out auditing of data.

  3. SQL Server introduced “after update”, “after insert”, and “after delete” features to ensure SQL Server Change Data Captureactivity in 2005. But it still did not completely solve the issue of accurately tracking changed data. In 2008, SQL Server brought in Change Data Capture technology that ensured that developers could capture and archive data without going through tedious additional programming. What is Changed Data Capture (CDC)? It is a process that tracks and monitors all changes made in user-created tables and these changes are later stored in relational tables for easy retrieval and access with the T-SQL. When the features of the CDC are applied to a database table, a replicated image is created of the tracked table.

  4. The column structure of this mirrored table is the same as the original but with one difference- it has additional columns of metadata that are used to check the type of changes made in the database row. The new audit tables after SQL Server Change Data Capturecan then be used by the SQL DBA to monitor the logged table and all other activities that have taken place. SQL Server Change Data Capturerecords, inserts, deletes, and updates all data that has been applied to a SQL Server table after ensuring that the details of all the changes are available in a user-friendly format. The required information on column and metadata for applying the changes is captured for the modified rows which are then stored in the change tables, replicating the complete column structure of the tracked source tables.

  5. The SQL Server transaction log in the SQL Server Change Data Captureprocess is the source of change in CDC. Whenever any modifications take place (inserts, updates, deletes) to the tracked source tables, the changes described in entries are added to the log. This is the point of reference in CDC. The log is then read and information added about the changes to the linked change table of the original table.

  6. What is important is that before individual tables are tracked within a database, SQL Server Change Data Capturehas to be enabled for the database and an associated capture instance has to be created to support the changed data in the source table.

More Related