push and Pull replication

marrowyung
marrowyung used Ask the Experts™
on
hi,

any recommendation on the diff between push and pull replication.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
IT Engineer
Distinguished Expert 2017
Commented:
Do you want us to recommend the use of Push or Pull Subscriptions or do you want to know the difference between them?

The difference is that push subscriptions runs the Distribution Agent on the Publisher and in pull subscriptions the Distribution Agent runs on the Subscriber or Subscribers if more than one.
The choice between one vs another it's all to have with performance.
Tahir QureshiSystem Analyst
Commented:
also

The log reader agent always runs on the distributor, the distribution agent will run on the subscriber for pull and the distributor for push.

With a large number of subscribers - normally over 10 subscribers - you should use pull as the performance hit of running all the agents on the distributor can be costly - depending on your workload.

The real advantage for pull is the it performs better than push over a WAN.
marrowyungSenior Technical architecture (Data)

Author

Commented:
Victor:

"Do you want us to recommend the use of Push or Pull Subscriptions or do you want to know the difference between them?
"

difference between them.

"The difference is that push subscriptions runs the Distribution Agent on the Publisher and in pull subscriptions the Distribution Agent runs on the Subscriber or Subscribers if more than one. "


good and tks.

in my previous company, the other DBA said push replication is good between very close SQL server. e.g. on the same building, or can easily setup Giga net between them.

pull is for subscriber location in DR site, far aways from publisher and distributor .

do you agree that ?


Tahir,


"The real advantage for pull is the it performs better than push over a WAN."

I think I am right, pull is good for SQL server far away from publisher and distributor and push is for closer one which can connect using local LAN ?


however, any MS SQL online document is about that ?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
pull is for subscriber location in DR site, far aways from publisher and distributor .

 do you agree that ?
Not really. They are using the same network so the performance should be the same. If you're talking about the CPU and Memory then it should make some difference in choosing between the server that has more resources to act as the distributor.

When it should really makes difference is when you have more than one subscriber (I don't know where than number of 10 came from) especially if they are all in different network zones (so not necessarily a WAN). Is this kind of solution, yes, I would recommend a PULL subscription so you can let each subscriber to manage their own distributor agent.
marrowyungSenior Technical architecture (Data)

Author

Commented:
victor:

"in pull subscriptions the Distribution Agent runs on the Subscriber or Subscribers if more than one. "

ok, for pull, distribution agent location on subscriber(s)

why is it? any good thing if architecture in this way ?

"Not really. They are using the same network so the performance should be the same."

what if it is not in the same network, e.g. a subscriber in DR site. another subscribers on local LAN, e.g. just upstairs , what should I do then ?

"If you're talking about the CPU and Memory then it should make some difference in choosing between the server that has more resources to act as the distributor."

you mean distributor always the busiest one ?

"If you're talking about the CPU and Memory then it should make some difference in choosing between the server that has more resources to act as the distributor"

sorry what is the point of it? too much subscriber make the distributor busy and for push replication, but you said: 'push subscriptions runs the Distribution Agent on the Publisher', so it should be the publisher busy but not distributor, right?
marrowyungSenior Technical architecture (Data)

Author

Commented:
should I said if Distributor and publisher is separated and distributor has a lot of CPU and RAM, then we should use push as distributor can do better job now ?

if distributor do not have much CPU and RAM resource, then simply us pull instead ?
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
why is it? any good thing if architecture in this way ?
It's a way to balance the load so you can always choose in which SQL Server instance you want to run the distribution agent.

what if it is not in the same network, e.g. a subscriber in DR site. another subscribers on local LAN, e.g. just upstairs , what should I do then ?
Tests. This is the first thing you should do. Test and see the differences.

you mean distributor always the busiest one ?
Yes. Hope that now you can understand the answer from your first question in this comment.

you said: 'push subscriptions runs the Distribution Agent on the Publisher', so it should be the publisher busy but not distributor, right?
There are 3 identities in a Replication:
  1. Publisher
  2. Subscriber
  3. Distributor
The distributor will be in the Publisher or Subscriber side depending if it is a Push or a Pull subscription, respectively.
marrowyungSenior Technical architecture (Data)

Author

Commented:
"It's a way to balance the load so you can always choose in which SQL Server instance you want to run the distribution agent.

hi, but people might not know this diff. right? hard to know

"Yes. Hope that now you can understand the answer from your first question in this comment."

you mean this by you?

"The difference is that push subscriptions runs the Distribution Agent on the Publisher and in pull subscriptions the Distribution Agent runs on the Subscriber or Subscribers if more than one. "

"The distributor will be in the Publisher or Subscriber side depending if it is a Push or a Pull subscription, respectively."

I am sorry about that I don't understand what you mean.  this seems to me that we don't need distributor at all, as the agent only in publisher or subscriber, distributor is not in this picture.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
hi, but people might not know this diff. right? hard to know
You need to know the systems that you're managing, right? Otherwise, yes, it will be very hard to know.

this seems to me that we don't need distributor at all, as the agent only in publisher or subscriber, distributor is not in this picture
With this comment, I think you still do not know what's the distributor's for. Hope that this MSDN article can explain it better than me.
marrowyungSenior Technical architecture (Data)

Author

Commented:
"Hope that this MSDN article can explain it better than me."

I think you want me to read this:

 The Distributor is a server that contains the distribution database and stores metadata and history data for all types of replication. The Distributor also stores transactions for transactional replication.

  " stores metadata and history data"

  I am sorry that at this moment this is a bit big for me and so the diff between push and pull is, as you said:

It's a way to balance the load so you can always choose in which SQL Server instance you want to run the distribution agent.

so this means if we have more and more subscribers, pull is better as the distributor agent on subscriber, we don't want all distributor agent all on publisher as it will be high loading for publisher.

it is just about distribution of load instead of it is subscriber far ways or short distance, right?
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
so this means if we have more and more subscribers, pull is better as the distributor agent on subscriber, we don't want all distributor agent all on publisher as it will be high loading for publisher.

 it is just about distribution of load instead of it is subscriber far ways or short distance, right?
Yes. I think finally you got it :)
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Also, there's an important statement in that article:
"The server you select as the Distributor should have adequate disk space and processor power to support replication and any other activities on that server. "
So, more subscribers, more space and processor time will be needed.
marrowyungSenior Technical architecture (Data)

Author

Commented:
"Yes. I think finally you got it :)"

I am not that dump yet my teacher, but just your answer is diff from what I know.

"So, more subscribers, more space and processor time will be needed."

on distributor itself only ? as publisher should only push once but distributor has to coordinate the rest of data transfer ?
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
on distributor itself only ? as publisher should only push once but distributor has to coordinate the rest of data transfer ?
Mind that both can share the same machine, and that is what this is all about. You need to decide between having a single distributor in the Publisher server (Push subscription) or have a distributor in each Subscriber server (Pull subscription). The first will require more resources and the latest not because each the load will be distributed for each Subscriber. Now it mostly depends on how much resource the Publisher has and how much resource has the Subscribers.
marrowyungSenior Technical architecture (Data)

Author

Commented:
"Mind that both can share the same machine, and that is what this is all about."


this is what I experienced before, that publisher, as per additional replication is setup, use huge another amount of resource. I heard there are no caculation for this but as per replication setup , 32GB additional of RAM need to add to that box, agree?

" You need to decide between having a single distributor in the Publisher server (Push subscription) or have a distributor in each Subscriber server (Pull subscription). The first will require more resources and the latest not because each the load will be distributed for each Subscriber."

then we go back to the same conclusion, when there are lot of subscribers, we should use as distributor agent will be on subscriber.

"Now it mostly depends on how much resource the Publisher has and how much resource has the Subscribers."


is this means we decide pull or push based on the existing hardware we have ?

if focus on which one is faster, resource can be add and it is open and welcome to propose now, then it is just money, ok give you money, which one should we use ?
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
I heard there are no caculation for this but as per replication setup , 32GB additional of RAM need to add to that box, agree?
No. I have had many replications running without performance issues in systems with 8GB RAM only.

then we go back to the same conclusion, when there are lot of subscribers, we should use as distributor agent will be on subscriber.
Yes but there's no "good" number for the subscribers. When do you know when to move from a Push to a Pull subscription? 2, 4, 8, 10, 12? In my opinion this my vary based on the available resources for the Publisher and Subscriber, so please, don't ask me for a correct number since there is none. It really depends and you might to perform some tests to get the best for your environment.

is this means we decide pull or push based on the existing hardware we have ?
Please refer to my above comment.
marrowyungSenior Technical architecture (Data)

Author

Commented:
"No. I have had many replications running without performance issues in systems with 8GB RAM only. "

subscribers or publisher you are talking about ?

so for an additional replication added, no need to add more RAM and tempdb .ndf file  ?

"Yes but there's no "good" number for the subscribers. When do you know when to move from a Push to a Pull subscription? 2, 4, 8, 10, 12?"

yes, this is a question.

"2, 4, 8, 10, 12? In my opinion this my vary based on the available resources for the Publisher and Subscriber"

so any monitoring metric for me to ping pong that out ? I can make use of the the monitoring tools
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
subscribers or publisher you are talking about ?
They are part of a Replication solution, so yes.

so for an additional replication added, no need to add more RAM and tempdb .ndf file  ?
You need to test it first. You can't expect we tell you that you'll need or not. We don't know your environment. Usually we go with what we have and if we see performance issues them we might add more memory, cpu or storage.

so any monitoring metric for me to ping pong that out ? I can make use of the the monitoring tools
There's a Replication Monitor in SSMS.
Replication-menu.PNGWhy are you always wanting to go for a 3rd party tool? In 20 years using SQL Server I never needed a 3rd party tool to get the information that I want. Between Windows tools and SSMS I usually have everything that I need.
marrowyungSenior Technical architecture (Data)

Author

Commented:
"Why are you always wanting to go for a 3rd party tool? I"

what the performance monitor giving us is we have to check it out MANUALLY and it can be a big trouble if we can't start to see there are problem/potential problem before something even bigger happen.

monitorings tool will send me email about all evident before the disaster happen, agree ?

"Between Windows tools and SSMS I usually have everything that I need."

you mean you just use these ?
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
what the performance monitor giving us is we have to check it out MANUALLY and it can be a big trouble if we can't start to see there are problem/potential problem before something even bigger happen.
Welcome to DBAs daily life :)

you mean you just use these ?
Yes. But we also work with other teams (Applications, Windows, Storage, Network and Monitoring) and each of those teams check problems on their side and can warn us if something happens and vice-versa. We don't need to step into each other areas of expertise.
marrowyungSenior Technical architecture (Data)

Author

Commented:
"Welcome to DBAs daily life :)"

nono, need to use a much robust tools to check and know before it happen, right?

"each of those teams check problems on their side and can warn us if something happens and vice-versa.

then how can they know it in advance ? using monitoring tools, right?
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Man, there's no miracles or witches. You're asking something like "I want to know when and where will happen the next earthquake or avalanche". You need to read the signs and speculate a little bit.
How's the network? It shows signs of underperformance?
The same for CPU, Memory, Storage, ...

That's what a DBA or a System Administrator does. You just need to gain experience to learn how to read those kind of signs.
Of course, sometimes there are no signs and sh*t just happens. When something like that exists, we all can say goodbye from our jobs.
marrowyungSenior Technical architecture (Data)

Author

Commented:
""I want to know when and where will happen the next earthquake or avalanche"

good example,

"Of course, sometimes there are no signs and sh*t just happens. When something like that exists, we all can say goodbye from our jobs."

that's why I said we need a tools to see it in advance and then we can at least buy some time to handle this case .

 "You need to read the signs and speculate a little bit.

this is what I am doing to read the log , etc. but speed can be very slow.

here boss ask us to tell the conclusion in one afternoon, not enough time usual;y.
marrowyungSenior Technical architecture (Data)

Author

Commented:
tks all.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
I'm usually the only one that follow up your questions but then ending up to have the same points as others than only make a single comment. Why's that?
marrowyungSenior Technical architecture (Data)

Author

Commented:
wait wait, I just select you win the solution and the other one is assist, I don't select your credit at all, is there something wrong ?

I don't see I can give you specific point, how?
marrowyungSenior Technical architecture (Data)

Author

Commented:
I am not try to close anohter ticket now, how can I give you the credit I want.. it is not the same as before, we can only select the best and assistant .
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
I don't see I can give you specific point, how?
By default the system will equality split the points but you always have the chance to edit the points before submitting the closure.
Isn't the first time that you do this to me and that's why I called your attention for it.
Anyway you can always request an attention for the question so mods can help you closing a question properly.
marrowyungSenior Technical architecture (Data)

Author

Commented:
"Isn't the first time that you do this to me and that's why I called your attention for it."

yes I will. sorry about that as I did know you and you always my best teacher.

" chance to edit the points before submitting the closure."

any screenshot on how can I edit the point ?
marrowyungSenior Technical architecture (Data)

Author

Commented:
I can see it now, not that very clear by this site.
marrowyungSenior Technical architecture (Data)

Author

Commented:
administrator, please reopen this question for me as I want to give Victor more score.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial