As I mentioned already on How to create new Subscription? you can choose between push or pull subscription. There are some benefits but also some drawbacks for both solutions.
The main difference between push and pull is the location of a distribution agent (actual SQL Job). In Push distribution agent is located on the Distributor and there you will be able to find it as a local SQL job (Category: REPL-Distribution). With Pull the job will be located on a Subscriber. Here are pros and cons of both.
- Faster when going over WAN. In this case agent is “downloading” the data instead of sending it. You may need to consider this if data is going over to the other side of the world.
- The load is moved from Distributor to Subscriber. The agent will be using subscriber resources. Could be important for you if you have multiple agents coming from one Distributor.
- Assuming you have multiple distribution agents to maintain it will get harder to maintain these as SQL jobs will be located on Subscriber. The big disadvantage for me is that you can’t stop/start agent while being on Distributor. Again if you have multiple distributions agents to maintain it is much better if you can do it from central location which is Distributor. If you logged on Distributor and have Replication Monitor open you wont be able to stop/start such distribution agent (screenshot below). Also job_id in msdistribution_Agents in Distribution db is different than job id on subscriber when using pull, which is bit harder to map SQL job to actual distribution agent.
- If Subscriber is really busy and under pressure already it won’t be good to get more load by adding Distribution agent. However one distribution agent wont make a big difference. That could be different story if we have multiple agents running on that given subscriber.
- In some cases it could be a security issue. Especially when we have subscriber located on some 3rd party/remote location etc. In that case you will need to open port 1433 for subscriber to your distributor. Distribution agent need access to Distributor.
- Everything can be managed from one location. It is much easier to apply changes to distribution agents, monitor and stop/start agents in bulk.
- Slower than Pull when going over WAN.
- If there are multiple agents going trough one Distributor you may need to consider separate box just for remote Distributor (that means additional costs).
What should you use? As always… IT DEPENDS:). Depends:
- how many distribution agents you have
- how fast is connection to your Subscribers
- what are security policies in your company
- how you prefer to manage your replication environment
The choice is yours:) You just need to be aware of some limitations of both solutions