CDC replication

📘

CDC replication only for Bulk Syncs

CDC replication from Microsoft SQL Server is only available for Bulk Syncs.

Bulk Syncs from Microsoft SQL Server can use SQL Server's change data capture (CDC). CDC is recommended but not required. Instead of running full table scans to find changes since the last sync, Polytomic reads the change tables that SQL Server maintains.

Requirements

• A SQL Server edition and tier that supports CDC. CDC is available in SQL Server Standard and Enterprise editions, Amazon RDS for SQL Server, Azure SQL Managed Instance, and Azure SQL Database (vCore tiers, or DTU tiers S3 and above). It isn't available in SQL Server Express.
• A capture process that's running. On self-managed SQL Server, Amazon RDS, and Azure SQL Managed Instance, CDC capture and cleanup run as SQL Server Agent jobs, so SQL Server Agent must be running. Azure SQL Database uses a built-in scheduler instead.
• A primary key or unique index on each table. Polytomic only reads changes for capture instances that have index columns. Polytomic uses full table scans to sync views, which don't support CDC, and tables that have neither a primary key nor a unique index.
• Exactly one capture instance per table.

Set up CDC

1. Enable CDC on your database

Run this in the database you're syncing from:

EXEC sys.sp_cdc_enable_db;

This requires membership in the sysadmin server role (self-managed SQL Server and Azure SQL Managed Instance) or the db_owner database role (Azure SQL Database). See Microsoft's sp_cdc_enable_db documentation for more details.

On Amazon RDS for SQL Server, which doesn't grant sysadmin, run this instead as the master user:

EXEC msdb.dbo.rds_cdc_enable_db 'YourDatabase';

2. Create a role for reading change data

CDC can restrict access to change data with a gating role. Create a role for Polytomic and add Polytomic's user to it:

CREATE ROLE polytomic_cdc;
ALTER ROLE polytomic_cdc ADD MEMBER polytomic_user;

Polytomic's user also needs SELECT on every captured column of each source table, which the read permissions already cover.

🚧

Polytomic's user must be able to read the capture instance

SQL Server only reports capture instances that the current user is allowed to read. If Polytomic's user isn't a member of the gating role, Polytomic doesn't see the capture instance and silently falls back to full table scans.

3. Enable CDC for each table you want to sync

Run this for each table (substitute your schema and table names):

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name = N'TableName',
    @role_name = N'polytomic_cdc',
    @supports_net_changes = 0;

If the table doesn't have a primary key, specify a unique index with @index_name = N'IndexName'.

sp_cdc_enable_table creates a capture instance for the table's columns as they exist at that point in time.

4. Turn on CDC in Polytomic

Turn on the Use change data capture for bulk syncs setting on your Microsoft SQL Server connection in Polytomic:

Azure SQL Database and Azure SQL Managed Instance

The Azure SQL connection type doesn't support CDC. To use CDC with Azure SQL Database or Azure SQL Managed Instance, connect with the Microsoft SQL Server connection type instead, using the same server, database, and credentials.

On Azure SQL Database, enabling CDC requires db_owner. Microsoft's Azure SQL Database CDC documentation covers additional limitations, including databases created by Microsoft Entra users and serverless databases (CDC doesn't run while a serverless database is paused).

Retention

SQL Server's cleanup job deletes change data older than the retention period, which defaults to 3 days (4,320 minutes).

🚧

Changes older than the retention period are lost

If Polytomic doesn't sync a table before the cleanup job deletes its unread changes, Polytomic logs a warning and continues from the oldest change still available. The deleted changes are lost. You must resync the table to recover them.

Make sure the retention period comfortably exceeds the longest expected gap between syncs, including pauses and outages. To check and change it (the value is in minutes):

EXEC sys.sp_cdc_help_jobs;

EXEC sys.sp_cdc_change_job @job_type = N'cleanup', @retention = 10080; -- 7 days

Schema changes

If the schema of a table changes, its capture instance must be removed and re-created to capture the new columns.

Find the capture instance:

EXEC sys.sp_cdc_help_change_data_capture @source_schema = N'dbo', @source_name = N'TableName';

Remove the capture instance:

EXEC sys.sp_cdc_disable_table @source_schema = N'dbo', @source_name = N'TableName', @capture_instance = N'CAPTURE_INSTANCE_NAME';

Add the capture instance again:

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name = N'TableName',
    @role_name = N'polytomic_cdc',
    @supports_net_changes = 0;

You must also resync the table in Polytomic.

Polytomic detects fields enabled in the sync that aren't in the capture instance and reports them in a sync warning.

See Microsoft's sp_cdc_help_change_data_capture documentation for details.

Advanced settings

Propagating deletes

Polytomic offers two options for propagating deletes during CDC replication:

• Soft deletes
• Hard deletes

By default, Polytomic will propagate deletes from your source as soft-deletes by marking the __polytomic_deleted_at column for each destination table with a datetime reflecting deletion time.

You can change this behaviour to propagating deletes as hard deletes instead. To do so, simply check the Propagate hard deletes instead of soft deletes checkbox in the Advanced settings option at the bottom of your Bulk Sync configuration.