Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Wednesday, 26 October 2016

How to Solve Transaction Replication Conflict Primary Key Issue with publisher and subscriber in SQL Server

How to Solve Transaction Replication Conflict Primary Key Issue with publisher and subscriber in SQL Server


Kindly see the

Source Server: USER-PC\SQL2012ENTPEDT

Version: SQL Server 2012 Enterprise Edition



Destination Server: USER-PC\SQL2012ENTPTEST

Version: SQL Server 2012 Enterprise Edition



Kindly see the jobs running in Publisher see below



Publisher table (article) info:



Subscriber info:


to see Replication monitor Right click Replication ---> choose Replication monitor as see below




it will open Replication monitor as see below


you can see there is no commands still needs to be deliver




In this demo,now am going to add one record in Publisher as follows

to apply this changes we can run job or we can run snapshot agent

So we are going to see publisher snapshot agent status




it will open snapshot agent then click Start button to run the agent


It went fine as follows



Now we need to run Log reader agent as follows




it went fine as follows


it went fine as follows



now we need to go subscribers server and start synchronization status as below


we just click start button to run the synchronization in subscriber server






now let see table info at subscriber



so it is correctly replicating when we add value in publisher

but here you can see 19772 value is not present in both publisher and subscriber


Now we are going to add one value/data 19772  in subscriber first as see below


kindly check value 19772 in subscriber as see below


Now i am going to add 19772 value in publisher data different not same as subscriber data as see below



after addition of data 19772 record you can check value in publisher as see below



Now we are going to Run snapshot agent @ publisher as see below

Method 1:


(or)




@ publisher Snaps Agent job went success as see below


as same like log agent job went success as see below



Now we are going to execute Distribution agent job as see below





When you click monitor button then you can see the error details as see below






To Resolve this we need to remove value from subscriber


after removed value from subscriber now those 19772 value applied from publisher to subscriber as see below