locked
Replication, Mirroring and Always On - for reporting, not failover. RRS feed

  • Question

  • We have SQL 2005 and 2008 boxes and are moving to 2012. Because we're starting from scratch with a new SQL2012 environment I'm just checking if there's a better way of doing what we've been doing...

    Previously we've used replication to copy data from PROD_DB (our production db) to RPT_DB (report DB). And it's proven problematic so I'd like to move away from replication if possible.

    RPT_DB is used by SQL SERVER Report Service to provide reports to users. The report db has a different set of indexes etc to ensure max performance for the reports. We don't want to put them on the transactional database because we've inherited some really bad table structures that suffer from too many indexes. We also turn on parrellism in reports environment (improves performance) but not in transactional one (makes some things slower - maybe down to bad design again).

    So the structure of RPT_DB isn't quite the same as PROD_DB - but we want to keep the data up-to-date.

    I'm trying to work out what's the best option - Replication, Mirroring or Always On. Most discussion on these topics tends to focus on failover scenarios. I just want to see what's recommended for what we are trying to do.

    I'm familiar with replication (tho not a fan) so I know it can do what I want but I'd like some input on whether it would be better to use Mirroring (tho soon to be made obsolete !) or Always On instead of replication.

    Monday, December 9, 2013 12:44 AM

Answers

  •  One of your requirement: Need to have extra indexes on your secondary for secondary.

    Obviously, mirroring will not work for the above reason and of course, you cannot query the mirrored DB actively.

    Always on: Even with Always on,I believe, you  need to have same indexes on primary and secondary replicas. So, you cannot create new index just on secondary replicas.

    Replication : obviously, you know this can work for you. 

    However, you may want to test Alwayon and see how it works, since you can have up to 4 replicas and this gives more options as you can distribute the load across and may be the extra indexes would not have much impact on the writes in  Primary.

    Refer :

    http://blogs.msdn.com/b/srgolla/archive/2012/09/17/sql-server-2012-always-on-faqs.aspx

    http://technet.microsoft.com/en-us/library/ff878253.aspx


    Hope it Helps!!

    • Proposed as answer by Fanny Liu Tuesday, December 10, 2013 9:58 AM
    • Marked as answer by Fanny Liu Monday, December 23, 2013 1:30 AM
    Monday, December 9, 2013 1:09 AM

All replies