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
We have been having an issue recently with our
Transactional Replication were the
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
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
publication going 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
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:
Does anyone have any experience with this potential kind of implementation?