We have a new system that runs on SQL Server 2008 r2 64-bit. There is a primary on-line transactional processing (OLTP) database that accepts a high volume of updates from several thousand Point of Sale systems at stores around the country. In order to protect this vital function, I have decided to introduce a dedicated reporting database server - from which multiple users will run some pretty complex reports.

I realise that there were a number of choices but I decided to use Transaction Replication as the mechanism for copying the data from the OLTP database to the new reporting database - one way replication.

The solution has worked well in test. I'm now being asked what changes need to be made to the backup policy to cover the architectural changes. I have read pages such as MSDN:Strategies for Backing Up and Restoring Snapshot and Transactional Replication but I think these are overkill for my solution. In fact, my current thinking is that we simply need to continue making backups of the OLTP data and logs. If the Reporting db or any of the system replication (eg distribution) databases fail then it's no big deal - we can clear all down then re-create the replication. I realise that taking a complete snapshot of the OLTP would be time consuming (approx 5 hours) but I'd be more relaxed about this that trying to restore backups of the various data and log files in the correct sequence.

My view is that the complex strategies set out in the MSDN article would only be the way to go for a more complex replication solution than I have, eg if there were multiple subscribers with 2-way replication.

Would you agree? I'd be grateful for any advice.

Many thanks,


  • Assuming that everything that has worked well in test, report functionality, response time, etc and that your test/practice environment is as close live/game conditions as possible, then you should be good to go. – jl. Jan 17 '11 at 18:49
  • the question is, can your business survive without a reporting server for 5 hrs? the backup strategies for replication would be aimed at reducing this window. It's a business decision. – Nick Kavadias Jan 18 '11 at 2:56

I'm assuming you have already good Backup Strategies in place for your OLTP DB & your Reporting DB which have happily been running in Production. I really dont see you have any additional concerns around amending these post-Replication.

You can simply generate Replication Setup Scripts via the SSMS Wizard.This will allow you to tweak and configure your Replication settings in any future Dev/Test environments and give your a Backup of your current configuration.

Replication can notoriously eat through your LOG Disk if for some reason Replication Stalls ..but provided you have manually or Automated Alerts tracking Disk Space & Replication Status you shouldnt have any concerns.

Good Luck - but in my mind the Backups needs in basic Transactional Replication shouldnt require any additional strategies.


In our environment I have a similar setup as the one you are proposing. I agree that the procedure for restoring the databases to keep replication consistent is a bit cumbersome, and I also rather recreate the replication if there is a serious problem, but I still backup the report database.

During the time it takes to generate and transfer a complete snapshot (8 hours in our case), I can restore a static copy of the report database with a different name in 30 minutes and point the report application to that database to avoid too much downtime for my report users, that way the users can execute their reports on the data from yesterday until the replicated database is up and running.

  • Thank you - it's helpful to know I'm not alone in my train of thought in this. – tr0users Jan 18 '11 at 12:55

Your Answer

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

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