is it a good idea to replace azure sql MI replication with CDC and ADF or Azure Function?

Question

The company I work for put everything on azure and we use sql server replication to move data from one big collection db server (a azure sql MI) to our other database servers (say 20 in total).

Every day we publish around 10 million( some days will be more, say 100 million plus) new/updated data in various databases.

We can only have one publisher (my understanding), and fairly often, we see replication commands get built up, slow and our dbas will firefight to get things moving.

In the database server where all the data is collected, we enabled CDC change tracking.

I am wondering if I create 10 or 20 azure functions (c# code) to periodically pulling changes from CDC,and then these azure functions will copy the changes to our 20 database servers (say all these servers need all these data). Would it be an reasonable alternative to replication? For me, each azure function acts like a distributor, so we suddenly will have 10 or 20 distributors other than just one.

I could use azure data factory to do it, but it is way too expensive comparing to azure function in my case.

Is this a good idea or have some rather big issues?

Answer 1

Generally CDC-based solutions perform worse than Transactional Replication with a remote distributor.

Transactional Replication reads the log and writes changes to the Distribution database, which you can offload to a remote distributor.

CDC reads the logs and writes the changes back to system tables on the main database. So the resources required to write the changes and read the changes are always main database, whereas with Transactional Replication they can be offloaded to another server.