Using database mirroring, database snapshots, and log shipping for warm standbys and reporting RRS feed

  • Question

  • I have a need to setup a new application environment that includes a SQL Server 2008 R2 instance for the oltp databases, and another instance for their reporting databases.  They also wish to have a warm standby instance for availability purposes in the same data center as the oltp instance, and another warm standby instance in a different data center.  So, I was thinking of using database mirroring to get my local warm standby instance.  I figured I could use database snapshots to create the reporting databases on the local warm standby (they want the reporting databases to be exact copies of oltp).  I think the only way to get the warm standby in another data center is to use log shipping on the oltp databases.  The total database sizes will be about 60 GB initially, growing about 75 GB per year for 3 years.

    My questions:

    1. Is this configuration possible?
    2. If I lose my primary oltp instance and failover to the local warm standby, have i totally lost my reporting environment?
    3. I know that log shipping would be set up on both the primary and mirror so that when the mirror takes over as the primary, the warm standby in the other data center will still be in sync.  Is this true?
    4. Can any oif this be automated / scripted to make this as easy for the DBAs as possible?

    Any suggestions or comments that could help me would be greatly appreciated. 

    Monday, March 18, 2013 6:24 PM


  • You can log ship from the primary but would manually have to re configure if failed over to the (old) mirror (now primary). Furthermore you would also have to overwrite the log shipped database which is not ideal.

    Making snapshots from a database mirror is possible and great for offloading reporting. 

    In terms of automating, you would certainly be able to automate some but not all of this solution and to be honest, the type of functionality you are trying to implement has been address in SQL Server 2012 with alwayson availability groups (and readable secondaries). Therefore I would advise you not to overengineer your solution and keep it simple for now.

    Mark Broadbent.

    Contact me through (twitter|blog|SQLCloud)

    Please click "Propose As Answer" if a post solves your problem
    or "Vote As Helpful" if a post has been useful to you
    Watch my sessions at the PASS Summit 2012 and SQLBits

    • Proposed as answer by Fanny Liu Friday, March 22, 2013 10:19 AM
    • Marked as answer by Fanny Liu Thursday, March 28, 2013 7:14 AM
    Wednesday, March 20, 2013 1:34 PM