none
Merge Replication with Delete Tracking disabled RRS feed

  • Question

  • First the question.  I wish to use sp_ChangeMergeArticle (as per the link at the end of this post) to set the delete_tracking property for a table to 0. The article says that this will not be supported in future versions but does not suggest another way to disable the replication of deletions. If someone can suggest another way, I would appreciate it.

    I should explain the requirement of course, in case a complete change of thinking is necessary for the future.

    I have two SCADA (industrial control applications) running in Primary/Standby mode, meaning that the Primary normally communicates with the plant hardware (known as PLCs) but the Standby will take automatically over if it fails.

    Both the Primary and Standby have a SQL Express database, on their local drives, thus making them always available. Normally the Primary logs data to its local database, at a an average rate of perhaps 10 records per minute. This data is replicated to the Standby so it is up to date, to within 1 minute, in the event that the Primary fails. When the primary is brought back to service, merge replication brings its database back up to date within 1 minute. All of this works well but there is a long term issue as described in the next paragraph.

    There is obviously a third SQL Server SE elsewhere on the network hosting the merge replication (publication and subscriptions) and we want this to act as long term storage for the data. Every month the SCADA application purge their local (SQL Express) databases of records that were created more than 6 month earlier. We don't want these records deleted from the "central replica" of the database that resides in the SQL Server SE.

    Any suggestions offered would be much appreciated.

    https://docs.microsoft.com/en-us/sql/relational-databases/replication/publish/specify-that-deletes-should-not-be-tracked-for-merge-articles


    R Campbell


    Monday, March 12, 2018 9:43 PM

Answers

  • There are two approaches to this. The first is to disable the delete triggers when you do your purge.

    This is unsupported and can cause problems where you will be forced to reinitialize. That been said I know a company who has been doing this for years.

    The supported way to do is to check article permissions for delete.

    The problem with this solution is that the deletes are tracked and replicated up to the publisher and then applied. At the point of application permission is checked and if it fails the delete attempt is logged as a conflict.

    You can selectively change permission of the merge replication account to allow deletes you want to apply to the publisher to delete, and then deny delete permission to prevent the deletes from happening.

    • Marked as answer by Dick Campbell Tuesday, March 13, 2018 7:35 PM
    Tuesday, March 13, 2018 1:54 PM
    Moderator

All replies

  • There are two approaches to this. The first is to disable the delete triggers when you do your purge.

    This is unsupported and can cause problems where you will be forced to reinitialize. That been said I know a company who has been doing this for years.

    The supported way to do is to check article permissions for delete.

    The problem with this solution is that the deletes are tracked and replicated up to the publisher and then applied. At the point of application permission is checked and if it fails the delete attempt is logged as a conflict.

    You can selectively change permission of the merge replication account to allow deletes you want to apply to the publisher to delete, and then deny delete permission to prevent the deletes from happening.

    • Marked as answer by Dick Campbell Tuesday, March 13, 2018 7:35 PM
    Tuesday, March 13, 2018 1:54 PM
    Moderator
  • Thanks Hilary, should I disregard sp_ChangeMergeArticle or is that still an option? I did notice “Verify DELETE permission” but did think of that it wouid generate conflict warnings. Thanks for the suggestions.

    R Campbell

    Tuesday, March 13, 2018 7:35 PM
  • It is still an option.  However keep in mind that you are kicking the can down the road. When you upgrade to your next version of SQL Server you may find that this option no longer exists and at that time and then you will need to scramble for a viable solution.
    Tuesday, March 13, 2018 7:40 PM
    Moderator