sql-server-2012 – DR Options for SharePoint Databases: Ideas and Experiences

availability-groupslog-shippingmirroringsql-server-2012

First let me clarify that I am not looking for thoughts in terms what is the best or the bad options for a DR for SQL Server. I am just looking for for inputs from experienced people.

We are running Virtual Hyper-V and SQL Sever 2012 with FCI. I have three options here.

  1. AlwaysOn (DR).
    It seems easy and awesome but it is a new topic for us. I cannot find too much information about it , other challenge is we have a limited support so i'm not sure to go with it unless feel comfortable. we implemented on test environment, but I believe production is a different story.

  2. Database Mirroring (DR)
    May be the easiest option to go with. and yes I know it a deprecated feature. but still supported in SQl Server 2012, so still we have enough time.

    I like mirroring because it is recommended from Microsoft before Always-on, and secondly it is good in terms of transactions as commit in primary then directory shipped to mirrored database in DR. easy to monitor and stable.

  3. Log shipping(DR)
    The more robust solution as DR. Microsoft use it in office 365, and very good option in case of slow network. It does need some work to configure and a lot of hands-on to prepare. but it is old and reliable. May be is good for non-critical databases where can I put interval like 24 hours. One cool thing is read only databases where I can switch application in read only mode till primary get fixed unlike mirroring.( Yes Always-On does that).

Again it is for DR only and I would like to hear from you. Consider this for SharePoint databases which means only content and other few databases will be replicated as Microsoft support them only.

Best Answer

In terms of wanting to get input from other people about their experiences, I can share about our use of Log Shipping in our Production environment. We use native Log Shipping and perform DR tests once a quarter for our critical systems. This involves failing over to the Secondary, pointing the application(s) at it, and testing that everything works as expected. We then fail back to the Primary and ensure things are ready for our end users again.

By repeatedly testing our DR process we've been able to make some hard-earned improvements. The solution we use for helping perform the failover and failback procedures is here.

Related Question