SQL Server Peer-to-Peer Replication – When a Conflict is Detected

Yesterday we hit the dreaded conflict in our production/DR SQL peer-to-peer replication setup.  My first reaction is usually “oh <expletive>, we’re going to need to rebuild replication”, but we happened to have the best possible scenario where we had an update-update conflict.

I’d like to note that I personally hate working with SQL Server peer-to-peer replication (we’re running SQL 2008 R2 version), and it looks like some of the limitations of the solution are still an issue in SQL 2012.

  • We have to quiesce (i.e. stop the application) in order to add new articles (tables, views etc), to the replication topology.  Big pain + off hours work.
  • Plus, unlike with MySQL replication when setup in multi-master, I can’t just not log my correction statements so they don’t replicate back.
  • This is why I’m excited for the Always On Availability groups in SQL 2012/2014, with a multi-subnet setup.  I’m still evaluating our environment options but this helps address some of the limitations in our peer-to-peer replication environment.

Environment:

  • 2 node setup with peer-to-peer replication, across two different domains. (I’m planning on documenting how this was setup in the future, testing with 2012)
  • Both servers running SQL 2008 R2 Enterprise & Windows 2008 R2 Enterprise

Resolution:

Microsoft has the tasks outlined for reference: Conflict Detection in Peer-to-Peer Replication

Note that the recommended approach is to reinitialize the slave – this requires downtime of the application in order to get a quiesced system.

In our case, we knew our conflict was most likely due to bringing up the same data in both sites as part of our DR setup work.  The application does an update of the data, which caused this conflict to occur.  This requires knowledge of the data to determine if the conflicts can be accepted.

Step Task Details
1 Notification received NOTE: If using Peer-to-peer replication, make sure that alerts have been enabled.See  How to: Configure Predefined Replication Alerts (SQL Server Management Studio) for details.

Database Mail will need to be configured.For example, this was the alert we were receiving, similar to both Master & Slave:

DESCRIPTION:A conflict of type 'Update-Update' was detected at peer 1 between peer 2 (incoming), transaction id 
0x000000000ce6b386  and peer 1 (on disk), transaction id 0x000000000dcad30d
2 Allow peer to peer continue on conflict Now that we have been notified of this conflict, on my DR site I wanted to continue replication on conflict so I could identify my errors.p2p_continue_onconflict needs to be set to true

DECLARE @publication AS sysname
SET @publication = N'AdventureWorks_PUB2'
USE [AdventureWorks]
EXEC sp_changepublication
  @publication = @publication,
  @property = N'p2p_continue_onconflict',
  @value = true
GO
3 Restart the Distribution Agent
  1. Launch Replication Monitor on problem node (Starting with DR here)
  2. Navigate to Failing subscription
    1. On All Subscriptions tab for that subscription, right click and choose “Stop Synchronizing”
    2. Right click and choose refresh to check stopped – this may take a minute or two
    3. Right click and choose “Start Synchronizing”
4 View the conflicts See View Conflicts for details – How to: Validate Data at the Subscriber (SQL Server Management Studio)

  1. Connect to the server where the conflict occurred (this is DR in our case)
  2. Expand the Replication folder, and then expand the Local Publications folder.
    1. Right-click the problem publication,  choose View Conflicts from dropdown
    2. In the Select Conflict Table dialog box, select a database, publication, and table for which to view conflicts.
  3. Once in the Replication Conflict Viewer, you can review the conflicts
    1. When you click on each conflict, you will see the conflict winners & losers and need to determine what will need to be fixed.
  4. To Review:
    1. With Reason Resolved- a winner was chosen by SQL Server (update-update) – best -case scenario
      1. Manually compare the records, you may need to select the current data within the database
      2. Note – the peer with the higher originator ID will most likely chosen as the winner
      3. Example:
        • CEDR-ConflictsView
    2. With Reason Unresolved – SQL Server was not able to address the conflict.
      1. This causes a non-convergence situation, requiring restore
  5. Once reviewed, you can remove the conflicts from the table.
    1. Choose “Select All” to choose all rows
    2. Check the “Log the details of this conflict” option, and make sure you have a valid, writable directory specified for the file location (View –> Options)
    3. Click Remove
5 Disable continue on conflict detection check p2p_continue_onconflict needs to be set to FALSE, so we will be notified of future conflicts.

DECLARE @publication AS sysname
SET @publication = N’AdventureWorks_PUB2′
USE [AdventureWorks]
EXEC sp_changepublication
@publication = @publication,
@property = N’p2p_continue_onconflict’,
@value = false
GO

 

6 Repeat on Primary node If Resolved conflict was found (step 4 part 4), then repeat steps 2-5 on the Primary node
7 Run Validation Check Run the validation check to see if there are any differences between the Primary & Secondary nodes
This can be run through the Replication Monitor for the publication, or as T-SQL:

— Validate Publication
DECLARE @publication AS sysname;
DECLARE @newobject AS sysname;
SET @publication = N’AdventureWorks_PUB’;

USE AdventureWorks

EXEC sp_publication_validation
@publication = @publication,
@rowcount_only=2,
@full_or_fast=2;
GO

Once finished (depends on the time of your system, this seems to run in background – this is about 10 minutes on my 90 GB system), you can select to look for differences.  Note that if you have timestamp datatypes, you will not get a consistent checksum on your validation checks.

select * from msdb..sysreplicationalerts
where error_id != 0 order by time desc;

One thought on “SQL Server Peer-to-Peer Replication – When a Conflict is Detected

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s