We got a requirement from our developer group, to do the followings:
- Frequently (every 30mins) move the transaction data out of live database to another database
- Secondary database will be utilize for Ad-Hoc query and Reporting
- They do not want to DELETE data from this secondary reporting database, if data gets deleted from the live database
Our database server is on SQL Server 2012 Enterprise Edition.
This will make sure, live data are not been queried by the end users causing blocking issue. Developer will be working on getting the Analytic data in near future, but they wanted to implement something quickly to make live data as small as possible.
What are the recommendations to achieve this?