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.
Consider 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.
The above said issue usually occurs when your replication was set up in any of following ways
- Continuous 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
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.
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