Question :
Is there a general rule of thumb for when it becomes good practice to assign distribution responsibilities to a separate server that is not also a subscriber?
e.g.
“At n publishers/publications with m subscribers/subscriptions it is recommended that distribution be handled on its own instance”
or
“At j total rows replicated it is recommended …”
I assume that this would also be dependent on SQL Server version so I’ll just say that publishers are varied (versions 2000, 2000, 2008, and 2008R2), current distributor (also a subscriber) is 2008, and subscribers are also varied (2000, 2005, and aforementioned 2008).
Answer :
It totally depends on the workload and the hardware in question. Having the distributor on another server means that part (possibly all) of the buffer pool will be getting used by the distribution database and not the actual workload that the server should be doing. Then there’s the CPU and memory needed by all the agents that you are running.