Change Data Capture vs. Change Tracking: How We Built Our SQL Server Replication

Ever since launching PowerSync, we’ve had a steady stream of users asking us to support SQL Server. We're excited to announce that the wait is over and PowerSync now officially supports Microsoft SQL Server and Azure SQL Database as source databases! This means you can now sync data from your SQL Server databases to local SQLite databases on clients, enabling offline-first experiences for applications backed by Microsoft's flagship relational database.

Let's take a closer look at how we built this integration, the technical decisions we made, and how you can get started syncing your SQL Server data today.

How PowerSync Works with SQL Server

When you first connect PowerSync to your SQL Server database, we perform a full snapshot of your configured tables. This involves querying the tables to capture the current state of your data and storing it in PowerSync.

Once that is done the PowerSync Service switches to streaming replication mode, where it leverages Change Data Capture (CDC) to track and replicate changes from your source tables. Unlike Postgres’ logical replication, or MySQL's binary log streaming where we listen for change events, PowerSync has to continuously poll the CDC change tables for updates.

What is SQL Server Change Data Capture?

Change Data Capture (CDC) is a feature built into SQL Server that records %%INSERT%%, %%UPDATE%%, and %%DELETE%% activity on tables. When enabled, CDC captures row-level changes and stores them in special change tables that mirror the structure of your source tables, plus metadata columns that track the type of operation and transaction details.

CDC also provides system functions that allow one to query changes within a specific Log Sequence Number (LSN) range. This is exactly what PowerSync uses to efficiently poll for changes.

Why CDC Instead of Change Tracking?

SQL Server offers another more lightweight update tracking mechanism called Change Tracking, so why did we choose CDC? There are a few reasons:

1. Change Tracking only tells you that a row changed, not what changed: It is up to you to query the current state of a row after learning it was modified. This requires additional queries to the database for each row that was modified. This can potentially introduce inconsistencies, because the data may have changed between the time the change was detected and the time the row was queried.

CDC, on the other hand, captures the actual before and after values of each change. Functionally this more closely matches the behavior of our other database adapters.

2. Change Tracking requires that a table has a primary key or unique constraint: While this is not a problem for most applications (having a primary key is good practice after all), our other database adapters support syncing tables without a primary key or unique constraint.

3. Delete operations: When a row is deleted, Change Tracking only tells you the primary key of the deleted row. For proper downstream processing, PowerSync needs to know the actual values of the deleted row.

A Note on Change Event Streaming (Preview)

Microsoft recently introduced Change Event Streaming, a new preview feature that provides a more modern, event-driven approach to change capture. Basically updates are streamed into Azure Event Hubs from where they can be synced to other configurable destinations. This could potentially be an interesting option for integration, offering a more real time stream of updates, but the current hard coupling to Azure Event Hubs adds an extra layer of complexity. We'll definitely keep an eye on this feature as it matures!

Database Requirements

This section presents a high level outline of requirements. See the documentation for detailed setup instructions.

Before PowerSync can sync from your SQL Server database, we require the following:

CDC must be enabled on the database level.
1. Note that CDC is only available on specific SQL Server editions. We support:
  1. Standard Edition 2022 (v16) or newer
  2. Enterprise Edition 2022 (v16) or newer (includes Developer and Evaluation)
  3. Azure SQL Database
  4. Azure SQL Managed Instance
Each table you want to sync must have CDC enabled.
A database user for PowerSync to connect with, with the necessary permissions:
1. %%SELECT%% access to the source and the CDC change tables
2. %%SELECT%% access to the CDC schema.
3. %%VIEW SERVER PERFORMANCE STATE%%, %%VIEW DATABASE PERFORMANCE STATE%% access to server and database level performance state views.
A PowerSync Checkpoints Table has been created. The PowerSync database user requires %%SELECT%%, %%INSERT%% and %%UPDATE%% access to this table. This table is used to generate regular CDC entries that help us track replication progress accurately.

Implementation Deep Dive

Let's get into the technical details of how PowerSync syncs data from SQL Server. Our SQL Server integration consists of two main phases: Initial snapshot and Streaming replication.

Initial Snapshot

When PowerSync first connects to your database, we need to capture the current state of all the tables configured in the sync rules/sync streams. Here's how it works:

We resolve the schemas for all tables from your sync rules/sync streams.
Then we confirm that CDC is enabled for each table and that the database user has the necessary permissions to read the source and the change tables.
Finally we stream the table data in batches, using primary key ordering for resumable snapshots

‍Note: The snapshot uses SQL Server's %%READ COMMITTED%% isolation level. While this means data can change during the snapshot process, we compensate for this during the streaming replication phase. We record the current LSN when starting the snapshot and when completing the snapshot. We then start streaming replication from the snapshot start LSN. Once replication has caught up with all the changes between the snapshot start and end LSN, we know that the snapshot is complete and consistent.

Streaming Replication

After the initial snapshot, we enter streaming replication mode using CDC polling. The polling mechanism:

Calculates the LSN bounds for the polling cycle, limiting the number of transactions based on the polling batch size.
Uses the built-in CDC functions to retrieve changes within the LSN range.
Processes any applicable updates.
Records a new checkpoint once all tables have been processed for this polling cycle.

Note: The polling interval and polling batch size are configurable. The default polling interval is 1 second and the default polling batch size is 10 transactions. Reducing the polling interval helps reduce the latency at the cost of higher CPU usage. Increasing the polling batch size helps to increase the throughput per polling cycle at the cost of increased memory usage.

Challenges and Solutions

Building a robust CDC-based replication system came with several interesting challenges:

Challenge 1: CDC retention and data expiry

CDC doesn't keep change data forever — it has a configurable retention period (default is 3 days). If PowerSync is stopped for longer than this retention period, the changes since the last checkpoint will no longer be available.

Solution: Before resuming replication, we check if the last checkpoint LSN is still within the retention window for all the CDC change tables. If data has expired, we automatically trigger a fresh snapshot.

Challenge 2: Transaction affecting multiple tables

CDC change data can include transactions that may span multiple tables. We need to ensure we process complete transactions so that when we record a checkpoint, it includes all the changed tables for that transaction.

Solution: Even though we retrieve the updates for all the change tables sequentially, we only create a new checkpoint once tables have been processed for a polling cycle.

Challenge 3: Potentially large backlog of changes

If PowerSync is offline for a long time, a large backlog can build up in the CDC change tables. If we simply processed all the updates in a single polling cycle, we are likely to run into memory issues. We might also be starved for checkpoints for a long time.

Solution: We limit the number of transactions that can be processed in a single polling cycle to a configurable number. Smaller values mean more frequent checkpoints, and a lower memory footprint.

Challenge 4: LSN mismatches between the transaction log and CDC

The LSNs in the transaction log and the change tables are not always in sync. This is commonly the case when a bunch of transactions are recorded in the transaction log, which do not apply to any of the replicated tables. It can also happen if the CDC capture job is not able to read updates in the transaction log fast enough, causing a backlog.

‍Solution: Since we can't guarantee that an LSN we record even ends up in the CDC change tables, we need to be able generate a transaction on demand that we know will be replicated. This is where the PowerSync Checkpoints Table comes in. Basically if we record the current LSN, then update the checkpoint table, we can reliably wait for a transaction with an LSN greater than the recorded LSN to appear in the CDC. Using this mechanism we can track the CDC progress accurately.

Limitations

While our SQL Server integration is fully functional, there are some limitations to be aware of:

No true real-time streaming: The CDC replication mechanism involves polling. This adds some inherent latency to the replication process. See Latency and Performance Tuning below for more details.
Schema changes: Currently, schema changes (adding/removing columns, renaming tables) require manual intervention. We plan to support automatic schema change handling in the near future.
Temporal data types: Temporal data types are replicated with a maximum precision level of milliseconds.
Spatial data types: Spatial data types like geometry and geography are replicated as non standard spatial JSON objects as supplied by the Tedious node-mssql client.
CDC retention period: If PowerSync is offline longer than the CDC retention period (default 3 days), a fresh snapshot will be required.

Note: Azure SQL Database comes with its own set of limitations and considerations. See the Azure SQL Database Specifics section below for more details.

Latency and Performance Tuning

Due to the fundamental differences in how CDC works compared to logical replication (Postgres) or binlog streaming (MySQL), there is an inherent latency in replicating data from SQL Server to PowerSync. The latency is determined by two factors:

Transaction Log Scan Interval: The frequency at which the CDC capture job scans the transaction log for changes. The default value of 5 seconds can be changed by modifying the capture job settings on SQL Server. The recommended value is 1 second or lower, but this can add additional load on the database.
Polling Interval: The frequency at which PowerSync polls the CDC change tables for changes. The default value is once every 1000ms. This can be changed by setting the %%pollingIntervalMs%% parameter in the PowerSync configuration.

The combination of the log scan interval and the polling interval determines the overall latency from when a transaction is committed to when it is available in PowerSync. In practice the actual latency is lower than the sum of the two intervals, due to the fact that the CDC capture job and the PowerSync polling are running asynchronously.

Azure SQL Database Specifics

Azure SQL Database fully supports CDC, but there are some additional limitations to be aware of:

Transaction Log Scan Interval is not configurable: Azure SQL Database doesn't have SQL Server Agent. Instead, CDC capture and cleanup are handled by a scheduler automatically. This means you can't customize the capture job polling interval the same way you would for an on-premise SQL Server. This means that even with aggressive polling settings on the PowerSync side, your actual sync latency is bounded by Azure's internal CDC capture frequency. For most applications, this is measured in seconds, but it's something to be aware of for latency-sensitive scenarios.

Service Tier Requirements: CDC requires S3 tier or higher for DTU-based pricing, or any tier for vCore-based pricing. Basic, S0, S1, and S2 tiers are not supported.

Getting started

Ready to try PowerSync with SQL Server?

PowerSync Cloud

Our SQL Server adapter is available in PowerSync Cloud. To get started see our official docs.

Open Edition

We've prepared a complete self host demo app to help you get started quickly. This demo includes:

Docker Compose setup with a CDC enabled SQL Server
Sample database schema for a todos app and corresponding client application
Pre Configured PowerSync service with sync rules

What's Next?

This release marks an important milestone in our mission to bring offline-first capabilities to every application, regardless of the backend database. With SQL Server and Azure SQL Database support, PowerSync now covers the major enterprise database platforms.

We'll be continuing to improve the SQL Server integration with:

Automatic schema change handling
Better handling of spatial data types
More authentication options for Azure SQL

Join our Discord server if you have any feedback for us.