We have a setup with a product that has a production database (many customer installations). The production database has been around many years and there are some performance issues that is getting worse. However the customers like to keep the data for generating different kind of reporting and statistics. Sometimes it also import to audit product logs.
So what we want to do is setup an archive / reporting database that the customer handles themselves and the production only holds "active" data and is maintained by a clean-up job that removes old dates.
There are many users +10000 that access the production database daily but very few that access the archive database.
What is the best way to achieve this, build in replication, log shipping or write a synchronisation mechanism? Performance issues on the production database?
If we for instance decides for logshipping, is it possible to prevent that delete operations on the production database are propagated to the archive database?