In my office, our employees use applications that are consistently writing transaction-type data to a number of databases on a SQL Server 2012. The traffic consists mainly of inserts and updates but little if any deletes. We also have some other applications which provide reporting and monitoring services, polling these same databases. These databases are critical to the running of our business, and I'm worried about increasing impact that our monitoring and reporting applications might have on the operational side of things.

What I'm looking to do is setup a secondary DB server which would be used exclusively for reporting, which would require a duplicate of our live data but would only retain the most recent X hours of data, or even data between 1 and X hours old (i.e. no need for live data). No additional metadata would be required (i.e. transaction logs etc)

I've been reading up on the different types of replication, but am a bit confused, as I expect that the secondary DB would need to manually manage the purging of old records and am not sure how this would work with replication. I've also investigated using backup/restore jobs, however since I would want to selectively choose which tables and rows to backup, I don't see how that could work.

Essentially all I believe I would need is a cheap way of selecting and inserting rows from one server to another in an automated fashion. Obviously, this could be accomplished using a few stored procedures and a scheduled script, however what I'd like to know is if SQL Server has another way of accomplishing this.

  • 1
    SSIS, BCP, and log shipping are the generic solutions to this. Rather than us trying to tell you which feature(s) best match your requirements, you can much more efficiently rule out any of those features by looking at their specs and comparing them to your requirements. – Aaron Bertrand May 25 '16 at 16:55
  • selectively choosing what tables to replicate will lead you to implement T-Rep - but that has an overhead to manage distribution database + if something goes wrong, you will end up doing a snapshot. I would suggest (depending on the database size and no of databases), go with log shipping - delay the log restore and adjust the frequency as per you need. You can even go with incremental SSIS loading of your data. There are many ways to address your problem .. and @AaronBertrand is spot on - read up the techniques and decide what suits your env. – Kin Shah May 25 '16 at 17:00
  • Great thanks. I was expecting to have to do some research, however was having a hard time distinguishing between the various methods. I'll look up log shipping as it seems that it might be a good place to start. – nageeb May 25 '16 at 17:04
  • You don't necessarily need anything this complex, but you can see my thinking about some of this here: Readable Secondaries on a Budget. – Aaron Bertrand May 25 '16 at 17:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.