In this post, you will get the many intricacies of the Microsoft SQL Server CDC (Change Data Capture) feature including its origin and evolution, functioning, and types. The CDC feature is available on both the Azure SQL Managed Instance as well as the Microsoft SQL Server.
The Need for the Change Data Capture Feature
Almost all organizations in today’s business ecosystem depend on data to power their operations and hence stringent data security measures and data durability is very critical. This is where Change Data Capture has a crucial role to play. It not only makes sure that all data in the system is firewalled and insulated from breaches and hackers but also stores changed data in a manner where their values and history are not compromised. To this effect, over time, various solutions have been experimented with such as complex queries, timestamps, triggers, and data auditing, but none had met with a great deal of success.
The Launch and Evolution of SQL Server CDC
In 2005 Microsoft launched their SQL Server CDC product with “after update”, “after insert”, and “after delete” features. However, database administrators found this CDC process too complex and was hence not much used. The first taste of success came when a revised version of SQL Server CDC was introduced in 2008 wherein developers could capture and archive changes and historical data without going through any other additional activities. It was very user-friendly and became very popular and is still widely applied today.
The Concept Behind SQL Server CDC
The SQL Server is used by SQL Server CDC for making changes such as insert, update, and insert. The details of the changes are available to users in a simple relational format. Also available for the changed and modified rows are the inputs needed to capture the changes to the intended target like metadata and column information. The changes made are then recorded in tables that replicate the architecture of the columns of the tracked stored tables. Access to this change data is strictly regulated by table-valued functions.
A very optimized example of the SQL Server CDC technology targeting the consumer is the ETL or Extract, Transform, and Load application. The ETL application moves the modified and incremental data from the source tables in the SQL Server to a data warehouse.
SQL Server CDC has an edge over other similar concepts in this field because it is an improvement over the previous processes. Typically, it is necessary to continuously refresh the source tables in a data storage repository that replicates the changes made to them. This can be quite tedious. On the other hand, the SQL Server CDC technology ensures a seamless flow of change data that users can apply to various target platforms they feel appropriate.
The Workflow of SQL Server CDC
Any changes that are made to tables created by a user are tracked and monitored by Change Data Capture which are later stored in relational tables. This data can be easily accessed and retrieved with T-SQL. Whenever the features of the CDC technology are applicable to a database table, a replicated image is created of the tracked table.
Further, the form of changes made in the database row is checked by the additional columns of metadata that are present within the structure of the replicated table. Apart from this difference, the two – replicated tables and the source tables – are similar in all respects. After completing the SQL Server CDC function for tracking the logged tables and other activities that have taken place, SQL DBA can use the new audit tables.
The source of change in the CDC is reflected through the transaction log in the SQL Server CDC. Immediately after any modification is noticed in the tracked source tables, all details of these entries are added to the log. This log is then referred to and the specific content of the changes is connected to the change table portion of the original table.
The Types of SQL Server CDC
There are two types of SQL Server CDC with most organizations starting with the first and then moving on to the second.
#Log-based CDC
In this mode, the changes made in the source are found by the system through the transaction log and file of a database. These changes at the source are then replicated in the target database. The main advantage of this log-based CDC is that no changes are missed (and is therefore highly reliable) apart from having a negligible impact on the production database system. Schemas of the production database do not have to be changed and neither is there a need to add new tables.
The drawback here is that this method is very complex and can be used with databases that support log-based CDC only.
#Trigger-based CDC
This mode of SQL Server CDC has triggers based on the database which are automatically set off whenever a change occurs, thereby lowering the costs of extracting the changes. However, the overhead costs of operating the source system increase as more runtime is required to allow the database to refresh every time.
There are several advantages of this trigger-based SQL Server 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.
There are some downsides too. Sometimes the triggers get disabled when there is an overload of operations. Also, the performance of the database is adversely impacted as in this method, multiple writes to a database take place whenever changes are made to the rows.
Summing up, SQL Server CDC has changed completely the existing CDC scenario.
