The Change Data Capture of Microsoft SQL Server

 The Change Data Capture feature of Microsoft SQL Server was started in 2005 with the launch of its “after date”, “after delete”, and “after insert” capabilities. However, there were certain flaws that surfaced which were ironed out in a new version in 2008, giving rise to SQL Server change data capture in its present form. This can monitor, track, and capture any changes that are made to the SQL Server database. 

SQL Server change data capture records all changes made to a SQL Server table like insert, update, and delete leading to details of all changes being available in a user-friendly relational format. Column information and metadata crucial to apply the changes to the target database are captured for the modified rows and stored in change tables that replicate the column structure of the tracked source tables. SQL Server change data capture has table-valued functions that allow consumers to have seamless access to the changed data.

The source of SQL Server change data capture is the transaction log. All changes like inserts, updates, and deletes are applied to the tracked source tables and these are added to the transaction log through entries that detail the changes made. Hence, the log can be said to be the basic input of the data capture process. Built-in capabilities in the SQL Server elaborate the changes that are present in the change tables and show the information as a filtered result set.

Two jobs are created by the SQL Server change data capture. One fills the database change tables with the changed data while the other cleans the change tables by deleting all records that go beyond the configurable retention.