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!