Sql-server – Replication of database used by two programs

replicationsql server

I have a peer to peer transactional replication set between two identical databases, each one of the databases is being used by a different program on two different servers. Since we implemented the replication I keep getting a PK violation on a table both programs update although the insert statement doesn't enter a primary key and lets SQL server take care of it. In this case the table name is Statistics:
Violation of PRIMARY KEY constraint 'PK_Statistics'. Cannot insert duplicate key in object 'dbo.Statistic'.

My insert statement: 

INSERT INTO [StackExchange].[dbo].[Statistic]
       ([UserId]
       ,[CreateDate]
       ,[IsError]
       ,[CustomData]
       ,[ModuleId]
       ,[StatisticTypeId])
 VALUES
       (19,GETDATE(),'true','test replication',1,1)

Also, the event viewer in the servers is filled with conflicts data:

This is the frequent error I get on server 2:

A conflict of type 'Update-Delete' was detected at peer 2 between peer 1 (incoming), transaction id 0x000000000031dea3 and peer (null) (on disk), transaction id (null)

This the the same transaction error I get on server 1:

A conflict of type 'Delete-Update' was detected at peer 1 between peer 2 (incoming), transaction id 0x0000000000245950 and peer 1 (on disk), transaction id 0x000000000031dea3

What would you advise me to do in that case?

Thank you!

Best Answer

For the PK violations:

You need to ensure that each node in your Peer-to-Peer topology has an identity range island which will not conflict if inserts originate at that node. To do this you'll need to run DBCC checkident(talbename, reseed, start of range), ie. DBCC checkident(tablename, reseed, 100000) for Node 1, DBCC checkident(tablename, reseed, 200000) for Node 2, and so on..

For the Update-Delete conflicts:

You need to add a location specific identifier column to your table and extend this column to your PK. This will reduce the chances of conflicts occurring. If this is not an option, you will lose data as the change that occurred on the node with the highest Originator ID will win the conflict. This may or may not be okay for you.

Related Question