6

Suggest me the best method for My DR plan. Currently we have set of databases (6) used for our application and we in the process of building DR and HA plan. As we are into small business we are using standard edition 2012 and we are unable to make use of AG feature.

Thanks for your help.

8

Could you please let me know the best option to proceed and least complexity to maintain

Logshipping is your best option for Disaster Recovery since you are on standard edition.

Make sure you test the disaster recovery - simulate it so that you are confident when a real disaster strikes.

Note: In my company, we use LogShipping as a primary DR solution involving 2500 databases globally and it works perfectly fine and fulfills our business continuity goal (it worked during Hurricane Sandy as well). We automate most of the things and test(simulate) every quarter per individual database and yearly the entire data center. THis gives us a huge confidence when doing an actual failover during a disaster.

Check out the things to keep in mind when going for Logshipping :

Log shipping is a low-cost (poor-man) solution that provides disaster recovery protection at the database level. A log shipping configuration includes one primary server and one or more secondary servers. A database is backed up and restored from the primary server to the secondary server. At regularly scheduled intervals, a transaction log backup and restore is performed at the primary and secondary server to keep the servers in sync.

Also, Kendra Little has an excellent post on Log Shipping Magic: Using A Differential Backup to Refresh a Delayed Secondary

Note: As per your question, it seems to me that you are mixing up HA and DR. They are different when it comes to RTO and RPO vs availability of your application.

  • 1
    I prefer this option also. It's much simpler and works very well. – Kris Gruttemeyer Jun 23 '15 at 23:49
  • Hi Kin, In my case whats the use of having multiple DR options when we are using VM Replica between two sites. – Ajay Jul 22 '15 at 18:50
  • Ajay, if you are using Hyper-V Replica then you should not be using any SQL Server high-availability features, as per support.microsoft.com/en-nz/kb/956893. – Greenstone Walker Jul 22 '15 at 21:18
  • Thanks, we are planning to use Hyper-V replica for hosting primary production and using the same approach for DR purpose. As you said Microsoft wont provide support if we enable any HA options, But our application require replication to be used for load balancing purpose. how to overcome these problems to make use of both Hyper V and Replication,Log shipping options in SQL Server. – Ajay Jul 24 '15 at 17:42
1

Log shipping will best meet your criteria. It is supported in standard edition, has the least level of complexity, and can easily be configured to meet your needs around allowable data loss and overall downtime.

It's really important to perform DR tests on a regular basis. This ensures not only that the steps work as expected each time, but also that you are comfortable with performing them during a stressful downtime situation. Consider using a step-by-step solution to help you logically work through the necessary steps to fail over (and back if a DR test).

Also, realize that log shipping is done at the database level and you need to take proactive action to ensure a successful failover. If your failover attempt works great, your databases are running fine on the Secondary, but nobody can actually sign into the application, then unfortunately your efforts have been in vain. Remember to:

  • Script out your logins (either automate this via a scheduled job or execute manually as part of each DR test, depending on how frequently your logins change).
  • Script out any applicable jobs and copy them to the Secondary server where they should be disabled unless actually needed as part of a true failover.
  • Determine whether you have any SSIS packages that may need to be copied across as part of every DR test you perform.

Configure log shipping at a frequency that's appropriate for your RPO and RTO. Probably not necessary to do log shipping this frequently, but then again most of us tend to configure it on the other end of the spectrum and open ourselves up to unnecessary and unacceptable levels of data loss.

  • Thanks Micah, I am aware of these steps and planning to have a DR document with all steps/scripts. – Ajay Jul 22 '15 at 18:51

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.