StreamSQL

The problem with asking a database what changed

Operational analytics wants low-latency propagation out of a transactional database and into whatever consumes events. The obvious implementation is polling: SELECT * FROM Customers WHERE UpdatedAt > ? on a timer. It is easy, it is wrong in a specific way, and the wrongness compounds.

Polling misses deletes outright, it cannot distinguish an UPDATE from an INSERT-then-UPDATE, it re-reads rows that did not change if your timestamp column is not reliably set, and the cost scales with table size rather than change volume. A table with a million rows and three writes a minute is a million-row scan three times a minute.

Change Data Capture sidesteps all of it by reading the transaction log instead of the table. The log already contains the before-and-after image of every committed change, in order. You do not have to be clever about detecting anything.

The path

SQL Server  ──CDC──▶  Debezium  ──▶  Kafka  ──▶  MQTT bridge  ──▶  subscribers

Debezium does the reading. It is a Kafka Connect source (io.debezium.connector.sqlserver.SqlServerConnector) configured by mssql-source-connector.json, and the two settings that matter most are snapshot.mode: initial — take a consistent picture of the table before streaming changes — and table.include.list, which is how you keep one noisy table out of the topic.

Kafka is the buffer. docker-compose.yml brings up Zookeeper and Kafka on Confluent 7.2.2, Connect running Debezium 1.9, and a Kafka UI so you can watch a topic fill up instead of trusting that it is.

kafkaHiveBroker.py is the bridge, and it is the only Python in the pipeline: pykafka on one side, paho-mqtt on the other, moving records from a Kafka topic onto an MQTT broker over TLS on port 8883.

That last hop is the interesting choice. Kafka is what the database side speaks; MQTT is what the field and device side speaks. Bridging once, in one small process, is far cheaper than asking every subscriber to run a Kafka client.

The part that eats the afternoon

SQL Server does not turn CDC on with one command, and the failure mode when you get it wrong is silence.

You have to enable it at two levels — sp_cdc_enable_db for the database, then sp_cdc_enable_table per table — and then, separately, the SQL Server Agent has to be running. Enabling CDC creates two Agent jobs, cdc.<db>_capture and cdc.<db>_cleanup, and nothing happens until the capture job actually runs. The cleanup job is not optional either; without it the CDC change tables grow forever.

The trap is that every layer above the database will report itself as healthy. The connector connects. The topic exists. The bridge subscribes. There is simply no data, because the thing that reads the transaction log is a scheduled job that is not running. The README spends four steps on this, and it is the right place to spend them.

What the compose stack does not give you

Worth being clear about: docker-compose.yml brings up Zookeeper, Kafka, Connect, the UI, the bridge and a connector-init job. It does not bring up SQL Server. The database is external, configured through DB_HOSTNAME, DB_PORT, DB_USER and DB_PASSWORD, which means the entire CDC-enablement sequence above is the part you cannot get from docker compose up. That is a reasonable split — nobody wants a database in a demo — but it should be the first thing a new reader is told, because the troubleshooting section all but assumes you already know it.

Honest limitations

pykafka is the weak link. The Kafka Python client has had no meaningful maintenance in years, and it is the component most likely to break on a dependency bump. confluent-kafka is the drop-in replacement and would be a small change to kafkaHiveBroker.py.

trustServerCertificate: true is in the connector config, which is correct for a local demo with a self-signed certificate and wrong for anything else. It should be a deployment decision rather than a committed default.

At-least-once, and unversioned. Debezium gives you ordered at-least-once delivery into Kafka, and the bridge forwards without its own offset tracking. A consumer that crashes mid-batch can see a duplicate, and there is no schema registry in this stack to catch a downstream producer that changes shape.