Issue with constraints on the subscriber in Merge Replication

  • Article

We have configured Merge replication in SQL Server and we have the replicated constraint's name on the subscriber is different from the constraints on the publisher.  It is possible that we will have the constraints on the subscriber even after they have been deleted from the publisher. How does this happen??

 

Cause

=====

When the snapshot gets applied, the scripts are executed on the subscriber.  These snapshot scripts are created using the existing object names on the publisher. So when we apply the snapshot, the constraints are created on the subscriber with the same name as in the publisher.

 

But when we add constraints like Foreign keys on the article in publisher, without specifying the name of the constraint, then the constraint is created with an system generated number.

An example of this is below,

ALTER TABLE [dbo].[Table_Sample] WITH NOCHECK ADD FOREIGN KEY([Column_Sample])

REFERENCES [dbo].[Table_Reference] ([Coulmn_Reference])

NOT FOR REPLICATION

 

This will create an foreign key named "FK__ Table_Sample__376846BD" on the table using an randomly generated number.

 

Starting from SQL Server 2005, we can replicate the schema changes of the articles without having to reinitialize the subscriber.

When we sync after making the schema change, the script for the schema modification of the article will be sent across to the subscriber and NOT the specific constraint name. When the same command is executed on the subscriber machine as part of the replicated command, we will have an foreign key constraint created on the subscriber with a system generated number. This will be a different number than the one which is on the publisher. So we will have foreign key constraints with different names on publisher and subscriber.

 

When we delete the specific foreign key constraint on the publisher, the schema change is propagated to the subscriber.  The schema change that is being propagated is the deletion of the specific foreign key constraint on the table. But the constraint name that has been created on the subscriber is different.  So the delete command will not find the specific foreign key on the subscriber and the deletion will not affect the foreign key constraint which has been created on subscriber.

After the sync, the foreign key still existing on the subscriber although it has been deleted from the publisher. 

 

 

Resolution

========

There are 2 options that we have to resolve the issue of extra foreign key constraints on the subscriber.

  • One solution is to delete the extra foreign key constraints on the subscriber manually. This extra foreign keys are created on the subscriber only once during the sync after the schema change on the publisher.
  • The other option is to reinitialize the subscription. This will generate a new snapshot and the schema changes will be part of the snapshot thus removing the issue of the script creating foreign keys with different name.

 

As a best practice, we should always use the specific constraint name while creating the constraints and avoid creating the constraints with machine generated names.

 

Refer to the below MSDN  article for more details,

https://msdn.microsoft.com/en-us/library/ms151870.aspx

 

 

Sanjaya Padhi
SE, Microsoft SQL Server

Reviewed By Anurag Sharma, SQL Server Escalation Services