Sql-server – the best solution to implement to replicate a database, with a purging process is in place

sql servertransactional-replication

I am planning to implement a solution solution to create a separate database for reporting purposes (read only). I thought of implementing transactional replication to solve this problem, however I have a data purge process that runs every week. This means that any data deleted will be replicated too.

I need help to design the best solution, how do I create a separate database instance for reporting. I want the primary database to keep the data purging in place, but I want a separate db system that holds all the data.

Need ideas! Please help!

Best Answer

If I understand you correctly the data is purged from the publisher database on a weekly basis - correct? In this case I would set up replication to a subscriber and then transfer the data from the subscriber to a data warehouse, as Max Vernon suggested.

Hope this helps