Progress
0% View detailed progress dashboard

MS SQL Server CDC — Quickstart & Runbook

Enable database-level CDC, configure the SQL Server Agent, register a connector, and verify change data flow.

Prerequisites (DB)

  • SQL Server Agent must be running.
  • Database should use Full Recovery Mode.
  • User needs the db_owner fixed database role to enable CDC.
  • Tables require a stable primary key or unique index.
  • Provision disk for transaction log and CDC change tables.
-- Is SQL Server Agent running?
EXEC master.dbo.xp_servicecontrol 'QueryState', 'SQLServerAGENT';

-- Recovery model
SELECT name, recovery_model_desc
FROM sys.databases
WHERE name = 'YourDB';

DB Setup (copy/paste)

-- Enable CDC on the database
USE YourDB;
GO
EXEC sys.sp_cdc_enable_db;
GO

-- Enable CDC on a specific table
EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'customers',
    @role_name     = N'cdc_role',
    @supports_net_changes = 1;
GO

-- Grant access to the CDC user
CREATE USER cdc WITHOUT LOGIN;
GRANT SELECT ON ALL cdc.change_tables TO cdc;

Connector Config (example)

{
  "name": "mssql-cdc",
  "config": {
    "connector.class": "io.debezium.connector.sqlserver.SqlServerConnector",
    "database.hostname": "mssql",
    "database.port": "1433",
    "database.user": "sa",
    "database.password": "Password!",
    "database.dbname": "YourDB",
    "topic.prefix": "server1",
    "table.include.list": "dbo.customers",
    "database.history.kafka.bootstrap.servers": "kafka:29092",
    "database.history.kafka.topic": "schema-changes.inventory",
    "tombstones.on.delete": "false",
    "snapshot.mode": "initial"
  }
}

Verify

-- Check that CDC capture jobs are running
EXEC sys.sp_cdc_help_jobs;

-- Look at the captured changes
SELECT * FROM cdc.dbo_customers_CT;

-- Generate a change
UPDATE dbo.customers SET email = 'new.email@example.com' WHERE id = 1;
# consume from the topic (adjust name)
kafka-console-consumer --bootstrap-server localhost:9092 \
  --topic server1.dbo.customers --from-beginning --max-messages 5

Acceptance (target/sink)

  • No duplicate PKs after a connector restart.
  • Latest change reflected in the sink using __$start_lsn ordering.
-- Check for duplicates in the sink
SELECT COUNT(*) AS rows, COUNT(DISTINCT id) AS distinct_keys
FROM target_customers;

Safe Rollback / Cleanup

-- Stop the connector first!

-- Disable CDC on the table
USE YourDB;
GO
EXEC sys.sp_cdc_disable_table
    @source_schema = N'dbo',
    @source_name   = N'customers',
    @capture_instance = N'all';
GO

-- Disable CDC on the database
EXEC sys.sp_cdc_disable_db;
GO

Disabling CDC removes the change tables and cleanup jobs, so take a fresh snapshot if you need to re-enable later.