I have a simple SQL server 2016 merge replication with one publisher and one subscriber. I changed a replicated table and followed the custom instruction of removing the table from the replication, editing the table, re-adding the table to the replication and creating a new snapshot. To my surprise the replication was not able to properly handle the change. When I looked at the schema script that was built to update the subscriber database I saw that the replication tried to drop and re-create the whole table which cannot work because of foreign key restraints. With ALTER commands this should have been possible. Is there a way to handle such table changes without completely deleting the subscriber database and re-creating the subscription (which would be my brutal but working solution for now)?

  • You ran the schema update script on the subscriber? Schema changes are supposed to be run on the Publisher, and those changes are then propagated by default to all Subscribers. Sep 30 '20 at 14:59
  • No, I did not run the script manually. The script was named as the cause for the error in the replication log. Oct 1 '20 at 11:56
  • I understand now. You are referring to the default way that SQL Server initializes a subscription. By default, a snapshot is captured after a publication is created, and that snapshot is applied to Subscribers during the initial synchronization of the subscription. When the snapshot is applied to a Merge subscription, the article property @pre_creation_cmd default value is drop, which specifies that the system is to drop the tables before re-creating them. Oct 1 '20 at 16:15
  • You may want to look into other ways of initializing your subscription which can be found here: docs.microsoft.com/en-us/previous-versions/sql/… Oct 1 '20 at 16:15
  • Thank you for the information. After reading more details about MS SQL replication I tend to switch from merge replication to a one-directional replication type, although our plan was to use it to have a fail-over system which jumps in/out a s soon as the primary server is down/online. This way we wouldn't have to cope with missing data no matter which server is online. Unfortunately merge replication seems to have too many pitfalls - even in our simple one publisher/one subscriber scenario. Oct 5 '20 at 11:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.