In 2005, Microsoft introduced the cutting-edge SQL Server Change Data Capture feature. Its cutting-edge capabilities could capture changes made to the source tables in the form of “after update”, “after insert”, and “after delete”. However, the feature was not user-friendly and was quite complex.
Keeping the feedback in mind, Microsoft launched a revised version in 2008, which lived up to the expectations of users. With this mode of SQL Server Change Data Capture, developers and DBAs could capture and archive historical data and changes without the need for additional programming or other activities.
SQL Server Change Data Capture tracks and captures changes made to user tables that are then stored in relational tables. These changes can subsequently be retrieved with T-SQL. When Change Data Capture is applied to a database table, a mirror image of the tracked table is created.
Changes made in database rows can be easily identified as the structure of the columns of the replicated tables has additional metadata columns. Apart from this difference between the source tables and the replicated ones, the two are similar in all other respects. Once the SQL Server Change Data Capture process is over, the new audit tables may be used to track the logged tables and monitor all activities that have been completed.
The source of the changes is reflected in the transaction log of SQL Server CDC. The details of changes are entered in the log as soon as any changes, like updates, deletions, or insertions, are seen in the source tables being tracked. These changes then become an integral part of Change Data Capture. The log contains a detailed description of the changes, and these can be read and linked to the change table component of the original table.
There are two types of SQL Server Change Data Capture, and users can use one per their convenience.
Log-based CDC
Here, the system first analyzes the file and transaction log of the database so that users can know about the changes made at the source. Next, all changes made at the source database are replicated in the target database.
The main benefit of this process is that it is very reliable, as all changes made are clearly reflected.
Trigger-based CDC
In this method, triggers in databases are set off automatically whenever a change occurs in the source database, thereby substantially reducing the cost of extracting the changes. However, there is an increase in database operating costs as there is more run time of the source systems, as the database has to be refreshed every time a change is made.
The benefit of this type of SQL Server Change Data Capture is that it offers direct support for selected databases in the SQL API, finds details of all transactions in the shadow tables, and ensures faster implementation of changes.