Designing an HA/DR Strategy for SQL Server 2016

By Mike Donaghey, DBA Services Manager

To ensure business continuity in the face of a power outage, natural disaster, or equipment
failure, it’s important to consider your high availability (HA) and disaster recovery (DR) strategy. HA minimizes the odds of a disruption from malfunctioning equipment or service failure, while DR is your plan for getting up and running after a disaster.

Developing an HA and DR strategy can be challenging.  Microsoft’s SQL Server 2016, like most other major databases, offers several possible HA/DR options, but which you choose will depend on your company’s requirements for recovery and the details of your IT infrastructure, as well as which edition of SQL Server you have. Microsoft provides a comprehensive comparison chart of the High Availability features of SQL Server 2016 editions here.

To help you understand your options, Matt Aslett, Research Director for Data Platforms and Analytics at 451 Research, and I reviewed many of the key considerations and capabilities around HA/DR plan and SQL Server 2016’s HA/DR capabilities in this recent webinar:

Register now: On-demand webinar_SQL Server: Understanding Your Options for Disaster Recovery & High Availability

As we discussed in the webinar, there are four major options for implementing HA and/or DR with SQL Server:

  1. AlwaysOn Availability Groups: First introduced in 2012, AlwaysOn Availability Groups are similar to database mirroring but with greater capabilities. An availability group is a group of databases designed to fail over together. A Basic Availability Group has one primary and one replica, while the advanced features of Availability Groups (SQL 2014) can support a set of primary databases and one to eight sets of corresponding secondary databases. Each replica resides on a separate instance of SQL Server and on individual physical nodes or cloud-based VMs. There is no need for shared storage.
  2. AlwaysOn Failover Clustering:  Enabled by Windows Server Failover Clustering, this is a SQL Server instance installed across nodes in a cluster with shared disk storage. An AlwaysOn Failover Cluster requires shared disk storage and it can leverage AlwaysOn Availability Groups for remote disaster recovery.
  3. Database Mirroring:  Database mirroring provides high availability and protection for a single database, with one mirror allowed per database. However, you may want to note, Microsoft is phasing out database mirroring in future versions of SQL Server, replacing it with Basic Availability Groups.
  4. Log Shipping.  Log shipping involves maintaining one or more standby databases for one primary production database, with transaction log backups automatically sent from the primary to the read-only secondary databases.

sql-imageSo how do you decide which to use?  That is a complicated decision that is often based on multiple factors. One consideration is whether you want both HA and DR. Log shipping, for instance, is good for DR and can be used for reporting, although limitations do exist.

When considering an HA/ DR strategy, two metrics are important to know: Recovery Point Objective (RPO) and Recovery Time Objective (RTO).

  • RPO is how much data you can afford to lose in a disaster. That might be 15 minutes’ worth, eight hours’ worth or none.
  • RTO is how fast you can get everything back up and running. Can you afford to have your system down for 15 minutes, for an hour, half a day? An RTO of zero will dictate a combined HA and DR solution.

There are many other details to consider as well.  Do you want automatic failover? Availability Groups is a good solution. Infrastructure matters, too.

While it’s certainly possible for an in-house IT organization to handle the complexities of an HA/DR implementation, it’s not always the best use of their time.  Unless they have direct experience with HA/DR and SQL Server, they might be spinning their wheels instead of dedicating valuable insight and direction to more strategic IT projects.  A professional HA/DR managed services provider can evaluate your options quickly and accurately, and can share best practices for ensuring that your data and your business are well protected from potential disruptions.

 

mike-d-linkedin-pic

Mike Donaghey has over 15 years of information technology experience specializing around SQL Server and managing database administrators. He has worked in a variety of industries including healthcare, marketing, financial and manufacturing. As the TierPoint DBA Services Manager, Mike is instrumental in helping to grow and develop his team’s contribution levels and skill sets. During his tenure, Mike has led multiple large scale projects around integration, migration and new deployments.