Change data capture (CDC) is one of the most effective ways to enable real-time data movement. Thankfully, it’s also a feature in Microsoft SQL Server. First introduced in the 2008 edition, SQL Server’s CDC feature is extremely useful for tracking the most minute changes in real time. As a result, it’s become an indispensable tool for not only real-time tracking but also data migrations, analytics, audits and fraud protection — all while reducing downtime and increasing data integrity.

In this article, we’ll give a detailed description of a CDC SQL Server, as well as use cases and simple procedures for installation.

What is a CDC SQL Server?

A SQL Server change data capture (CDC) is an instance of Microsoft SQL Server with change data capture enabled. CDC is an invaluable tool for any data storage solution — and Microsoft SQL Server is no exception.

Enabling CDC in Microsoft SQL Server allows administrators to capture and record database changes in real time. These changes are the familiar SQL INSERT, DELETE and UPDATE statements that dictate how data fields are updated.

So what’s the big deal? Shouldn’t administrators already have this information?

While they do to some degree, what they can do with it is limited without enabling CDC. By enabling CDC, administrators won’t just be able to view change events but also implement them in various applications.

Before we get into how to enable CDC in SQL Server, let’s first explore CDC itself, what it does and why it’s such a valuable tool for data tracking.

What is change data capture (CDC)?

Change data capture (CDC) is a software process that runs a capture job to capture changes in a database — hence the name.

More specifically, CDC identifies and tracks these changes as they occur in a current database, whether it’s running Microsoft SQL Server or something else.

These changes are the SQL operation types performed on the database’s source table. Upon identifying these changes, CDC publishes them to a target table. As this target table essentially tracks the changes that occur over time, it can be used in any application where tracking changes is useful, such as audits or incremental backups.

While CDC may seem like an involved process, it happens in near real time to target systems. This ability to instantly and accurately collect change data offers many valuable benefits and use cases, especially for SQL Server.

What is SQL Server?

There’s a good chance you’re already familiar with Microsoft SQL Server. Whether you are or aren’t, however, it still helps to understand the basics of SQL Server so we can later see how CDC fits into the whole package.

Microsoft SQL Server is an RDBMS – short for Relational DataBase Management System. As the term might suggest, its sole purpose is to manage relational databases, which organize data into individual tables of rows and columns according to predefined relationships (or relations).

In most cases, SQL Server is simply used to store and retrieve data as needed by specific users or applications. Like any other relational database manager, these actions are performed with SQL operations — the very same we covered earlier (the UPDATE operation, DELETE operation and INSERT operation) that CDC keeps track of.

However, SQL Server’s SQL capabilities aren’t limited to these commands. The RDBMS also offers advanced variations of SQL, including ANSI SQL and its proprietary Transact SQL (or T-SQL), which allows administrators to handle exceptions, set variables and so on. As we’ll see later, many CDC features are easily enabled with T-SQL commands.

With advanced features for SQL query processing, analytics and more, Microsoft SQL is one of the market's most popular and powerful enterprise RDBMSs. Data integration tools like Fivetran also offer specialty integrations for SQL Server to help streamline Extract, Transform, Load (ETL) or Extract, Load, Transform (ELT), CDC and other processes.

CDC in Microsoft SQL Server

CDC has been an included feature in Microsoft SQL Server since 2008. Though originally limited to the enterprise edition, it's been available in all editions since 2016.

SQL Server implements CDC by creating two SQL Server Agents. The first agent adds change information to the database tables, while the second agent cleans and removes older data according to a preset retention policy. SQL Server also creates a special list of tables — cdc schema, cdc user and metadata tables — specifically for CDC.

In summary, CDC is an incredibly powerful and useful feature in SQL Server. However, it may not always be the ideal solution for some applications, leaving many administrators to adopt third-party CDC tools instead.

Benefits and drawbacks of CDC SQL Server

With the background out of the way, let’s get down to business: why should (or shouldn’t) you enable the CDC feature in SQL Server?

The exact answer depends on your organization and its applications. While tracking changes in real time has several obvious benefits for many applications, SQL Server’s CDC feature also has some drawbacks and limitations.

Benefits

• Supports data replication in tables without a primary key. The CDC feature isn’t SQL Server’s only data replication capability. However, it’s the only one that doesn’t require tables to have a primary key column, making it ideal for real-time and incremental replication where there isn’t one.
• Compatible with Always On availability groups. Suppose you use SQL Server’s Always On availability groups feature to maintain replicas for disaster recovery. In that case, you’re lucky: it’s also compatible with the CDC feature, which automatically replicates CDC data to the replicas.

Drawbacks

• Not always ideal when running a ton of SQL operations. SQL Server’s CDC feature cannot slow down or block DML (database manipulation language) activity such as SQL operations. As a result, it’s not always efficient for tables that undergo many DML/SQL operations.
• Setup isn’t always easy. Though enabling the CDC feature isn’t exactly difficult, it still requires more manual work than most tools specializing in data integration or pipeline automation.
• Many integrations require custom coding. By extension of the above, the CDC feature doesn’t automatically integrate with external storage solutions such as a data warehouse or data lake. Instead, these integrations need to be custom-coded, which may not always be straightforward. Note that these will also have to include monitoring and alert solutions.
• Maintenance of custom code/integrations. If writing custom code wasn’t demanding enough, you’ll also have to maintain it over time. That also means maintaining robustness and reliability, as well as adapting to new tables and data types.

Overcoming drawbacks with data integration tools

In short, using the built-in CDC feature often requires custom configurations and hand-coded integrations. These not only increase your upfront overhead but also increase long-term costs as you’ll usually have to maintain them yourself.

Thankfully, data integration tools like Fivetran have built-in CDC tools that automatically integrate with SQL Server. Using a separate tool also gives you greater flexibility in other areas, especially with connections for almost every data source.

Use cases for CDC in SQL Server

Whether you use SQL Server’s built-in CDC feature or a third-party tool, there are many exciting uses for CDC in SQL Server.

While the following list covers most major use cases, it isn’t exhaustive. In practice, CDC can benefit any application or workflow where gathering real-time updates and data changes are useful.

Real-time data replication

CDC is an invaluable tool for data replication. The data replication process is exactly what the name suggests: continuously making copies or replicas of data to store on another device. As a result, CDC is an essential tool for creating backups, synchronizing files across distributed systems and so on.

While SQL Server offers several data replication capabilities, choosing CDC provides a couple of unique benefits.

The first benefit is that replication happens in real time, allowing for near-instant updates as changes are made. This makes it possible to instantly sync files and create backups that minimize data loss and meet your recovery point objectives (RPOs).

The second benefit is that the CDC feature is more flexible than SQL Server’s other real-time replication capabilities. Much of this flexibility comes from not requiring a primary key, which may not always be present in analytics datasets.

Real-time analytics

With more data available than ever before, it’s not only useful to analyze data in real time — it’s also necessary. That’s especially true if your data sources provide a constant stream of new data, where the value and quality of data can drop quickly.

It’s not hard to see where CDC is useful here. By capturing real-time changes and writing them to your destination data repository, you can support real-time analytics without having to supply new data in batches manually.

There is a caveat, however. 

Since the CDC feature doesn’t come with any pre-built integrations, connections to your data sources and data repositories may require a lot of custom code. As a result, most organizations prefer using third-party data integration tools like Fivetran to build their CDC SQL Server.

Migrating data into a data repository

Data warehouses, data lakes and other types of data repositories see a lot of data traffic. 

Whether they’re used for analytics, backups, file synchronization or anything else — data repositories need fast and reliable updates to keep things running smoothly and accurately.

Since CDC constantly migrates data as changes occur, you won’t need to set aside time and resources to perform migrations. That also means little to no variation between live data and data storage, resulting in little to no data loss during an outage.

Of course, new data repositories may need a single “bulk” migration to get started. After this point, however, this won’t be necessary as long as CDC is working in the background.

Zero-downtime cloud migrations

Many organizations migrating from on-premise to cloud infrastructure face an important dilemma: how can we migrate our data without an interruption of service? 

Again, CDC comes in for the rescue. By extension of the migration capabilities described above, CDC allows for seamless cloud migration with zero downtime. Here, any cloud infrastructure is just another data repository and neither end will have to shut down just to migrate data. This capability is critical for applications where uptime is critical, such as in medical or retail.

Fraud detection

Change data events can tell a rich story of how your data is being used — especially when it comes to fraud.

As a result, many organizations keep a close eye on their data to detect patterns that could indicate fraudulent transactions. Since fraud must be detected and mitigated as quickly as possible, CDC is an ideal tool for real-time analysis of potential fraud. Such measures may also be necessary for maintaining regulatory compliance in industries such as finance.

Conclusion

Change data capture (CDC) is a powerful tool for adding real-time capabilities to SQL Server. By enabling the CDC feature, you can enjoy real-time replication, zero-downtime migration and support analytics for business intelligence and transaction fraud detection.

However, SQL Server’s built-in CDC feature doesn’t always make integrating with source databases and data repositories easy. As a result, many companies have turned to Fivetran to not only implement seamless CDC but also automatically build data pipelines that seamlessly connect their SQL Server to virtually anything in their data stack.

‍