SQL Server CDC Source(Debezium) - Lock-free Snapshot

Hi everyone :vulcan_salute:

We’re implementing cdc on Confluent Cloud to a mission critical SQL Server so locking the database is not an option for us when the connector starts.

According to the doc there are 2 options(‘never’ and ‘exported’ ) which looks like lock-free but we can’t decide if they are appropriate for this scenario because both using replica slots.

Could you help about the best ways to connect Microsoft SQL Server CDC Source without locking the database?

Greetings from Istanbul,
Batu

@batu could you please clarify why the Snapshot mode exported may not work for your use case?

Hi @rick,
Thank you for the response.

There are two main concerns about the exported mode that I am not sure about. First of all, I did not fully understand what the replication slot is and when it was created. Secondly, i can’t estimate what are the tradeoffs when we choose export instead of initial mode, especially in the long term?

Thanks

@rick you can see my question here https://groups.google.com/g/debezium/c/c3Pa8n-wm6w/m/HQegKoGRAQAJ, according to the debezium team both methods will result in the same data, but exported will not use locks. They also plan to make it the default in future releases.
The replication slots is been created when you start the connector and is that part that keeps track in Postgres on where “debezium” is in the log, to know if any data can be removed.

To be clear I’m not related to debezium, just came across all those great questions a few weeks ago :slight_smile:

2 Likes

@shlomi thanks for the explanation, i tried something similar with a self-managed Debezium connectordebezium-doc which has parameters called snapshot.mode and snapshot.isolation.mode. When i implemented it on my test environment with snapshot.mode = "schema_only" and snapshot.isolation.mode="read_committed" it gave me an abstract solution without locking the Db. But this is self-managed in a docker-container. Due to your answers @rick, @shlomi, i tested it with snapshot=exported in Confluent Cloud full-managed connector and it actually behaved similar way as i wanted. As debezium team considering to make exported as default, it looks like i shouldn’t worry about the consistency or any possible tradeoffs that much.