Article with a filter: Instead of updating, it is deleting rows at subscriber


  • EXEC sp_addarticle @publication = N'Sales_db__TranRepl', @article = N'DummyTable'
    , @source_owner = N'dbo', @source_object = N'DummyTable', @type = N'logbased', @description = N''
    , @creation_script = N'', @pre_creation_cmd = N'delete'
    , @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual'
    , @destination_table = N'DummyTable', @destination_owner = N'dbo'
    , @status = 24, @vertical_partition = N'false', @ins_cmd = N'CALL [dbo].[sp_MSins_dboDummyTable]'
    , @del_cmd = N'CALL [dbo].[sp_MSdel_dboDummyTable]', @upd_cmd = N'SCALL [dbo].[sp_MSupd_dboDummyTable]'
    , @filter_clause = N'[Delete_date] IS NULL'
    -- Adding the article filter
    EXEC SP_ARTICLEFILTER @publication = N'Sales_db__TranRepl', @article = N'DummyTable'
    , @filter_name = N'FLTR_DummyTable_1__91', @filter_clause = N'[Delete_date] IS NULL'
    , @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
    -- Adding the article synchronization object
    EXEC sp_articleview @publication = N'Sales_db__TranRepl', @article = N'DummyTable'
    , @view_name = N'SYNC_DummyTable_1__91', @filter_clause = N'[Delete_date] IS NULL'
    , @force_invalidate_snapshot = 1, @force_reinit_subscription = 1

    Env details:

    SQL SERVER 2008 R2 for both publisher and subscriber.
    Both are ent edition.
    Article in question has a filter at column Delete_date which is a date column (NOT datetime, date only)

    All inserts, deletes and updates are running fine
    When for the article, I am trying to update the value of column Delete_date for a record from Null to a date value, lets say 2013-06-12, it is deleting that record from the subscriber.
    I verified and found that the replication sp is fired the moment I am updating the column value on which filter is defined.
    This looks strange to me.

    My requirement is that:
    when the column Delete_date has a value of not null, it must not be processed by replication.
    So in the above example when I am putting a value to  the column Delete_date for a record, it should be ignored by replication (No effect at the subscriber).

    Please help. In case I need to furnish more details, please let me know.

    Wednesday, June 12, 2013 2:19 PM

All replies

  • Hi Aanya,

    That is the default behaviour of replication. It should DELETE the rows on subscriber if you update the field to a value where that is not replicated by Replication sicne the Replication engine fires DELETE SP to delete these records on Subscriber..

    Regards Chenchi MSSQL Server DBA

    Wednesday, June 12, 2013 3:32 PM
  • Hello, Chenchi, this is not very clear to me.
    Why is it deleting a row at the subscriber when I am updating at the publisher?
    Is it documented any where?
    How can I achieve that the updates are not converting to the deletes and rather there should not be any action performed.

    Wednesday, June 12, 2013 3:49 PM
  • Just to be clear about what Chenchi said.

    You setup a filter to only send rows to the subscriber "WHERE [Delete_date] IS NULL".  When you change the value of [Delete_Date] from NULL to non-NULL it no longer meets the criteria you specified for rows on the subscriber, so it is deleted from all subscribers.

    This is normal and expected behavior.

    It sounds like you don't want the [Delete_Date] changed on the subscriber?  You want it left as NULL on the subscriber?  There is no function which will "ignore" changes.

    Wednesday, June 12, 2013 7:39 PM
  • Okay so here is the requirement.

    ServerA is production

    ServerB is reporting

    1. We need to purge some records from the ServerA. The same records must not be purged from Reporting.

    2. Records to be purged will be identified on the basis of Column [Delete_Date]. If the value of [Delete_Date] for a record is NOT null, it is eligible for purging. 

    3. Column [Delete_Date] will be populated at both ServerA and ServerB.
    The only difference is that the records will not be purged from Reporting Server (ServerB)
    4. We have a batch process which will populate the [Delete_Date] on both the Production and Reporting servers (ServerA and ServerB)

    5. This purging is a periodic exercise (Every three Months).
    6. Since the data is not removed from the subscriber, the users will be able to run the reports on the basis of the [Delete_Date] values too. Lets say the first purge happened on 1st Jan 2013, next on 1st April 2013 and the next on 1st July 2013. Now the user has the option to filter the data on the basis of these dates too.

    To achieve this I wanted to put replication filter on the Date column "Where [Delete_Date]  IS NULL". So that the records which have a purge date ([Delete_Date] not null) would have become out of range for replication and hence deleting those records from production would have not caused the reporting data to be deleted too.  This plan is working for deletes and when I am deleting the records from the production Server they are not deleted from the Reporting Server. 

    What is not working is just that when I am populating the date in the column [Delete_Date], it is automatically removing the records from the reporting database too.

    • Marked as answer by Aanya P Thursday, June 13, 2013 9:34 AM
    • Unmarked as answer by Aanya P Thursday, June 13, 2013 9:34 AM
    Thursday, June 13, 2013 2:14 AM
  • Okay, this is what I have almost decided to do unless I hear from somebody that there are better options.

    Step1: Take the article off the replication.
    Step2: Run the batch to update the column Delete_Date in both the Publisher and Subscriber. Now since the article is off replication, it wont delete the data from the Subscriber database.
    Step3: Add the article back with the filters as mentioned in the original Question.

    Next time when the purge activity will happen, repeat the same steps again.

    Any better way of doing this so that we dont have to take the article off Replication, please?

    Thursday, June 13, 2013 9:39 AM
  • You are going about this entirely the wrong way.

    The [Delete_Date] has nothing to do with the data you want on the subscription.  Remove the row filter and change the article to "Do Not replication delete statements".  This will not delete from the reporting instance when the purge process runs.

    Thursday, June 13, 2013 1:23 PM
  • Dear Tom,

    I see the following issues will come if I follow the approach you suggested. Please see if I am understanding them correctly.

    1. This will block all deletes and the deletes that I want to be replicated after the purge activity will also be blocked.  Where as If I add the article with filters, it will block only the purge deletes (out of replication scope data).
    2. When we will reinitialize the subscription, it will again pick everything from the publisher and extra records that we still need to maintain at the subscriber will be lost. Publisher will have X number and subscriber will have Y+X after purging.  Where as with the filtered replication, I would be able to maintain both Y+X on the subscriber as it will only repopulate the out of replication scope data. The out of replication scope data will remain intact.

    My requirement is only to block the purge deletes and not only delete.
    Other requirement is that when a pity production BAU DBA will reinitialize the whole subscription, it will bring the whole dataset from the publication and overwrite the entire (X+Y will become X) dataset at the subscriber. 

    Thursday, June 13, 2013 6:33 PM
  • Yes, it will block all deletes.  You are saying there are valid deletes other than the purge process? 

    The article row filter on Delete_Date will never work.  It does not only send those rows, it syncs only those rows. 

    Under all circimstances, if you physically delete the rows for the source publisher, you cannot reinitialize the from the publisher to the subscriber.  Those deleted rows no longer exist on the publisher and the subscriber tables are dropped and recreated on snapshot.

    I do not see any way to accomplish what you are trying to do.  You could setup a trigger on the subscriber table, and capture deletes into a new "history" table.  However, I do not know how you would identify a non-purge delete and a purge delete?

    Thursday, June 13, 2013 6:59 PM
  • no, this is where the catch is.
    "Those deleted rows no longer exist on the publisher and the subscriber tables are dropped and recreated on snapshot."
    This is not true, we can set the precreation command to delete the filter data only. This will ensure that everytime a subscriber is reinitialized, it doesnot actually drop the table from the subscriber but only deletes the In scope data from the subscriber. This way it will maintain the Y +X
    Where Y is the purged data and X is the current publisher data.

    The way I will distinguish between purge and non purge data, would be on the basis of the value of the column Delete_Date.
    If there is a DATE value on the delete_date column, it will be out of scope of replication through the filters.

    If there is a NULL value on the delete_date column, it will be IN of scope of replication through the filters.

    So, when I will delete the rows from the publisher which has a Date value, those deletes will be blocked by replication filters. Where as normal deletes will not be blocked.

    Thursday, June 13, 2013 7:22 PM
  • To name the precreation command, I mean the following option in the sp_addarticle when we add an article.


    The issue with the approach that I mentioned I would be using is that, on every release, I need to remove the article from replicaiton and then add it back once the values in the column delete_date is populated.  Just wondering if there can be an option where I dont have to do this on every release.

    Saturday, June 15, 2013 5:28 AM