Real-Time Data Migration from MSSQL to PostgreSQL Using CDC and Kafka Migrating a database is never really just about the data. It's about the application sitting on top of it, and the fact that the business doesn't stop needing that application while you move its backend to a different engine. A dump-and-restore works fine for a reporting database nobody touches at 2 a.m. It does not work for an order system, a financial ledger, or anything else where a multi-hour freeze window is a business decision someone has to justify, not a technical detail to wave through. This post walks through how we built a real-time migration pipeline from SQL Server to PostgreSQL using Change Data Capture and Kafka, and the specific problems we hit along the way that weren't well documented anywhere we looked. Why CDC instead of a direct migration The conventional migration path looks like this. Freeze writes to the source, take a full snapshot, transform and load it into the target, then redirect the application. It's simple and well understood, and for low-traffic or non-critical systems it's usually fine. For a live transactional system, the freeze window is the actual problem. Depending on data volume, that window can run from minutes to hours, and for anything genuinely mission critical, an hours-long freeze is rarely something the business will sign off on. CDC sidesteps this entirely. Instead of one point-in-time snapshot, it captures every insert, update, and delete from the source database's transaction log as it happens, and streams those changes continuously to the target. PostgreSQL becomes a live, continuously updated replica of SQL Server for the whole migration window, not a one-time copy that starts drifting the moment it's taken. When cutover time comes, you're not trading downtime against data loss. You get a real cutover point with both systems in near-perfect sync and a downtime window measured in minutes. Architecture Four moving parts: SQL Server, with CDC enabled at the database and table level, as the source The Debezium SQL Server connector, reading SQL Server's CDC change tables and publishing each change as a Kafka event Apache Kafka, running in KRaft mode, as the durable, ordered backbone between source and target A PostgreSQL sink connector, consuming those Kafka topics and applying the changes to the target schema One decision worth calling out: we ran Kafka in KRaft mode instead of the older ZooKeeper-based setup. KRaft removes the separate ZooKeeper ensemble entirely, meaning fewer moving parts to operate and noticeably faster broker startup during testing. If you're standing up a new Kafka cluster for a project like this today, it's hard to justify choosing ZooKeeper mode unless you already have infrastructure reasons to. Implementation Enable CDC on the source database, at both the database and table level: sql-- Enable CDC at the database level EXEC sys.sp_cdc_enable_db; -- Enable CDC on each table you want to capture EXEC sys.sp_cdc_enable_table @source_schema = 'dbo', @source_name = 'Orders', @role_name = NULL, @supports_net_changes = 1; Deploy Kafka in KRaft mode. This requires generating a cluster ID and formatting the storage directory before the first start, a step that trips up anyone coming from a ZooKeeper-based setup where it wasn't necessary: bashKAFKA_CLUSTER_ID="$(bin/kafka-storage.sh random-uuid)" bin/kafka-storage.sh format -t $KAFKA_CLUSTER_ID -c config/kraft/server.properties bin/kafka-server-start.sh config/kraft/server.properties Configure the Debezium source connector, pointing it at SQL Server and the tables you enabled CDC on: json{ "name": "mssql-source-connector", "config": { "connector.class": "io.debezium.connector.sqlserver.SqlServerConnector", "database.hostname": "mssql-host", "database.port": "1433", "database.user": "cdc_user", "database.password": "********", "database.names": "SalesDB", "topic.prefix": "mssql", "table.include.list": "dbo.Orders,dbo.Customers", "schema.history.internal.kafka.bootstrap.servers": "kafka:9092", "schema.history.internal.kafka.topic": "schema-changes.salesdb" } } Configure the PostgreSQL sink connector to consume those topics and upsert into the target: json{ "name": "postgres-sink-connector", "config": { "connector.class": "io.confluent.connect.jdbc.JdbcSinkConnector", "connection.url": "jdbc:postgresql://pg-host:5432/salesdb", "connection.user": "pg_user", "connection.password": "********", "topics": "mssql.dbo.Orders,mssql.dbo.Customers", "insert.mode": "upsert", "pk.mode": "record_key", "auto.create": "true", "auto.evolve": "true" } } What went wrong, and what the docs didn't mention The architecture above sounds tidy on paper. In practice, nearly all of the effort went into debugging four problems, none of which were obvious from the official documentation at the time. Connector plugin paths fail silently. Kafka Connect doesn't raise a clear, actionable error when it can't find the Debezium or JDBC sink plugins. It just fails to load them, and the resulting error is vague enough to send you looking in the wrong place. Both times we hit this, the cause was plugin.path in connect-distributed.properties not matching the actual directory structure the connectors had been extracted into. Use absolute paths, and verify the directory structure directly instead of trusting the config looks right. Cluster ID mismatches after a restart. Reformat a KRaft storage directory without keeping the cluster ID consistent across every broker's config, and the cluster refuses to start with a "Cluster ID does not match" error and no further context. The fix is procedural. Keep the generated cluster ID in a shared config file every broker reads from, rather than letting it regenerate per node. Kafka defaults to localhost, and that only works until it doesn't. The default advertised.listeners setting is fine when everything runs on one machine, which makes it easy to miss until Kafka Connect or a remote producer actually needs to reach the broker. Set it explicitly to the broker's real, reachable hostname or IP, not localhost, and do it before deploying across more than one host, not after. Type mapping between SQL Server and PostgreSQL isn't automatic. SQL Server's datetime2 and money types in particular don't map cleanly onto PostgreSQL's defaults, and left unhandled, this produces silent precision loss rather than an obvious error. Explicit type mapping in the sink connector configuration caught this for us. Worth checking every column type crossing the pipeline rather than assuming the defaults are close enough. Results Running this pipeline kept the live SQL Server database and the PostgreSQL target in near-real-time sync for the entire migration window, instead of relying on a single frozen snapshot. When it came time to cut the application over, downtime was measured in minutes, not hours. We validated row counts and checksums across both databases before decommissioning the source system, which gave real confidence the sync had held throughout. The pattern itself isn't specific to SQL Server or PostgreSQL. The same CDC-plus-Kafka approach applies to any heterogeneous database migration where downtime carries a real cost and a maintenance-window approach isn't acceptable.
Running this pipeline kept the live SQL Server database and the PostgreSQL target in near-real-time sync for the entire migration window, instead of relying on a single frozen snapshot. When it came t





