How not to replicate certain delete statements

Question

How not to replicate certain delete statements

fvdsteen

SSC Veteran

Points: 212
More actions
September 6, 2013 at 6:34 am

#395433

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.

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

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

Bhaskar.Shetty Hall of Fame Points: 3054 More actions · Answer 1

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 Veteran Points: 212 More actions · Answer 2

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: 29389 More actions · Answer 3

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 Veteran Points: 212 More actions · Answer 4

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 More actions · Answer 5

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