1

Q: Is less expensive/more efficient for a Distributor server to use 1 publication to 2 subscribers or to use 2 publications with the same articles going to a single subscriber each?

Details:

We have been having an issue recently with our Transactional Replication were the Distributor to Subscriber processes 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 reinitializing the publication and moving on.

We have narrowed this down to a resource utilization issue on the Distributor server. Most times we can resolve by restarting the server from Azure but the larger publications don't always recovery on their own.

All of our articles need 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 publications and send them to our separate Distributor server which accepts both Publications. One publication going to the IT server, the other going to the Data Analyst server. Something like below:Current Replication Architecture

The resource utilization issue in part is due to expensive queries on the Data Analyst server blocking the application of the replication commands. 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 Distributor to send 1 publication to 2 subscribers instead of using 2 publications going to a single subscriber each. Something like below:Potential Replication Architecture

Does anyone have any experience with this potential kind of implementation?

Improve this question

1 Answer 1

Reset to default
1

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.

Improve this answer
2
  • Thank you very much for the details. Are you aware of any resources that might shed some light on the details you are talking about? We don't currently have any issues Publisher --> Distributor but I am looking to do what I can to lighten the load on the Distributor if I am able to. That way it will be more tolerant of issues with queries causing issues from Distributor --> Subscriber.
    – Kirk Saunders
    Mar 22, 2022 at 3:20
  • @KirkSaunders No problem, I tried finding specifically where it talks about how the Distributor distributes the transactions to the Subscriber in the docs on Replication, but it wasn't exactly clear. I'll let you know if I find something. But basically, I don't think locking / blocking on a Subscriber should affect the abilities of a Distributor, since it's de-coupled from the process at that point. The transaction commands are copied to the Subscriber (as files?) and then the Subscriber applies them transactionally, I believe.
    – J.D.
    Mar 22, 2022 at 11:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.