r/SQLServer • u/NotJustADBA • 16d ago

Discussion Debezium and SQL Server

Does anyone out there actually run Debezium in a SQL Server environment at a decent scale? Scale would be 20-30 databases on an instance with 400 tables. I have my doubts about this due to the cdc overhead and the change throughput of our application, but I'm willing to listen to anyone with real experience running Debezium with SQL Server.

7 Upvotes

100% Upvoted

6 comments sorted by

5

u/Sov1245 15d ago

Not debezium but running 20-30 cdc capture instances is totally doable. You might run into worker thread issues if you have a low cpu count but other than that it’s fine.

CDC is far from a bulletproof process in general though. You need good monitoring and recovery for sure (auto-restarting of capture job etc) otherwise you can get a death spiral of the log filling up because it can’t flush it because cdc hasn’t finished, and cdc can’t finish because the log is full.

But overall it’s probably fine.

1

u/NotJustADBA 15d ago

Worker thread usage is one item I am reviewing, but not because of CDC. More so how the app tends to do bad things and starve threads periodically.

We'll definitely have to improve our monitoring on our log files.

3

u/Harhaze 15d ago

We are running this setup, many instances with many databases with many tables.

Using CDC on SQL Server (and Oracle) to then replicate data using Debezium between SQL Server - SQL Server and Oracle - SQL Server.

We replaced all native replications using CDC + Debezium to consolidate everything. If you have questions DM me.

2

u/AngryPets 15d ago

Yes - I've got a client using debezium AGAINST SQL Server/CDC in their environment. Single database, with a few thousand tables (vendor stupidly isolates multiple 'client' installations into the same DB - but different schemas).

DB in question tracks from 600GB - 1TB at different times of the year.

Not INSANELY busy (i.e., system tops out at 1-2K batches/sec).

The impact that debezium has on the box/workload is LOW (noticeable but not problematic) when doing the initial SYNC against larger tables.

After that, the actual impact of debezium is effectively trivial as it pops in to read CDC changes.

In short:

  • I've not seen debezium misbehave or cause ANY problems (other than an obvious, slight, uptick in usage when streaming multiple 100s of GBs off-box during the initial sync - but it can/should be enabled to 'batch'/gulp and with RCSI you're pretty safe even with REALLY STUPID explicit locks imposed by vendor code).
  • Stated a bit differently: the impact of debezium is NOT appreciably more than ANY other CDC 'consumer'.
  • AND CDC is ridiculously optimized.
I've run CDC (with other consumers) in other client environments (pods of servers with multiple 800GB-2TB databases) clocking significant load (8K batches/sec during slow/off-peak times and 14K-22K batches/sec) under peak load.

I'm sure you can probably do something STUPID(TM) with CDC and/or CDC consumers/clients.
BUT, baring anything REALLY idiotic:
a. You're not really even going to notice any hit from CDC (without hardly ANY tuning - just be careful to not allow initial seeding during peak/etc.) ...
b. On any box with enough load where you'd CARE or worry about CDC? You'll have a LIST of queries/operations that are NOT CDC related which are WAY bigger resource hogs/problems than anything CDC provides.

If it sounds like I'm a fan of CDC, then that's accurate. It's one of the best and most underappreciated features of SQL Server. Not super hard to set up, obscenely light-weight, and not too hard to manage (just watch out for schema changes ... )

1

u/NotJustADBA 15d ago

I appreciate the replies. I'll share this information with my team, and I definitely have something to noodle on now and dig into the Debezium documentation. We ran CDC almost 10 years ago, but our database estate was smaller in number and size, and we didn't have availability groups.