In this post, we will go step by step into several aspects of Change Data Capture, a feature that is available on the Azure SQL Managed Instance and Microsoft SQL Server. The focus will be on the highly optimized SQL Server Change Data Capture feature, its evolution, functions, and types.
The Change Data Capture (CDC) is a very important component in the working of databases in the modern digital data-driven business ecosystem. Apart from ensuring that the confidentiality of data is not compromised and data breaches do not happen, the CDC feature also makes sure that changes made to data are stored in a way that keeps their history and values safe and secure.
This requirement is not new. Efforts have been made in the past too to work out a solution in this direction with data auditing, complex queries, triggers, and timestamps but none of the options gave the required results. It was only when Microsoft came up with the SQL Server Change Data Capture tool that a real solution was found which is used even today.
Microsoft launched its SQL Server Change Data Capture in 2005 with its “after update”, “after insert”, and “after delete” feature. Though it was a path-breaking achievement, it fell short of the required benefits with certain discrepancies. Keeping the feedback from hands-on users and DBAs in mind, a revamped version was introduced in 2008 with features whose working exceeded all expectations.
Needless to say, this version is in use to this day as it seamlessly allows developers and DBAs to capture and archive data without having to perform additional cumbersome
programming activities.
What is SQL Server Change Data Capture
In SQL Server Change Data Capture, change activities in a table such as delete, insert, and update are easily available in a relational format. All the required inputs necessary to track changes made to a target system like column information and metadata are captured for the modified rows. These changes are then stored in tables that reflect the column structure ofthe tracked stored tables.
Access to the change data is strictly controlled as it is a matter of data security of the table-valued functions.
Looking at the best example of a user targeted by this Change Data Capture technology, it is the Extract, Transform, and Load (ETL) application. In SQL Server Change Data Capture, changes made to data in a source table are moved by an ETL application to a data mart or a data warehouse.
In traditional systems, source tables placed within a data warehouse mirror all changes made to them. This technology is complex and not easy to use as the source tables must be continually refreshed to capture incremental changes made to them. SQL Server Change Data Capture is a more convenient technology that allows a steady stream of change data structured to help consumers apply it to divergent target representations of the data.
Working of the SQL Server Change Data Capture
First, let us understand the workflow of the SQL Server Change Data Capture. This feature tracks changes made to tables created by users which are then stored in relational tables. This data can be easily accessed and retrieved with T-SQL. A mirror image is created of the tracked table whenever the attributes of the CDC technology are applied to a database table.
All aspects of the source tables and the replicated tables are similar in every way except one. It is that the column structure of the replicated tables has additional columns of metadata. These primarily check the type of changes made in the database row. The new audit tables are used by the SQL DBA after going through the SQL Server Change Data Capture function to track the logged tables and other activities that have occurred.
The source of changes made by CDC is shown in the transaction log of the SQL Server Change Data Capture. Immediately after changes such as updates, deletes, and inserts are identified in the tracked source tables, their details are added to the log and become the point of reference in SQL Server CDC. This log is then read and all details of the changes are linked to the change table part of the original table.
Types of SQL Server Change Data Capture
There are two types of SQL Server CDC and it is the general norm of organizations to start the activity with the first before going on to the second.
Log-based CDC
The CDC process here is a straightforward one. The system reads the transaction log in a database to identify the changes made at the source and then replicates them to the target database. The benefit here is that the process is very reliable and every change is captured without missing out on any of them. There is also no need to change the schemas of the production database or add new tables.
On the flip side, log-based CDC is not user-friendly and works well only with databases that support log-based CDC.
Trigger-based CDC
In this method, triggers are placed in the source databases that are activated whenever a change takes place, thereby lowering the cost of extracting changes made at the source. The plus point of trigger-based CDC is that it is easy to implement, changes occur faster, detailed logs of all transactions are provided in shadow tables, and direct support is received in the SQL API for selected databases.
There are certain downsides to this form of SQL Server Change Data Capture. First, the expenses of the CDC are more due to the additional runtime required during every database refresh.
Further, there is a possibility of trigger overload during heavy transactions with triggers getting disabled during certain operations.
