Gopi S (5/5/2015)
Hi DBA's,I'm looking for a process to archive data through replication. I have nightly job that purge records in few source tables(publisher) retaining only 3 yrs data. I have archive database (subscriber) that contains prior 3 yrs data and current 3 yrs data. Before nightly job DELETES records in Source table i want to STOP replication so that the delete is not replicated in archive database. After the job completes i would like to TURN ON replication so that any new inserts and updates in Source will ONLY be replicated in archive database.
My DBA tested this but after last step of turning replication back ON archive database is sync'd with source table.
Please suggest if this is possible or any other best way to do.
There are around 70 tables where 30 of them are transactional tables that needs record purge. Developing ETL process is possible but tedious.
Quick thought, you can horizontally partition the tables at the subscriber, pushing everything older than 3 years into an archive set, either by a trigger or a job. No need then to flip the replication on and off.
😎