Understanding Peer-to-Peer Transactional Replication, Part 2

  • SQLBlimp

    SSCertifiable

    Points: 6322

    Comments posted to this topic are about the item Understanding Peer-to-Peer Transactional Replication, Part 2

  • gregwhite83

    SSC Veteran

    Points: 233

    Great Article when implementing peer to peer replication how did you find the latencies, and how wildly do they fluctuate for you? .. I know this is a "it depends answer" but just trying to get an understanding on how this behaves in the wild.

    would love to see something similar for merge replication too as I have an interest in both these replication technologies.

  • SQLBlimp

    SSCertifiable

    Points: 6322

    gregwhite83 (11/5/2015)


    Great Article when implementing peer to peer replication how did you find the latencies, and how wildly do they fluctuate for you? .. I know this is a "it depends answer" but just trying to get an understanding on how this behaves in the wild.

    Our PPTR implementation is done for redundancy/load balancing on a web application; the machines are physically close together on the same 10GB subnet; latencies are usually negligible.

    Part III talks about issues where mass updates introduce latency issues as distribution agents "catch up" with workloads. There are artefacts that must be closely monitored and addressed.

    Thanks

    John.

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the great detailed article in the continuing series.

  • SQLBlimp

    SSCertifiable

    Points: 6322

    Iwas Bornready (11/5/2015)


    Thanks for the great detailed article in the continuing series.

    You're welcome. Glad that you like it.

  • AZJim

    Default port

    Points: 1432

    Thank you for providing this. I had to implement transactional replication without training and it wasn't pleasant. I still struggle with some aspects of it, especially the way the monitor works (not very intuitive). But I am confused about something on the difference between PPTR and Merge Replication. Both seem to be able to cross pollinate data with write capability. From what I read, even if there was a conflict (such as two nodes getting separate updates near simultaneously), there can be provisions made to resolve the conflict. So what is the practical difference?

  • SQLBlimp

    SSCertifiable

    Points: 6322

    jim.drewe (11/5/2015)


    Thank you for providing this. I had to implement transactional replication without training and it wasn't pleasant. I still struggle with some aspects of it, especially the way the monitor works (not very intuitive). But I am confused about something on the difference between PPTR and Merge Replication. Both seem to be able to cross pollinate data with write capability. From what I read, even if there was a conflict (such as two nodes getting separate updates near simultaneously), there can be provisions made to resolve the conflict. So what is the practical difference?

    Hi.

    First of all, Merge replication imposes an extra column with a GUID to control the merge. Merge is trigger-based. It uses a different sort of agent, the merge agent. Merge can be set to track column changes instead of just row changes.

    Peer-to-Peer is based on the transaction log, and is an amplification of classic transactional replication. Peer-to-peer uses a log reader agent and a distribution agent. It generates a SP and generates one call per row affected. That can add up to a lot of commands if a mass update is effected at one of the peers. Peer-to-peer is only in Enterprise Edition.

    I hope that I've answered your questions.

    Thanks

    John.

  • AZJim

    Default port

    Points: 1432

    Excellent. Thanks.

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply