Debezium with SQL Server: Real-Time CDC Setup

Implementing Change Data Capture with Debezium and SQL Server opens up real-time data streaming possibilities. Here’s a complete setup guide.

Prerequisites

• SQL Server 2017+ (Enterprise or Standard with CDC)
• Kafka cluster
• Kafka Connect

Enable CDC on SQL Server

-- Enable at database level
EXEC sys.sp_cdc_enable_db;

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

Debezium Connector Config

{
  "name": "sqlserver-connector",
  "config": {
    "connector.class": "io.debezium.connector.sqlserver.SqlServerConnector",
    "database.hostname": "sqlserver",
    "database.port": "1433",
    "database.user": "sa",
    "database.password": "Password123",
    "database.dbname": "MyDatabase",
    "database.server.name": "myserver",
    "table.include.list": "dbo.Orders",
    "database.history.kafka.bootstrap.servers": "kafka:9092",
    "database.history.kafka.topic": "schema-changes"
  }
}

Message Format

Each change event includes before/after state, making it perfect for downstream processing.

References

• Debezium SQL Server Connector