Violation of PRIMARY KEY constraint during Merge Replication

Question

I have two identical SQL Server 2005 databases (MYDB_Pub, MYDB_Sub1) where the merge replication was configured and it works fine.

Recently we upgraded to SQL Server 2014. To test the replication functionality on the new SQL Server I followed the below steps:

• Backup the MYDB_Pub on the SQL Server 2005.
• Restore the MYDB_Pub on the SQL Server 2014 with the same name.
• Restore the same MYDB_Pub on the SQL Server 2014 with name 'MYDB_Sub1'.
• Configure Merge Replication on a single table 'Country'.

Now when I run the replication process, I get the following error message:

The table structure is given below:

CREATE TABLE [dbo].[Country](
    [CountryId] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [Code] [char](2) NOT NULL,  
    [CountryName] [varchar](50) NOT NULL,   
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL CONSTRAINT [DF_Country_rowguid]  DEFAULT (newsequentialid()) 
)
GO
ALTER TABLE [dbo].[Country]  WITH NOCHECK ADD  CONSTRAINT [repl_identity_range_BEB70305_9154_4BBE_B898_61681A047BA2] CHECK NOT FOR REPLICATION (([CountryId]>(112251) AND [CountryId]<=(113251) OR [CountryId]>(113251) AND [CountryId]<=(114251)))
GO
ALTER TABLE [dbo].[Country] CHECK CONSTRAINT [repl_identity_range_BEB70305_9154_4BBE_B898_61681A047BA2]
GO

Please note that [rowguid] column was already there in the database before defining the replication. Country table in both publisher and subscriber have similar data as publisher and subscriber were restored from same file.

To configure the replication I used the instruction provided in this article.

Subscription properties and Article properties of Country table are shown in the below screenshots

I have tried to find the solution for this error but nothing helped. I must make it clear that I am not a DBA and its the first time I am playing with replication on SQL Server. I would really appreciate any help.

Answer 1

The reason you are getting the error is because the table already exists on the subscriber, and is populated, and the initialization is trying to re-populate the table with the data from the publisher. It is doing this because your article property says "Action if name is in use = Keep existing object unchanged". I believe the default is "drop table and recreate object" or something like that, which means when you synchronize the initialization, it will drop the table at the subscriber, then populate it. This would avoid the error above, since the table at the subscriber will be empty before it is populated.

So you need to decide, if you need the existing data at the subscriber, or if during initialization, you can let merge replication drop the objects, and repopulate it based off the snapshot from the publisher.