5

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

3
  • What version of SQL-server would you guys be using?
    – KASQLDBA
    Commented Jun 12, 2015 at 12:25
  • Also with what edition, like standard, enterprise etc?
    – KASQLDBA
    Commented Jun 12, 2015 at 12:25
  • Also, define frequently. Daily? Hourly? Every minute?
    – Prime03
    Commented Jun 12, 2015 at 12:26

1 Answer 1

7

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.

enter image description here

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).

7
  • Thank you Kin for your suggestions, I really appreciate it!
    – NismoGTR05
    Commented Jun 12, 2015 at 17:34
  • @NismoGTR05 Glad that you find it helpful. Please upvote / accept as answer if you feel that the answer has answered your question.
    – Kin Shah
    Commented Jun 12, 2015 at 17:39
  • Only thing I am worried about is, what if we had to reinitialize the replication? How it would be affect all the non-deleted records from the reporting database?
    – NismoGTR05
    Commented Jun 15, 2015 at 14:13
  • A reinitialize will have to generate a new snapshot and apply it to subscriber. You will be at square 1 again, so you need to be careful. What you can do is take a backup of subscriber and then after the snapshot is applied, you can do a data compare (I have used redgate's data compare and its a life saver) to insert the data back.
    – Kin Shah
    Commented Jun 15, 2015 at 14:34
  • oh!. Is there any other way to accomplish this other than using Replication than?
    – NismoGTR05
    Commented Jun 15, 2015 at 15:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.