This article will review specific SQL Server merge replication issues related to foreign keys and schema snapshots
Problem
We have merge replication configured on a database to replicate data to different subscribers. While applying the initial snapshot to subscribers the merge agent fails due to foreign keys on non-replicating tables. In this article we’ll see how we solved this using pre and post snapshot scripts.
Solution
There are few tables which are added to merge replication. These tables have primary key and are being referenced by foreign keys which were created on non-replicating tables. Now while applying the initial snapshot on subscribers, the merge agent tries to drop and re-create the table which throws error “The schema script could not be propagated to the subscriber. Could not drop object ‘dbo.tablename’ because it is referenced by a FOREIGN KEY constraint”.
To illustrate this, I will create a table “PARENT” with primary key on both publisher and subscriber.
|
1 |
CREATE TABLE PARENT (ID INT PRIMARY KEY, NAME VARCHAR (50)) |
Create a table with foreign key which refers to the “PARENT” table created above on both publisher and subscriber.
|
1 2 |
CREATE TABLE CHILD (ID INT, ADDRESS1 VARCHAR (50) CONSTRAINT FK_CHILD FOREIGN KEY (ID) REFERENCES PARENT(ID)) |
Now I will create a merge publication and add the table “PARENT” to publication using SQL server management studio.
Merge replication:
Login to SQL Server from SQL Server management studio. Assuming the distribution is already configured navigate to replication folder -> Local publications -> Right click on new publication. Please refer to the below image.
Click on Next– > select the database where you created “PARENT” table. Click Next and select merge replication.
Click Next and check 2008 or later -> add table “PARENT” and click on article properties -> set properties of all table articles -> select drop existing object and create new one as shown below images.
Click Next– > Next-> Add filter in case if you want to send specific data to each subscriber or you can skip this step and click Next-> check create snapshot immediately to create snapshot on creation of publication and uncheck schedule the snapshot agent to run at following times.
Click Next and add the credentials you want to use for snapshot agent and connecting publisher -> Next -> Next -> and input publication name and finish.
Right click on the publication you just created and click on Launch replication monitor, click on the publication in replication monitor and click on the Agents tab as shown in below image and make sure the snapshot is completed.
Once the snapshot is completed we need to add the subscriber to publication we just created.
Adding a subscriber
Navigate to publication you just created, right click on the publication and click on New subscription -> Next -> Next -> select the type of subscription you want (Pull or push) as per your need -> Next-> add the subscriber server and select the subscriber database. Add the logins to run merge agent, connect the publisher, distributor and the subscriber -> Next -> select agent schedule as per your need. In my case I selected run continuously -> Initialize immediately -> select subscription type as per your need -> check create subscriber and click Next -> Finish.
Now the merge agent runs and applies initial snapshot. While applying the initial snapshot the merge agent fails with error.
“The schema script could not be propagated to the subscriber. Could not drop object ‘dbo.tablename’ because it is referenced by a FOREIGN KEY constraint” as shown in below image.
This is due to foreign key on table “CHILD” which refer to primary key on “PARENT” table. This error would not appear if both tables are part of replication but the scenario here is only “PARENT” table is replicated.
To fix this issue we need to drop the foreign key, apply the snapshot and then re-create the foreign key.
We can manually run the script to drop foreign keys and recreate them after applying snapshot. But in case of many subscribers it would be bit difficult to run scripts manually. To automate this, we used pre and post snapshot scripts in merge replication.
Pre and Post snapshot scripts
These scripts are applied at subscriber before and after the snapshot. The location of these scripts is provided while configuring merge replication.
These files are copied to snapshot folder by snapshot agent at the time of snapshot creation and applied at the subscriber. We need to take care of syntax errors or any other errors in these files else the merge agent fails while applying the snapshot and we must re-run the snapshot agent after fixing the errors in files.
These scripts should be created in a such a way that they can be re-run on subscriber during re-initialization process or in case of any failure while applying the snapshot.
For example, if you are creating a foreign key or dropping a foreign key use if exist statements so that the script can be rerun in case of failures or re-initialization.
In our case we added the drop foreign key statement in pre- snapshot script and create foreign key statement in post snapshot script. So that the foreign key is dropped before applying the snapshot and the snapshot is applied by merge agent and the foreign key is recreated after applying snapshot.
Please refer to below pre-snapshot script.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
if exists(select 1 from sys.foreign_keys where name = 'FK_CHILD') BEGIN ALTER TABLE [dbo].[CHILD] DROP CONSTRAINT [FK_CHILD] END This script checks for existence of foreign key and drop if exist in database. Please refer to the below post snapshot script. if not exists (select 1 from sys.foreign_keys where name = 'FK_CHILD') BEGIN ALTER TABLE [dbo].[CHILD] WITH CHECK ADD CONSTRAINT [FK_CHILD] FOREIGN KEY([ID]) REFERENCES [dbo].[PARENT] ([ID]) NOT FOR REPLICATION ALTER TABLE [dbo].[CHILD] CHECK CONSTRAINT [FK_CHILD] END |
These scripts are saved in a .sql file and the location is given in merge replication properties.
Navigate to the publication -> right click on the publication -> Click on properties -> Click on snapshot -> Now input the location of pre and post snapshot files you created earlier. Please refer to the below image.
Once the location of pre and post snapshot scripts is updated in merge replication properties, generate the snapshot.
Navigate to the publication -> Right click on the publication -> view snapshot agent status and click on Start.
We can also generate snapshot using replication monitor. Once the snapshot is generated we can see the pre and post snapshot file are copied from their locations to snapshot folder.
Now when the merge agent runs, the pre-snapshot script is applied first which drops the foreign key.
Now the snapshot is applied which drops the “PARENT” table successfully as it has no foreign key references and the table is re-created. Now the foreign keys which are dropped as in pre snapshot script are created back using post snapshot script.
We can create merge replication using scripts as well. I have replaced the database name with “yourdatabasename”.
To enable replication on the database use below script. This script should be executed on master database.
|
1 2 |
exec sp_replicationdboption @dbname = N'yourdatabasename', @optname = N'merge publish', @value = N'true' GO |
Below are the procedures used in creating merge publication, adding snapshot agent, to grant access to publication and adding table to merge publication. These procedures should be run on publisher at publisher database with parameters as per your need.
- sp_addmergepublication
- sp_addpublication_snapshot
- sp_grant_publication_access
- sp_addmergearticle
- sp_addmergesubscription
Notes:
The pre- and post-snapshot should be modified in case of any new foreign keys added during new releases.
These scripts should be prepared in such a way that they can be re-run.
These scripts should be run with sqlcmd to check for errors before.
Check permissions on these files to avoid permission issues while copying these files to the snapshot folder.
It is always good to make changes to main scripts instead of the pre and post snapshot scripts that were copied to snapshot folder. So that we do not loose changes in further snapshots.
Table of contents
| SQL Server Replication with a table with more than 246 columns |
| Foreign key issues while applying a snapshot in SQL Server merge replication |
| SQL Server Replication (Merge) – What gets replicated and what doesn’t |
| SQL Server Replication (Merge) – Performance Issues in replicating schema changes |
| Merge SQL Server replication parameterized row filter issues |
| Log shipping on a mirrored database |
- Geo Replication on Transparent Data Encryption (TDE) enabled Azure SQL databases - October 24, 2019
- Overview of the Collate SQL command - October 22, 2019
- Recover a lost SA password - September 20, 2019