Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How not to replicate certain delete statements Expand / Collapse
Author
Message
Posted Friday, September 06, 2013 6:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 09, 2013 11:43 PM
Points: 3, Visits: 11
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.

Post #1492191
Posted Friday, September 06, 2013 7:19 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 8:31 AM
Points: 409, Visits: 386
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.
Post #1492219
Posted Saturday, September 07, 2013 12:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 09, 2013 11:43 PM
Points: 3, Visits: 11
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
Post #1492476
Posted Sunday, September 08, 2013 10:11 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:34 PM
Points: 2,844, Visits: 2,426
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



Post #1492654
Posted Monday, September 09, 2013 11:45 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 09, 2013 11:43 PM
Points: 3, Visits: 11
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.
Post #1492994
Posted Wednesday, September 11, 2013 2:54 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 06, 2014 1:35 AM
Points: 178, 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
Post #1493509
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse