0

I have SQL Server Merge Replication setup on two servers and I am getting a key constraint error when the synchronization runs. The only way I can resolve this issue is to delete the record on one of the servers and then run synchronization.

Question: Is there a way to configure replication or a resolver so that the publisher record wins and the subscriber record is automatically removed when it encounters a unique or primary key violation?

Sample Table:

CREATE TABLE [dbo].[tblPeople](
  [ID] [bigint] IDENTITY(1,1) NOT NULL,
  [col1] [int] NULL,
  [col2] [int] NULL,
  [col3] [int] NULL,
  [name] [varchar](52) NULL 
CONSTRAINT [UK_keys] UNIQUE NONCLUSTERED 
(
  [col1] ASC,
  [col2] ASC,
  [col3] ASC
)

Insert on Server 1

INSERT into tblPeople (col1, col2, col3, name) values (1, 1, 1, 'Server 1 Insert')

Insert on Server 2

INSERT into tblPeople (col1, col2, col3, name) values (1, 1, 1, 'Server 2 Insert')

Trigger synchronization, which results in this conflict error and both servers having their own version of this record.

A row insert at 'SERVER1.TestDb' could not be propagated to 'SERVER2.TestDb'. This failure can be caused by a constraint violation. Violation of UNIQUE KEY constraint 'UK_keys'. Cannot insert duplicate key in object 'dbo.tblPeople'. The duplicate key value is (1, 1, 1).

Everything I read about this suggests adding a unique guid or using identity columns, which isn't a solution to this problem. The identity ranges work great and I can even create my own rowguid, but that still doesn't solve the constraint violation where I end up needing to manually delete the records.

This person asked a similar question, but I need the unique key on top of the guid and identity. Automatically resolve primary key merge conflict

Community
  • 1
  • 1
Shawn Palmer
  • 321
  • 3
  • 5

1 Answers1

0

This was resolved by setting compensate_for_errors to true on the merge article. By default SQL Server doesn't trigger a resolver when a constraint error occurs. You cannot change this setting through the user interface and must use t-sql to update it.

exec sp_changemergearticle @publication = 'PublicationName' 
, @article = 'TableName'
, @property = 'compensate_for_errors'
, @value = N'true'
, @force_invalidate_snapshot = true 
, @force_reinit_subscription = true

https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.replication.mergearticle.compensateforerrors.aspx

Shawn Palmer
  • 321
  • 3
  • 5
  • Link is dead. Now available at https://msdn.microsoft.com/en-us/library/ms153258.aspx – wes Jan 06 '17 at 16:10