Debezium with SQL Server: Real-Time CDC Setup

Change Data Capture (CDC) turns your database into an event stream. Debezium is the industry standard for this pattern, acting as a Kafka Connect source connector. This guide details how to configure SQL Server CDC and consume changes using Debezium.

How it Works

flowchart LR
    SQL[(SQL Server)] -->|Transact-Log| CDC[CDC Tables]
    CDC -->|Poll| Debezium[Debezium Connector]
    Debezium -->|JSON Avro| Kafka[Kafka Topic]
    Kafka -->|Consume| App[Microservice]
    
    style SQL fill:#E1F5FE
    style Kafka fill:#FFF3E0

Step 1: Enable CDC on SQL Server

-- Enable CDC on database
EXEC sys.sp_cdc_enable_db;

-- Enable CDC on specific table
EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'Orders',
    @role_name     = NULL,
    @supports_net_changes = 0;

Step 2: Kafka Connect Configuration

{
  "name": "sql-orders-connector",
  "config": {
    "connector.class": "io.debezium.connector.sqlserver.SqlServerConnector",
    "database.hostname": "sqlserver",
    "database.port": "1433",
    "database.user": "sa",
    "database.password": "Password!",
    "database.dbname": "OrderDB",
    "database.server.name": "fulfillment",
    "table.whitelist": "dbo.Orders",
    "history.kafka.bootstrap.servers": "kafka:9092",
    "history.kafka.topic": "dbhistory.fulfillment"
  }
}

Step 3: Event Payload Structure

Debezium sends a “before” and “after” state for every change.

{
  "before": null,
  "after": {
    "id": 101,
    "customer": "Acme Corp",
    "total": 500.00
  },
  "op": "c", // Create
  "ts_ms": 1585800000000
}

Key Takeaways

• SQL Server Agent must be running for CDC to capture changes.
• Use **topics.regex** to capture multiple tables.
• Monitor the transaction log size; CDC retains data for 3 days by default.