This post is a one-stop learning medium where you get everything there is to know about Microsoft SQL Server CDC (Change Data Capture). You will be able to navigate from the concept of Change Data Capture, to the evolution of SQL Server CDC, the functions and features, and finally the types of CDC.
The Generic Concept of Change Data Capture
Most businesses today, regardless of their size and scale, are data-driven and depend on cutting-edge technologies to operate their systems. In such a scenario, data durability and security are of paramount importance. This is where the Change Data Capture technology has a crucial role to play.
In Change Data Capture (CDC), all data is firewalled and insulated from hackers and unscrupulous elements, thereby ensuring optimized security. Another key feature of CDC is to store changed data in a manner where its history before the change is not compromised. In the past, various resolutions had been tried in this regard, but without any significant success. These included complex queries, timestamps, triggers, and data auditing.
It was only when Microsoft came up with its SQL Server CDC that a resolution to this issue was achieved.
The Evolution of Microsoft SQL Server CDC
Microsoft launched its SQL Server CDC product in 2005 with “after update”, “after insert”, and “after delete” features. The acceptance of this feature was quite poor among DBAs, who found it quite complex to work with. Keeping this feedback in mind, Microsoft introduced a revised version of SQL Server CDC in 2008. Here, DBAs and developers could capture and archive changes and historical data without depending on other additional activities to do so.
Because of its user-friendly features, this form of SQL Server CDC became very popular and is still in use today.
SQL Server CDC Explained
SQL Server CDC uses SQL Server to make changes to the data, such as insert, update, and delete, with their details available to users in a simple relational format. They also get the inputs required to capture the changes to the intended target, like column information and metadata for the modified and changed rows.
The changes that are captured are recorded in tables that are a mirror image of the tracked stored tables. Access to the change data tables is strictly controlled through table-valued functions.
The ETL (Extract, Transform, and Load) application is a classic example of the SQL Server CDC technology. The application extracts data from the source table, transforms the structure to match the target table, and then loads it into the intended target. It is ideal for moving change and incremental data from the source tables in the SQL Server to a data storage repository.
The cutting-edge and advanced technology of SQL Server CDC gives it an edge over others in this niche. In Change Data Capture processes in the past, it was necessary to continuously refresh the source tables to spot any changes made to them. SQL Server Change Data Capture, on the other hand, ensures a constant flow of changed data to be applied to various platforms by users.
Functioning of SQL Server CDC
Change Data Capture tracks and monitors all changes made to tables created by users. These changes are then stored in relational tables and easily accessed and retrieved by T-SQL. Thereafter, a replicated table is created that mirrors the tracked table whenever the features of the CDC technology are applied to a database table.
The structure of the replicated table has additional columns of metadata that check the type of change made in the database row. This is the only point of difference between the source tables and replicated tables, as otherwise, they are similar in all respects. The new audit tables can be used by SQL DBAs after going through the SQL Server CDC function for tracking the logged tables and other activities.
The transaction log in the SQL Server CDC reflects the source of change in Change Data Capture. As soon as any change is registered in the tracked source tables, all the details of the changes are included in the log and can be referred to by users. The content of the changes is linked to the change table portion of the original table.
Types of SQL Server Change Data Capture
There are two types of SQL Server CDC. For the best results of Change Data Capture, it is advisable to start with the first and then move on to the second.
- Log-based CDC
In this process, the changes made at source are identified by the system through the file of a database and the transaction log. These changes made at the source are then moved to the target database.
The advantage of the log-based CDC is that it is very reliable, as all changes are scrupulously considered and none are missed. This CDC process has a negligible impact on the production database system. Moreover, schemas of the production database do not have to be changed, nor do new tables need to be added.
The downside here is that the process is very complex and can only be carried out on databases that are compatible with log-based CDC.
- Trigger-based CDC
This form of SQL Server CDC has triggers based on databases. These are set off automatically whenever a change to data is identified. This lowers the cost of extracting the changes.
There are several benefits of the trigger-based CDC. Implementation is simpler, shadow tables provide detailed logs of all transactions, support is received directly in the SQL API for specific databases, and finally, changes take place faster.
The downside is that with heavy operational loads, the triggers are often disabled. Moreover, in this method, multiple writes to a database take place whenever changes are made to the rows, thereby adversely affecting database performance.