Push vs Pull Subscription
Published: 3rd May 2013
When Subscribing to a Publication you will need to decide whether you want to use a Push or a Pull subscription. The difference may not sound much on paper, but there are some key points that really need to be noted before you make your choice.

What is the Difference between Push and Pull Subscriptions?



Basically it’s nothing more than location of the SQL Server Agent job. If you have a Pull subscription then the job resides on the Subscriber. This means that each time the job runs, it connects to the Distributor, asks if there are any changes it needs to receive, and “pulls” them down the network.

Whereas in a Push subscription the scheduled job resides on the Distributor and therefore whenever it runs it simply “pushes” the changes down the line to the Subscriber.

Considerations



When it comes to choosing which to use, it’s very much an “it depends” situation but there are some factors to consider in respect to your environment.

All you have to do is remember that this comes down to the location of your replication SQL Server Agent job. As such it’s down to knowing which server will have the most load and which has the most capacity. For example, if you run your Publisher and Distributor on the same server and they are under very heavy load then it would be wise to not add additional jobs to that server, hence a Pull subscription. But if you are in a situation in which you have a dedicated Distributor, or even a Distributor/Publisher which is very quiet and yet your Subscribers are serving IO heavy website queries, then it would be more advisable to have your jobs on the quieter server, hence Push.

Another consideration is with regard to Merge… if you are in a situation in which your Subscriber is not present on the network for long periods, then for ease of use you would want the job running on the Subscriber.

Notes



For reference, if you want an easy way to know whether your existing subscriptions, simply look in the “Local Publications” folder on your Publisher and expand the Publication in question. This will give you the answer straight away…

Find a Publication and expand it (I have removed the names of my other servers for security, hence this picture looks a little feeble)…

If you look closely you can see that 2 of the subscription icons are faded, but the top one is brighter. That’s your answer. The brighter icon tells me it’s a Push subscription (running locally on the Distributor), and the faded ones mean they are Pull subscriptions running on the Subscriber. Simple.
Comments:
Bernard Kiss
21/03/2017 15:41:00
Question about push/pull. I'm running MS SQL Server 2014 Ent. and I'm doing peer-to-peer with it. It's set up with pull, can it be switched to push?

Thanks
NB: Comments will only appear once they have been moderated.