How to Deal with Unique Keys in SQL Server Replication Errors

Replication errors will have a key with which we should identify the missing row from the subscriber. We must then manually copy those records from Publisher and then insert into the subscriber. While doing this activity, we would have to turn off and turn on the identity column. In this article, we will learn how to deal with Unique keys in a replication.

Missing data:

Unique Keys In SQL Server Replication ErrorsConsider that you had initialized a subscription without a snapshot by using options like initialize with backup or initialize from log sequence number or replication support only. Before you change the configuration of the publication, some transactions can occur in the database and data might be changed. These changes will not get replicated. This will cause distribution agents to fail and your SQL Server error log might receive error messages to confirm that subscriber is missing some records or rows. You might also receive key violation error.

This can also happen when a user or an application deletes or adds records to the subscriber database.

Setup

The above said issue usually occurs when your replication was set up in any of following ways

  • SQL Server Replication ErrorsContinuous mode or a high frequent schedule is used for distribution agents
  • A long-running transaction has introduced a very big change in the data which in turn would develop a latency and stop the log reader agent
  • A same log reader agent is used to service multiple publications

Fix it

The first step to fix such issues is to identify IDs of commands that had failed. You should also identify transaction sequence numbers. You can easily do this using the replication monitor. If you prefer to use GUI, open the replication monitor and then navigate to the “Distributor to Subscriber History”. From the sessions of the distribution agent, select session that has errored out and then you can find the transaction sequence number from the error details section.
You can also use this script to fetch that information:

Use distribution
go
Select * from msrepl_errors
order by time desc
go

Use the transaction sequence number obtained from the previous script in this query:

Use distribution
go
Exec sp_browserreplcmds 'transaction sequence number' , 'transaction sequence number'
go

You can then continue to validate articles and identify missing records.

Turn On and Off

Now, if you manually try to push records into subscriber, you will receive an error stating that you are violating the primary key and the operation would fail. This is where the IDENTITY_INSERT comes into play. We must turn on this feature, insert records into our target table and then turn it off.

-- The statement below allows values to be inserted into the identity column 
SET IDENTITY_INSERT target_table ON
GO
-- Now SQL will allow you to insert values
INSERT INTO target_table(Col1, Col2) VALUES(anyvalye,anyvalue)
GO
--Turn of the IDENTITY_INSERT
SET IDENTITY_INSERT target_table OFF

When manually pushing records to Subscriber, please work with attention else you might end in corrupt mdf files.

Author Introduction:

Neil Varley is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including pst recovery and excel recovery software products. For more information visit www.datanumen.com

Comments are closed.