May 5, 2015 at 10:24 am
Hi,
I need to archive OLTP database daily transactions for reporting and retain only 3 yrs of data in OLTP DB. I would like to retain only 3 yrs of transaction data in OLTP in few transactional tables where some tables like Cutomers will have all data. I have a job that deletes all transactions prior 3 yrs in required tables. I can use SSIS to load data in Archive before delete but its tedious if there are more tables?
Will replication works if i do the following process? If i restore OLTP for first time and replicate Archive and if there is any new transaction then that will be replicated in archive. Before the nightly job purge 18 month prior records i will turn off replication so that purge delete is not replicated in archive. Once the job completes i will turn on replication. In such case will archive DB still holds history data? Since history data is deleted in OLTP DB, after replication is turned ON will those deletes will occur in archive DB and will all those records deletes in archive DB?
Please let me know if there is any other best way of archiving database.
Thanks
-Gopi
May 7, 2015 at 11:19 am
you can use option in the article properties not to replicate deletes to subscriber then you don't need to remove and add replication back during your purge process.
May 7, 2015 at 11:23 am
I finally made it to work by using filters when defining table lists to be replicated.
Thanks for information.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply