Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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).

share|improve this question
    
You have to evaluate the workload of the distributor. There's no real way to have a "j" in a vacuum. –  Eric Higgins Aug 9 '12 at 19:06

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
    
That's my hope is to have a dedicated distributor so that my reporting server can shed the load of distribution and be completely dedicated to a reporting workload. I have 134 agents running on this reporting server (which is acting as a distributor as well). –  swasheck Aug 10 '12 at 15:30
    
Sounds like it is probably time to move the distributor to it's own machine. –  mrdenny Aug 10 '12 at 19:15

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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