Folks, I will appreciate your feedback on the following challenge: Context A SQL Server transactional source system is being replicated to a shadow copy Triggers were added on tables in the shadow copy to populate a Log Table with actions and timestamps (Added , Deleted , Updated) The Log table is used for ETL to populate an Oracle DWH Problem The source system data will be archived and all rows is all tables will be deleted prior to certain date. The archived data is still required to be maintained in the Oracle DWH The replication between the source and shadow will populate the log table with lots of delete actions on data that in the DWH The data in the DWH will be lost How can we maintain the data in the DWH and prevent the data from being lost after the archiving ? Essam Salah

Is that possible to archive the data into a separate database?Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

That may be possible, but then ?Essam Salah

The data in DWH will not be lost.. That database will not be affected by replication or whatever, and if Oracle needs the data juts connect to that db, what do you think?Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Will try to propose that approach, but what if it is not possible to archive the data into a separate database?Essam Salah

Hmm, tghen perhaps the option is to have a table/s for archive data and partition them by month for example, and sure , exclude it from replicationBest Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/