Understanding Peer-to-Peer Transactional Replication, Part 1

  • SQLBlimp

    SSCertifiable

    Points: 6310

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

  • Clive Strong

    SSChampion

    Points: 11127

    Interesting article. Contemplated this topic previously, but never tried to build it. Looking forward to seeing how you configured this "ugly duckling"!

  • KenpoDBA

    SSCrazy Eights

    Points: 9801

    I always thought that one of the biggest reasons for using this over MERGE was that this is the only one not in the publ/subr model. That every node both publishes and subscribes to the other nodes. This gives you a very distributed design, whereas with MERGE other subrs only receive updates and have conflict resolution with their publ, and not the other subrs.

    With MERGE, the diff subrs can have completely different data... and quite often do. You can architect MERGE to be more distributed but it's slower and you've still got the single point of failure at the publ. With PPTR you've got the ability to allow an app to connect to one of the other nodes if theirs goes down, or if you need to perform maintenance on it, because the nodes are identical. You can also segregate the data in MERGE by only sending certain rows to each subr.

    Is that not the case? Because if it is, then that seems like a more compelling difference to me than whether or not a GUID is going to grow your DB. I find it odd that with all these reasons the only thing discussed as a decision point is whether the GUIDs will grow your DB.

    Watch my free SQL Server Tutorials at:
    http://MidnightDBA.com
    Blog Author of:
    DBA Rant – http://www.MidnightDBA.com/DBARant

    Minion Maintenance is FREE:

  • SQLBlimp

    SSCertifiable

    Points: 6310

    Hi Sean,

    Thanks for the insightful comment. GUID space usage is by no means a sole decision point to choose PPTR over Merge. I completely agree that PPTR does permit, assuming that all tables accessed by the application are in the topology, the ability to use another PPTR node as a failover database. I did not mention that in Part I, but if it is OK with you, I'll include that in the preamble of Part II.

    Thanks

    John.

  • KenpoDBA

    SSCrazy Eights

    Points: 9801

    Why wouldn't it be ok with me? Yeah, go ahead.

    Watch my free SQL Server Tutorials at:
    http://MidnightDBA.com
    Blog Author of:
    DBA Rant – http://www.MidnightDBA.com/DBARant

    Minion Maintenance is FREE:

  • Julius Bichage

    SSC Journeyman

    Points: 97

    I have the following scenario:

    1. I have live databases installed for my clients

    2. I want to replicate the data from each live client into a single central database

    3. This central database has an identical schema for all the tables on live databases which I will host on our on-premise server

    4. I have added a ClientId column to each and every table on the central database to identify the clients. So essentially this ClientId plus the each tables primary key will comprise the new primary key in the central database

    I want to design a replication model which will enable me achieve the following

    1. Replicate the data from each live client to the central database in real time or close to real time

    2. Replication has to be done over HTTP to our on-premise Sql Server, so I will need some security

    3. Ensure the ClientId is inserted into the central database as part of the replication from live database.

    I have read this article and I feel this closely captures my requirement. I look forward to your second article. I am happy if you can spare time to advise how I can implement my solution as well. I have asked this question here as well http://ask.sqlservercentral.com/questions/109357/sql-server-2012-replication.html

  • Charles Kincaid

    SSChampion

    Points: 13593

    Great article. I'm about to set one of these up myself. I'll hold off and anticipate the next part.

    I have some tables that represent "Domain" data. I take it for those the best way to handle it is to set those as read only for the logins at all nodes but the main one.

    ATBCharles Kincaid

  • SQLBlimp

    SSCertifiable

    Points: 6310

    Charles Kincaid (1/13/2014)


    Great article. I'm about to set one of these up myself. I'll hold off and anticipate the next part.

    I have some tables that represent "Domain" data. I take it for those the best way to handle it is to set those as read only for the logins at all nodes but the main one.

    Generally, if your identity management is strong between nodes, you do not have to so restrict yourself. However, if you want to maintain a "virtual" transactional publication within the PPTR framework, I'd DENY INSERT,DELETE,UPDATE,MERGE to those articles to all potential users EXCEPT the one that is used by the replication agents.

    Thanks

    John.

  • SQLBlimp

    SSCertifiable

    Points: 6310

    Julius Bichage (1/13/2014)


    I have the following scenario:

    1. I have live databases installed for my clients

    2. I want to replicate the data from each live client into a single central database

    3. This central database has an identical schema for all the tables on live databases which I will host on our on-premise server

    4. I have added a ClientId column to each and every table on the central database to identify the clients. So essentially this ClientId plus the each tables primary key will comprise the new primary key in the central database

    I want to design a replication model which will enable me achieve the following

    1. Replicate the data from each live client to the central database in real time or close to real time

    2. Replication has to be done over HTTP to our on-premise Sql Server, so I will need some security

    3. Ensure the ClientId is inserted into the central database as part of the replication from live database.

    I have read this article and I feel this closely captures my requirement. I look forward to your second article. I am happy if you can spare time to advise how I can implement my solution as well. I have asked this question here as well http://ask.sqlservercentral.com/questions/109357/sql-server-2012-replication.html%5B/quote%5D

    Hi. PPTR does not sound like the solution, since every node is a publisher and subscriber to all other nodes. Thus, you would be forced to interconnect all of your clients, and the data from each one would end up everywhere.

    One-subscriber-multiple-publisher articles aren't truly within the scope of SQL Server PPTR. Merge replication might work with web synchronization. To me, provided that you can arrange the replication, I would replicate or otherwise transmit the data into your server, with a discrete article in the central database for each client, and combine that with a job to ETL that into your central database on a schedule.

    Thanks

    John.

  • Perry Whittle

    SSC Guru

    Points: 233794

    Nice article John

    -----------------------------------------------------------------------------------------------------------

    [font="Tahoma"]"Ya can't make an omelette without breaking just a few eggs"[/font] 😉

  • patrick.l.fornes

    SSC Enthusiast

    Points: 124

    Nice article, but I do have a question regarding the conflict resolution. Microsoft states " The conflict is resolved based on the originator ID value that you specified during configuration: the row that originated at the node with the highest ID wins the conflict"

    http://technet.microsoft.com/en-us/library/bb934199.aspx. That's how I have my Peer to Peer topology set up and it does appear that's the way it behaves. Would you please clarify your statement that "PPTR comes with optional conflict detection, in which each database in the topology is assigned a number, with the lowest number giving the highest priority?"

    thanks!

    Patrick

  • SQLBlimp

    SSCertifiable

    Points: 6310

    patrick.l.fornes (1/14/2014)


    Nice article, but I do have a question regarding the conflict resolution. Microsoft states " The conflict is resolved based on the originator ID value that you specified during configuration: the row that originated at the node with the highest ID wins the conflict"

    http://technet.microsoft.com/en-us/library/bb934199.aspx. That's how I have my Peer to Peer topology set up and it does appear that's the way it behaves. Would you please clarify your statement that "PPTR comes with optional conflict detection, in which each database in the topology is assigned a number, with the lowest number giving the highest priority?"

    thanks!

    Patrick

    You are correct; I have a typo. I will correct this.

    Thanks

    John.

  • scout7-500377

    SSC-Addicted

    Points: 406

    Excellent article, any time frame as to when part 2 will be available, I am just starting out trying to setup PPTR and need all the help I can get! 🙂

  • SQLBlimp

    SSCertifiable

    Points: 6310

    scout7-500377 (3/27/2014)


    Excellent article, any time frame as to when part 2 will be available, I am just starting out trying to setup PPTR and need all the help I can get! 🙂

    Hi --

    Been tied up with production work over here but I should have it in within a week or so.

    Thanks

    John.

  • scout7-500377

    SSC-Addicted

    Points: 406

    Excellent, thanks, I look forward to going through it!

Viewing 15 posts - 1 through 15 (of 21 total)

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