Link to home
Start Free TrialLog in
Avatar of marrowyung
marrowyung

asked on

push and Pull replication

hi,

any recommendation on the diff between push and pull replication.
ASKER CERTIFIED SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of marrowyung
marrowyung

ASKER

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 ?
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.
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?
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 ?
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.
"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.
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.
"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?
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 :)
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.
"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 ?
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.
"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 ?
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.
"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
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.
User generated imageWhy 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.
"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 ?
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.
"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?
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.
""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.
tks all.
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?
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?
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 .
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.
"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 ?
I can see it now, not that very clear by this site.
administrator, please reopen this question for me as I want to give Victor more score.