SQL 2008 R2 - peer to peer replication

It's the nature of the beast (2-way replication) regardless of the RDBMS. Collisions are going to happen. IMO if you can't come up with an algorithm to decide which client wins, the best way to minimize them is with application partitioning. That is, place all clients that are likely to update the same rows, on the same server. That way the locking mechanisms will prevent transactions from stepping on each other.

Another thing to consider is that 90% of DB activity is read-only (in most cases). You might consider writing your application to do all DML on a single server and replicate to other servers that are read-only. In this scenario, SELECT's can be load balanced across all servers, while DML goes to just one. In this scenario you could use any of several methods to synchronize the read-only servers. Either transactional replication, mirroring (with snapshots) or log shipping (in standby mode). With both of these methods however, the replica will always be a few minutes behind the primary db depending on how often the agent jobs run.

P2P is inherently a multi-master system. If you update the same row in multiple peers at the same time you will get an update conflict.

SQL 2008 R2 allows automatic update conflict resolution, by saying the server with the lowest name in the collation sequence wins. You can also get automatic logging of the data that has been lost, so you can decide what to do about it.

We are in the final stages of going live with a P2P environment running within AWS. For us the main benefit of P2P is fast and relatively simple failover.

We have decided to route all our updates to a single node, so that we never get update conflicts. For us this is easy, as all updates are done from our web applications and all web applications use a common connection string to identify the server that hosts the updates. We use a DNS vanity name to identify our 'master write server', so if we have to move updates to another node then all we have to change is the IP address referenced by the vanity name.

Use the Microsoft SYNC framework. Takes out the guesswork.

o you have a sample article or any information about putting this together? Since they have deprecated Transactional Replication with Updatable Subscriptions in 2012, this will be a possible route for me.

I used this tutorial from Microsoft:

http://msdn.microsoft.com/en-us/library/ff928700.aspx

It actually works very well, and the only object type that is not supported is FileStream.

Interesting. Though it seems to be a lot of extra work to add a simple "article." Am I understanding that correctly? I would have to go back and change the C# code every time I add a new article?

Yes, that is the only drawback I could find.

However, I can tell you first hand that it does work, and it's a pretty flexible solution other than having to maintain the C# code.

Thanks for sharing!

Even if you use Sync Framework to do P2P data propogation, this does not overcome having to deal with update conflicts.

Your design either needs to accept that conflicts will occurr and include a process for dealing with them, or eliminate the possibility of a conflict ocurring.

Imagine a P2P system dealing with your personal bank account. You make a deposit of 1000 in a bank while buying an event ticket for 50 on your mobe. The two transactions are processed by different peer systems and the updates conflict. Most conflict resolution processes end up deciding one of the updates will be lost. Do you want to loose your deposit, or loose the event ticket?

Neither. I want the transactions serialized. That's why I always favor eliminating the possibility of conflicts by application partitioning, DML segregation, or both.