SQL 2008 R2 - peer to peer replication

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » SQL 2008 R2 - peer to peer replication

12 posts, Page 1 of 2

1 2 »»

SQL 2008 R2 - peer to peer replication

Rate Topic

Display Mode

Topic Options

Author

Message

Berch

Posted Thursday, August 02, 2012 1:34 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, June 14, 2013 1:12 AM
Points: 1, Visits: 10

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

DBA328

Posted Thursday, August 02, 2012 3:10 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 8:11 PM
Points: 118, Visits: 819

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

chuck.hamilton

Posted Friday, August 03, 2012 6:18 AM

Grasshopper

Group: General Forum Members
Last Login: Today @ 9:38 AM
Points: 20, Visits: 144

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

EdVassie

Posted Monday, August 06, 2012 8:03 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 5:46 AM
Points: 2,636, Visits: 2,788

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.

Author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2012, 2008 R2, 2008 and 2005. 4 June 2013: now over 24,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

tim.cloud

Posted Monday, August 06, 2012 11:58 AM

Valued Member

Group: General Forum Members
Last Login: Thursday, June 13, 2013 8:30 AM
Points: 51, Visits: 339

Use the Microsoft SYNC framework. Takes out the guesswork.

Post #1340801

SQLKnowItAll

Posted Monday, August 06, 2012 12:34 PM

SSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 9:05 PM
Points: 2,624, Visits: 3,145

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

tim.cloud

Posted Monday, August 06, 2012 12:37 PM

Valued Member

Group: General Forum Members
Last Login: Thursday, June 13, 2013 8:30 AM
Points: 51, Visits: 339

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

SQLKnowItAll

Posted Monday, August 06, 2012 12:45 PM

SSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 9:05 PM
Points: 2,624, Visits: 3,145

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

tim.cloud

Posted Monday, August 06, 2012 12:48 PM

Valued Member

Group: General Forum Members
Last Login: Thursday, June 13, 2013 8:30 AM
Points: 51, Visits: 339

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

SQLKnowItAll

Posted Monday, August 06, 2012 12:50 PM

SSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 9:05 PM
Points: 2,624, Visits: 3,145

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 »

12 posts, Page 1 of 2

1 2 »»

Permissions