push and Pull replication

hi,

any recommendation on the diff between push and pull replication.
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Tahir QureshiSystem AnalystCommented:
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.
0
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 ?
0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
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?
0
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 ?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
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.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
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?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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 :)
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
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 ?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
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 ?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
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
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
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 ?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
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?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
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.
0
marrowyungSenior Technical architecture (Data)Author Commented:
tks all.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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?
0
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?
0
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 .
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
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 ?
0
marrowyungSenior Technical architecture (Data)Author Commented:
I can see it now, not that very clear by this site.
0
marrowyungSenior Technical architecture (Data)Author Commented:
administrator, please reopen this question for me as I want to give Victor more score.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.