Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SQL 2008 R2 - peer to peer replication Expand / Collapse
Author
Message
Posted Thursday, August 02, 2012 1:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 02, 2014 10:26 PM
Points: 1, Visits: 12
Hi,

We are setting up a POC of SQL replication between two indentical SQL server (including hardware).

The concurrent updates (same record updated in both the DB at same time) are failed and replication continued to fail until we fix it manually (see the details below).

It is in SQL 2008 R2 - peer to peer replication.

Is this the real nature of replication, if this is the case what is best practice to have a load balanced environment.


--- Error message --------------

Command attempted:
if @@trancount > 0 rollback tran
(Transaction sequence number: 0x0000002600000142000700000000, Command ID: 1)

Error messages:
• A conflict of type 'Update-Update' was detected at peer 1 between peer 2 (incoming), transaction id 0x0000000000000ab5 and peer 1 (on disk), transaction id 0x000000000000039e (Source: MSSQLServer, Error number: 22815)
Get help: http://help/22815
A conflict of type 'Update-Update' was detected at peer 1 between peer 2 (incoming), transaction id 0x0000000000000ab5 and peer 1 (on disk), transaction id 0x000000000000039e (Source: MSSQLServer, Error number: 22815)
Get help: http://help/22815

Thanks
Berch
Post #1338979
Posted Thursday, August 02, 2012 3:10 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 7:28 PM
Points: 119, Visits: 1,007
Never used peer to peer replication before, but found this article, hope it helps.
http://www.databasejournal.com/features/mssql/article.php/3820361/Peer-to-Peer-Replication-in-SQL-Server-2008-150-Add-a-node-and-resolve-conflict.htm


[li][/li]
Post #1339023
Posted Friday, August 03, 2012 6:18 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 17, 2014 8:20 AM
Points: 46, Visits: 217
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.

Post #1339763
Posted Monday, August 06, 2012 8:03 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, April 14, 2014 3:21 AM
Points: 2,797, Visits: 3,079
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.


Original author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2014, 2012, 2008 R2, 2008 and 2005. 25 March 2014: now over 28,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.
Post #1340635
Posted Monday, August 06, 2012 11:58 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, December 03, 2013 2:11 PM
Points: 54, Visits: 349
Use the Microsoft SYNC framework. Takes out the guesswork.
Post #1340801
Posted Monday, August 06, 2012 12:34 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, April 11, 2014 7:37 AM
Points: 2,673, Visits: 3,325
tim.cloud (8/6/2012)
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.


Thanks,

Jared
SQL Know-It-All

How to post data/code on a forum to get the best help - Jeff Moden
Post #1340826
Posted Monday, August 06, 2012 12:37 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, December 03, 2013 2:11 PM
Points: 54, Visits: 349
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.
Post #1340828
Posted Monday, August 06, 2012 12:45 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, April 11, 2014 7:37 AM
Points: 2,673, Visits: 3,325
tim.cloud (8/6/2012)
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?


Thanks,

Jared
SQL Know-It-All

How to post data/code on a forum to get the best help - Jeff Moden
Post #1340835
Posted Monday, August 06, 2012 12:48 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, December 03, 2013 2:11 PM
Points: 54, Visits: 349
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.
Post #1340837
Posted Monday, August 06, 2012 12:50 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, April 11, 2014 7:37 AM
Points: 2,673, Visits: 3,325
tim.cloud (8/6/2012)
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!


Thanks,

Jared
SQL Know-It-All

How to post data/code on a forum to get the best help - Jeff Moden
Post #1340838
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse