Filter SQL Replication Deletes by user

5 pts.
Tags:
Replicating databases
Replication
SQL replication

I am in the process of setting up replication from sql 2008r2 to Oracle 11.  I have one requirement that is giving me some issues.  My sql db is the publisher and currently maintains data from year 1.  The Oracle subscriber will maintain 7 years data.  After the initial snapshot the sql db will only maintain 1 year data.  My problem is to distinquish between what I would call a transactional delete and a data purge process. 

The transactional delete is defined as any normal delete performed on the 1 year data in the sql db.  The purge function is deleting (purging) any records older than the 1 year.  It is obvious that the purge transactions can not be seen by the Oracle database as the data retention time is 7 years. 

I can easily run a dedicated proc to purge the data but then the normal replication delete proc will delete the purged records.  I have tried creating a purge proc on the subscriber that will delete the older data.  But to get this to work properly I had to disable the normal MS delete proc.  Secondly, I modified the MS delete proc to both purge and delete data and this seems that it would work but I do not know how to pass additional parameters to the proc.  The origional only uses PK where the revised version will need a purge/delete flag and a time from which to purge data.  Running the modified proc manually works but as I said I do not know how to pass in parameters within replication.

Any assistance with this would be most helpful.

 

 



Software/Hardware used:
SQL 2008 R2 & Oracle 11

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following