March 11, 2010 at 8:14 am
We are looking at perhaps setting up peer to peer replication.
Something I want to be able to test though is how long of a delay there is between a change happening on node A (the node we will be writing to with our applications) and node B which we will use for ETL and reporting work.
If the updates are fast enough, we might look at doing some writing to node B as well.
The setup we have is our web servers and database servers will be at a colo and our customer support and sales agents will be in our local offices.
When customers call in on the phone they may need something in their account changed, an order updated, etc... and we would like to be able to have those changes be made to our local node B and then quickly replicate to node A which is what the customer sees via the website.
Of course this could cause issues with latency, etc...
So:
1. How can I determine when a change has propigated
--Is there some nifty tool to show nice pretty graphs of how fast replication is happening, etc...
2. Conflict detection
---SQL 08 says that when a conflict is detected it has a tool to deal with this, but it sort of stops everything until it is resolved. Any wisdom you can share about this?
3. Bringing a downed node back into synch
---If I take node B offline to install some updates, reboot, make sure everything is looking good, then I would want to bring it back up to speed and take node A offline next. Are there any "gotchas" here to be aware of?
March 11, 2010 at 8:36 am
So:
1. How can I determine when a change has propigated
--Is there some nifty tool to show nice pretty graphs of how fast replication is happening, etc...
2. Conflict detection
---SQL 08 says that when a conflict is detected it has a tool to deal with this, but it sort of stops everything until it is resolved. Any wisdom you can share about this?
3. Bringing a downed node back into synch
---If I take node B offline to install some updates, reboot, make sure everything is looking good, then I would want to bring it back up to speed and take node A offline next. Are there any "gotchas" here to be aware of?
My question is why don't you use merge replication instead of Peer to Peer transactional replication, if you are thinking about bi directional replication? is there any specific reason?
Given your scenario i would use set up i would consider Merge replication with always publisher wins set up.
1: Replication monitor is good enough to monitor replication.
2: http://technet.microsoft.com/en-us/library/ms151752.aspx
3: There shouldn't be any problem, it can be treated as transactional replication.
March 11, 2010 at 8:46 am
I have no good reason to NOT do merge replication, I will read up on that.
Thanks!
EDIT:
OK, I recall now.
It was because I was under the impression that merge had a higher latency between updates.
So that instead of being "near real time" it was defered until they got around to a snapshot being sent out and then merged/updated into the other system.
Are those incorrect assumptions on my part?
March 11, 2010 at 9:19 am
OK, I recall now.
It was because I was under the impression that merge had a higher latency between updates.
So that instead of being "near real time" it was defered until they got around to a snapshot being sent out and then merged/updated into the other system.
Are those incorrect assumptions on my part?
Yes, it depends on the number of times updates occur before subscriber synchronize with publisher. Initial snapshot is generated one time only, which is must for all replication types.
It depends on the business requirements, Well Merge replications essentially runs a transactional replication engine to distribute the transactions, and additional logic to apply the transactions at the destination, because it has to resolve conflicts.
From BOL
Merge replication
The application requires net data change rather than access to intermediate data states. For example, if a row changes five times at a Subscriber before it synchronizes with a Publisher, the row will change only once at the Publisher to reflect the net data change (that is, the fifth value).
Peer to Peer Transactional Replication.
The application requires access to intermediate data states. For example, if a row changes five times, transactional replication allows an application to respond to each change (such as firing a trigger), not simply the net data change to the row.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply