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.