SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How not to replicate certain delete statements


How not to replicate certain delete statements

Author
Message
fvdsteen
fvdsteen
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 12
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
Bhaskar.Shetty
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1128 Visits: 509
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
fvdsteen
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 12
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
happycat59
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8579 Visits: 3281
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
fvdsteen
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 12
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
Dennis Post
SSC Eights!
SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)

Group: General Forum Members
Points: 969 Visits: 547
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.

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


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search