Understanding Peer-to-Peer Transactional Replication, Part 1

Question

Understanding Peer-to-Peer Transactional Replication, Part 1

SQLBlimp

SSCertifiable

Points: 6340
More actions
January 12, 2014 at 11:14 pm

#284440

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

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

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

Clive Strong SSChampion Points: 11222 More actions · Answer 1

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: 9811 More actions · Answer 2

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: 6340 More actions · Answer 3

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: 9811 More actions · Answer 4

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 More actions · Answer 5

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 More actions · Answer 6

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: 6340 More actions · Answer 7

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: 6340 More actions · Answer 8

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: 234013 More actions · Answer 9

Nice article John

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

"Ya can't make an omelette without breaking just a few eggs" 😉

patrick.l.fornes SSC Enthusiast Points: 124 More actions · Answer 10

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: 6340 More actions · Answer 11

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: 408 More actions · Answer 12

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: 6340 More actions · Answer 13

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: 408 More actions · Answer 14

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