How not to replicate certain delete statements

  • fvdsteen

    SSC Rookie

    Points: 42

    I have a database for an order handling system that is replicated to an other database.

    The publisher and distributor database are the same.

    I want to keep the publisher database small for performance reasons.

    The subscriber database is used for reporting.

    I want the delete statements that are executed by the user applications replicated to the subscriber database.

    At night I want to run a stored procedure to clean up the publisher database. This involves deleting records from several table if they are older than x days.

    These deletes (from the clean up job) I do not want to replicate to the subscriber database.

    Is there are way to do this?

    So if a user deletes an order from the system with his application then I want to mirror that delete to the subscriber database but if the order is deleted by a clean up job because it is shipped more than 60 days ago then I do not want to mirror that delete.

  • Bhaskar.Shetty

    Hall of Fame

    Points: 3054

    You can implement Filter on the publications of tables, or disable log reader of Replication publications for the time being so the record deleted by your Stored procedure doesn't record it and in turn never gets deleted from subscribers.

    But I have doubt what about the foreign key constraints with the data.

  • fvdsteen

    SSC Rookie

    Points: 42

    Bhaskar.Shetty (9/6/2013)


    You can implement Filter on the publications of tables, or disable log reader of Replication publications for the time being so the record deleted by your Stored procedure doesn't record it and in turn never gets deleted from subscribers.

    But I have doubt what about the foreign key constraints with the data.

    A filter on the publications is not an option but I will try the disable of the logreader.

    Thanks for your reply

  • happycat59

    One Orange Chip

    Points: 29186

    Disabling the logreader agent won't work - the log records will remain in the transaction log until the logreader stars up again and the deletes will still be sent to the subscriber.

    I think you will need to think about another option - perhaps a logical delete flag. The publisher would treat the record as though it has been deleted and the subscriber would ignore the flag and, hence, would still be able to read the record. Another option would be to configure replication to use custom stored procs and write the code required to keep the deleted records in the subscriber database

  • fvdsteen

    SSC Rookie

    Points: 42

    Thanks for your reply.

    Indeed, as I already figured out myself, disabling the logreader won't work.

    I will try to find an other solution.

  • Dennis Post

    SSCarpal Tunnel

    Points: 4473

    Hi,

    Instead of just deleting the records on the publisher, insert them in an archive table on the subscriber then do your publisher cleanup.

    Create a view on the subscriber with both the replicated table and the archive table.

    Alter your reports to use the view instead of the table.

    This will also mean that if you have to create a new replication snapshot, you won't lose all your archival data on the subscriber.

    Would this work for you?



    For better, quicker answers on T-SQL questions, read Jeff Moden's suggestions.[/url]

    "Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply