1 / 4

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. An Introduction to Change Data Capture and SQL Server It is often seen for an application that the value of data in the database has to be recorded before it can be changed that is, the history of all data changed has to be saved. This in-built specification is mostly to ensure the security of data. To implement this feature there have been various solutions over time, ranging from timestamps, triggers, and other complex queries to auditing the data. Initially, SQL Server 2005 brought this feature with “after update”, “after insert”, and “after delete” triggers. While this was just a start, it did not fully resolve the issue of tracking changes made to data. A comparatively better solution was offered with SQL Server 2008 called Change Data Capture. This SQL Server Change Data Capturehas enabled SQL Server developers to archive and capture data without additional programming.

  2. Change Data Capture (CDC) however monitors changes only in user-created tables. Since captured data is later stored in relational tables it can be retrieved and accessed with the usual T-SQL. When CDC features are applied to a database table, a mirror of the tracked table is created. This has the same column structure of the original table but has additional columns of metadata which is used to review the type of change in the database table row. New audit tables can then be used by the SQL DBA to track the activity of the logged table.

  3. CDC records all updates, inserts, and deletes that have been applied to a SQL Server table making the details of all changes available in an easily understood format. Required column information and metadata for applying the changes to a target setting is captured for the modified rows and stored in change tables. These mirror the column structure of the tracked source tables.

  4. In SQL Server Change Data Capture, the SQL Server transaction log is the source of change for the CDC. Whenever inserts, updates, and deletes are applied to tracked source tables, the changes described in entries are added to the log which acts as a point of reference to the capture process. It then reads the log and adds info about the changes to the associated change table of the original table.

More Related