none
Merge Replication not deleting unmatched filter rows from Subscriber Database RRS feed

  • Question

  • I have applied filters on articles in SQL Merge Replication and syncing the data using MergeSynchronizationAgent via Web Synchronization. The data is syncing as per the applied filters from publisher to subscriber. Similarly when anything is changed on subscriber that is syncing to publisher correctly and if the data at subscriber does not lie under applied filter on publisher, that is automatically deleting from subscriber database.

    All this is working well if I am syncing daily. Problem starts when I initiate a sync after a weekend, or after couple of days. Everything in the sync works except the data which should now be deleted from subscriber as it does not fulfill the applied filter criteria.

    I actually have filter applied based on GetDate(), which means as soon as the next day starts and I initiate a sync, the data from the previous day should be deleted automatically from subscriber. Even in this, the publisher to subscriber direction syncing is fine i.e. the previous day's data does not sync to subscriber. But this should work the other way (subscriber to publisher) as well i.e. the last days data from subscriber should be deleted too. This is happening correctly if I am syncing daily, but not happening if I have a gap of days (like weekend) in syncing.

    Monday, December 9, 2019 7:22 AM

Answers

  • Check this link -> here you have some recommendations on filtering on merge replication. https://docs.microsoft.com/en-us/sql/relational-databases/replication/merge/best-practices-for-time-based-row-filters?view=sql-server-ver15 considering your scenario.. can you run the agent on weekends as well? Because I think that the problem is the last sinchronization date. Hope this helps!
    Wednesday, December 11, 2019 2:48 PM

All replies

  • Hi M0Bilal,

    Did you check the replication job executions or replication monitor to see if there were any issue on the synchronization process?

    Monday, December 9, 2019 4:18 PM
  • Hi M0Bilal,

    Could you please check your replication monitor? And please share us the result if you found some issue from it. 
    Please check if below link could help you. Merge SQL Server replication parameterized row filter issues

    Best regards,
    Cathy 

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, December 10, 2019 9:42 AM
  • Hi Cathy / Gonzalo,

    I cannot see anything in the replication monitor. I right clicked my publication > Launch Replication Monitor. When I select my Publisher, the right sided area is blank. I cannot post a picture here, otherwise I could have.

    Note: Just to remind and clear that I am using MergeSynchronizationAgent via Web Synchronization, that means I am creating subscriptions remotely on-demand on remote machines and invoking syncing on-demand from remote machines.

    Tuesday, December 10, 2019 1:48 PM
  • Hi M0bilal,

    If you are calling direcly the merge synchronization agent I'll reccomend enabling the verbose to a file to see the progress or if you have any issue. You can use this link -> https://docs.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.replication.mergesynchronizationagent?view=sqlserver-2016 to use the output file as well as the verbose level (output and outputverboselevel parameters).

    Tuesday, December 10, 2019 2:48 PM
  • Hi Gonzalo,

    I resolved the Replication monitor issue. I restarted the Server, connected the publisher and distributor again. and the replication monitor started to show me the recent activities. And surprisingly there are no errors in the history.

    The output log file in ON with verbose level 4. But there is error logged.

    I am not sure this is an error, because if this was an error; I must have been notified about that by replication itself. This is something I believe some configuration issue or some setting I am missing. As the syncing works without any flaw during weekdays. Even it will work on Monday too. But it should delete the previous day records. For example, If I run the sync on Wednesday, the syncing will insert fresh records from server, update existing records if applicable and automatically delete Tuesday's records from my subscription database; as the filter applied on article contains Date = GETDATE(). But if I run the syncing on Monday, it will insert / update the records but wouldn't delete the Friday's records from subscription database.


    • Edited by M0Bilal Wednesday, December 11, 2019 8:58 AM Typo
    Wednesday, December 11, 2019 8:57 AM
  • Check this link -> here you have some recommendations on filtering on merge replication. https://docs.microsoft.com/en-us/sql/relational-databases/replication/merge/best-practices-for-time-based-row-filters?view=sql-server-ver15 considering your scenario.. can you run the agent on weekends as well? Because I think that the problem is the last sinchronization date. Hope this helps!
    Wednesday, December 11, 2019 2:48 PM
  • GetDate means that it will look for changes which have occured in that time period matching the filter and then delete/insert/update them.

    Rows which are not changed in the time period even though the value of yoru filter criteria is met, will not enter the filtering condition and will not be replicated/removed.

    Wednesday, December 11, 2019 6:09 PM
    Moderator
  • Hi Gonzalo,

    Thanks for your interest in my case. I read the docs from the link that you have shared. This makes sense. I will now device some mechanism to update the records somehow after the weekend. Hopefully that will resolve the problem. Unfortunately I am not able to test this at the moment, but fortunately its Friday today and I will be able to give it a try on Monday.

    Thanks again for your consistent help. If it worked, I will make sure to mark it as answer.

    Friday, December 13, 2019 6:07 AM
  • Hilary, Thanks for the response. Yes i found the same in the link shared by @Gonzalo.
    Friday, December 13, 2019 6:09 AM
  • Hi M0Bilal,

    After testing, did you resolve your issue? If you have resolved your issue, please mark the useful reply as answer. This can be beneficial to other community members reading the thread.
    In addition, if you have another questions, please feel free to ask.
    Thanks for your contribution.
     
    Best regards,
    Cathy

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, December 16, 2019 9:17 AM
  • Hi Cathy / Gonzalo,

    Surprisingly on Monday 16th, when I initiated the syncing; everything just worked as expected. What I did on Friday 13th was, I restarted the Server, I configured the replication Monitor and added some data into database. I then synced that data on my tablet. Then I left for the day and on the Monday I synced, all the data that I downloaded on Friday on my tablet was deleted after this sync. I didn't make any change in the data. I don't know how it worked as same practice I was following since last many weekends but it did not use to work.

    Then something strange happened again today. After Monday 16th, I didn't initiate any sync on the tablet. Today (Thursday 19th) when I tried to sync, same problem occurred again. Replication was not deleting Monday's data after the syncing. So as suggested in the link, I updated the data in one of the columns of article, initiated sync again. The changes did propagated to Server, but didn't delete from my tablet database even though they do not lie under the filter of GetDate().

    Was this the remedy I have to do to force the records to lie under the filter criteria?

    Let me just explain it little bit. Among several other columns, lets just consider two columns of an article; LogDate & StatudId. On Monday 16th, there was

     - LogDate -> 2019-12-16

     - StatusId -> 78

    On Thursday 19th, when I initially synced in the morning, it was same as above and nothing get downloaded or uploaded to server. Then I Updated StatusId = 128 and synced. Now I believe the publication should have recorded this change and sync this table to server. It did synced, I verified StatusId is changed on Server too. but it did not delete this record from my tablet.

    Please correct me if I didn't do it as I was supposed to do.

    • Edited by M0Bilal Thursday, December 19, 2019 7:59 AM
    Thursday, December 19, 2019 7:56 AM
  • Hi Gonzalo Bissio,

    I have changed the whole logic as mentioned in the Microsoft's docs. Everything is working perfectly as expected. Thanks for helping me out.

    Apologies for late marking as answer. I actually needed weeks to test this, as the issue only reproduces after weekend. Thanks again!



    Thanks, Bilal

    Wednesday, January 15, 2020 7:41 AM