Moving Transaction Data To Another Database For Reporting Purpose

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?

Thank you,

HP

Since you have this requirement,


They do not want to DELETE data from this secondary reporting database, if data gets deleted from the live database

AlwaysON gets eliminated.

I would suggest you to setup Transactional replication.


Frequently (every 30mins) move the transaction data out of live database to another database

You can schedule the log reader agent frequency to 30 mins (or as per your needs).

As a side note, I will suggest you to read this article by Kendra Little. It explains T-Rep and compares it with other technologies (AlwaysON, CDC, etc).

StackExchange Database Administrators Q#103887, answer score: 7