SQL Server

The SQL Server source reads the change tables of SQL Server's built-in change data capture (CDC). CDC must be enabled on the database and the table, and SQL Server Agent must run.

Prerequisites

Enable CDC

As a member of sysadmin (for the database) and db_owner (for the table):

USE shop;
EXEC sys.sp_cdc_enable_db;

EXEC sys.sp_cdc_enable_table
     @source_schema = N'dbo',
     @source_name   = N'products',
     @role_name     = NULL;

This creates the capture instance dbo_products, its change table cdc.dbo_products_CT, and the capture and cleanup jobs. With @role_name = NULL no gating role is required to read the changes; if you set a role, add the CDC user to it.

SQL Server Agent

The Agent runs the capture job that fills the change tables. When the source can see that the Agent is stopped, the task fails with:

sqlserver: SQL Server Agent is stopped, so the CDC capture job does not run; start it (Linux containers: MSSQL_AGENT_ENABLED=true)

In the official Linux container, set MSSQL_AGENT_ENABLED=true. The check reads sys.dm_server_services; it is skipped where that view does not exist (Azure SQL Database) or when the user lacks VIEW SERVER STATE.

A dedicated user

USE shop;
CREATE LOGIN cdc WITH PASSWORD = 'Change-me-123';
CREATE USER cdc FOR LOGIN cdc;
GRANT SELECT ON dbo.products TO cdc;     -- snapshot
GRANT SELECT ON SCHEMA::cdc TO cdc;      -- change tables and cdc.lsn_time_mapping
-- optional, lets the task detect a stopped Agent:
USE master; GRANT VIEW SERVER STATE TO cdc;

Connection string

sqlserver://cdc:Change-me-123@sql.internal:1433?database=shop
sqlserver://cdc:Change-me-123@sql.internal:1433?database=shop&encrypt=true&trustservercertificate=false

The database must be in the connection string (?database=) or in the task's database. Connected to master, tempdb, model or msdb, the task fails and asks for the database.

Task fields

How changes are applied

• Primary key required. The table needs a primary key, and the capture instance must capture every key column.
• Captured columns only. Only the columns captured by the capture instance are read. A column added to the table after the capture instance was created is not captured; create a new capture instance (and pass it as slot) to include it.
• Snapshot. Keyset pages of 5,000 rows.
• Changes are polled every 500 ms, up to 5,000 change rows at a time. This adds to the capture job's own latency, a few seconds by default. Inserts and updates upsert; deletes delete.
• Commit times come from cdc.lsn_time_mapping, which SQL Server records in server local time; they are reported as UTC, so lag is off by the server's UTC offset when the server does not run in UTC.

Positions and resume

Positions are lsnhex:seqvalhex: the __$start_lsn and __$seqval of the last change row delivered, 20 lowercase hex digits each, for example 0000002b000004880021:0000002b000004880020. The task resumes strictly after it.

The cleanup job removes change rows older than its retention period (3 days by default). A position older than the capture instance's minimum LSN is lost and the task resyncs automatically. A task that is idle (no changes) for longer than the retention also sees this, because its stored position stops moving while the low-water mark advances.

Troubleshooting