Transactional Replication Architecture - One Article - Multiple Subscribers
morde last edited by
Q: Is less expensive/more efficient for a
Distributorserver to use 1
subscribersor to use 2
publicationswith the same
articlesgoing to a single
We have been having an issue recently with our
Transactional Replicationwere the
Subscriberprocesses will halt an will not recovery gracefully. It is almost like some transactions get skipped or get applied out of order. Historically we have just resolved this with
publicationand moving on.
We have narrowed this down to a resource utilization issue on the
Distributorserver. Most times we can resolve by restarting the server from
Azurebut the larger
publicationsdon't always recovery on their own.
All of our
articlesneed to be delivered to two different servers (one for reporting functions owned by the IT department and another for reporting functions owned by a Data Analyst team). We will break the various articles into groups, each group getting 2
publicationsand send them to our separate
Distributorserver which accepts both
publicationgoing to the IT server, the other going to the Data Analyst server. Something like below:
The resource utilization issue in part is due to expensive queries on the Data Analyst server
blockingthe application of the
replicationcommands. We are working with that team to improve the queries but I also want to see if we can make an architecture adjustment to help.
I am looking to see if there is any reason why it would be less expensive for the
Distributorto send 1
subscribersinstead of using 2
publicationsgoing to a single
subscribereach. Something like below:
Does anyone have any experience with this potential kind of implementation?
I don't think it's should make much of a noticeable difference, but generally unless there's a need for a second Publisher on the same Article (each using different row or column filters for example), there's no reason to have two Publishers and would cause some slight extra work for the Distributor to manage two instead of one.
If my memory servers me correctly, Transactional Replication Distributors copy the commands for the transactions to replicate to the Subscriber server first, and then they're applied. So if your issue is a locking / blocking one on one of the Subscribers, then that shouldn't have anything to do with your Publisher to Distributor architecture anyway.