1 / 6

Introducing CDC (Change Data Capture) in Microsoft SQL Server

Get Enterprise-level Data Integration for Snowflake, S3, SQL Server, Redshift, Azure Synapse, and more. Lightning-fast data replication in real-time 24x7 in your data lake. For large enterprises, fast data ingestion in bulk with partitioning and multi-thread loading. Create real-time data lakes without any coding. <br>t

pstrong638
Download Presentation

Introducing CDC (Change Data Capture) in Microsoft 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. Introducing CDC (Change Data Capture) in Microsoft SQL Server How does CDC (Change Data Capture) apply to SQL Server tables? SQL Server CDC tracks changes like Insert, Update, and Delete that are applied to SQL Server tables and records what changed, where changed, and when changed in simple relational change tables that are easy to understand. Further, these change tables have columns that show the structure of the columns of the source table that is being tracked. Metadata is also available to understand the changes that have been made through SQL Server CDC.

  2. It is often seen that the specifications of an application require that before changing, the value of the data in a database should be recorded. Simply put, all the history of the changes made to the data must be saved, specifically for safety and security purposes. The processes hitherto followed by businesses have been timestamps, triggers, and complex queries to audit data.

  3. The concept of SQL Server CDC was launched in 2005 with new features in SQL Server of ‘after update’, ‘after insert’ and ‘after delete’ triggers, greatly solving the problem of tracking changes to the data. However, a better solution of SQL Server CDC was introduced in 2008 that ironed out the flaws of the previous version. It was named Change Data Capture (CDC) and helped developers to provide SQL Server data archiving and capturing without additional programming.

  4. The new 2008 version of SQL Server CDC tracks changes only in user-created tables. They can be quickly accessed and retrieved later using regular T-SQL because the data captured is stored in relational tables. • What happens when Change Data Capture features are applied to a database table? A mirror is created of the tracked table with the same column structure as the original table. But the difference with the original table is that there are additional columns that have the metadata that was used to summarize the type of the change in the database table row. The DBA handling the SQL Server CDC can seamlessly monitor the activity of the logged table by using the new audit tables.

  5. How does the SQL Server CDC function? • While all changes of SQL Server CDC are recorded in mirrored tables, other columns in the SQL Server are recorded in the following format. • • One record is written by the SQL Server showing the inserted values for every Insert statement. • • One record is written by the SQL Server showing the deleted values for every Delete statement.

  6. • Two records are written by the SQL Server showing the updated values for every Update statement. One shows the data before the changes are made and the second shows the value after the changes are executed. • Finally, two jobs are created by the SQL Server CDC. First, the changed data fills the database change tables while in the second, the change tables are cleaned by deleting those records that are older than the configurable retention.

More Related