SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL 2008 R2 - peer to peer replication


SQL 2008 R2 - peer to peer replication

Author
Message
Berch
Berch
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 13
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
DBA328
DBA328
Old Hand
Old Hand (308 reputation)Old Hand (308 reputation)Old Hand (308 reputation)Old Hand (308 reputation)Old Hand (308 reputation)Old Hand (308 reputation)Old Hand (308 reputation)Old Hand (308 reputation)

Group: General Forum Members
Points: 308 Visits: 1123
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]
chuck.hamilton
chuck.hamilton
SSC-Enthusiastic
SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)

Group: General Forum Members
Points: 156 Visits: 395
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.
EdVassie
EdVassie
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5806 Visits: 3866
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 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005. 14 Mar 2017: now over 40,000 downloads.Disclaimer: All information provided is a personal opinion that may not match reality.Quote: When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist. - Archbishop Hélder Câmara
tim.cloud
tim.cloud
SSC Journeyman
SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)

Group: General Forum Members
Points: 96 Visits: 349
Use the Microsoft SYNC framework. Takes out the guesswork.
Jared Karney
Jared Karney
SSCarpal Tunnel
SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)

Group: General Forum Members
Points: 4986 Visits: 3694
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
PFE - Microsoft
SQL Know-It-All
How to post data/code on a forum to get the best help - Jeff Moden
tim.cloud
tim.cloud
SSC Journeyman
SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)

Group: General Forum Members
Points: 96 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.
Jared Karney
Jared Karney
SSCarpal Tunnel
SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)

Group: General Forum Members
Points: 4986 Visits: 3694
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
PFE - Microsoft
SQL Know-It-All
How to post data/code on a forum to get the best help - Jeff Moden
tim.cloud
tim.cloud
SSC Journeyman
SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)

Group: General Forum Members
Points: 96 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.
Jared Karney
Jared Karney
SSCarpal Tunnel
SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)

Group: General Forum Members
Points: 4986 Visits: 3694
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
PFE - Microsoft
SQL Know-It-All
How to post data/code on a forum to get the best help - Jeff Moden
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search