SQL Server Disaster Recovery VS. High Availability (Update)

published: 2015-10-07 12:36

SQL Server offers many disaster recovery and high availability options. However, before we discuss the different options available it’s worth talking about the differences between disaster recovery and high availability implementations.

Disaster recovery can be categorized as the failure of multiple servers often separated by distance. Often implementing a disaster recovery plan is a manual process resulting in a period of downtime. The amount of downtime will generally depend on the personnel involved and whether the failover process is well documented and practiced. Obviously costs also play their part but a well planned and executed failover should not incur significant expenditure. The intention of a disaster recovery plan is to:

  • retain data
  • minimise downtime
  • minimise data loss

High availability differs from disaster recovery as it is often an automated process involving fewer servers. Failover often happens within the same data centre. The intention is:

  • to retain service
  • 100% uptime
  • zero data loss

Disaster Recovery: Log Shipping

Performed at a database level and available in both standard and enterprise editions, this is a tried and trusted disaster recovery implementation that has been an option within SQL Server for many years. It copies logged operations from a primary to a secondary, often offsite location. It consists of three main operations; backup of the transaction log, copy the transaction log backup from source to destination and restore the transaction log at destination. The amount of potential data loss experienced is dependent on the configuration options chosen. For example, how often do you backup, copy and restore the transaction logs? Generally, failover is a manual process.

High Availability: Clustering

Clustering is performed at an instance level. It is not designed to protect data but rather to protect the availability of server hardware. Unlike AlwaysOn Availability Groups, SQL Server clustering uses shared storage which exposes it to SAN failures. As a result, clustering is often used with other disaster recovery technologies such as log shipping. Unlike most other options, when failover occurs, all SQL Server logins, jobs etc. failover.

RockSolid Tip: RockSolid SQL have noticed that, since the introduction of SQL Server Always On Availability Groups, most of our customers have moved away from shared disk clustering in favour of the shared nothing architecture provided by SQL Server AlwaysOn, if supported by the version and edition of SQL Server deployed.

Disaster Recovery or High Availability?

Database Mirroring

One important point to note is that database mirroring is expected to be removed as an option in future releases of SQL Server. It has been replaced by AlwaysOn Availability Groups. Database mirroring is performed at a database level. A database can be configured to use mirroring for high availability (synchronous) or disaster recovery (asynchronous), but not both.

  • Synchronous mirroring is a high availability option and is often referred to as high safety. Transactions need to be committed at both the principle and the mirror increasing latency. However, as high availability options are often configured within the same physical location this should not result in major performance problems. Additionally, an optional witness can be configured, enabling automatic failover.
  • Asynchronous mirroring is a disaster recovery option and is often referred to as high performance. It sends logged transactions to the mirror without waiting for the mirror to write the log to disk. This allows the principle to run with minimum transaction latency and is especially useful as often disaster recovery sites are separated by distance. The amount of potential data loss will depend upon the network latency between your principle and mirror database. Failover should be a manual process. You would not configure the optional witness as failover in mirroring is only possible when using a synchronous configuration.

Async AlwaysOn Availability Groups (Starting With Sql Server 2012)

It has been described by Microsoft as the next evolution of database mirroring, which is great, but it comes at a cost. Synchronous database mirroring is available in the standard edition of SQL Server, whereas in SQL 2012 & 2014, AlwaysOn is an Enterprise Edition feature only (limited use may be available in SQL 2016 Standard Edition). It uses Windows clustering technology to manage the network name and the IP address.

However, one important point to note is that it doesn’t use shared storage, unlike traditional clustering. It uses a listener which is like a virtual network name that determines which SQL instance clients connect to. There is no need to reconfigure clients to point to a different SQL Server instance name as this does not change during failover. In SQL Server 2012 Enterprise Edition, you can configure 1 primary and 1 to 4 secondary replica groups. In SQL Server 2014 this has been increased from 4 to 8 secondary replicas. These groups can contain up to 100 databases. A SQL Server instance can contain many replica groups but a particular database can only be a member of one primary replica. Unlike database mirroring, groups of databases can be failed over rather than a single database. Additionally, replica groups can be used in both synchronous and asynchronous configurations.

So for example you could have;

  • One SQL Server instance name
  • A primary replica group containing databases for read-write operations
  • A secondary replica group for synchronous, high availability failover
  • A third replica group for asynchronous disaster recovery failover
  • And a fourth replica group for reporting purposes

We think AlwaysOn Availability Groups are a great leap forward and is major step towards offering a high availability and disaster recovery solution in one.

SQL Server AlwaysOn Availability Groups

Replication

Some of you may be wondering why SQL Server replication isn’t listed above? Although, SQL Server replication can be used as a disaster recovery / high availability solution, what we’re finding more and more is that this implementation is often used for reporting purposes. For example more and more clients are distributing load by separating their reporting from their production environments. Replication gives you the option of replicating certain articles so copies of entire databases are not necessary.

Summary

Implementing a disaster recovery and/or high availability plan will depend upon business requirements. Questions the business needs to answer include;

  • Can the business cope with downtime and if so how much?
  • Can the business cope with data loss and if so how much?
  • What budget do you have available?

Implementing a plan does come at a cost but those costs don’t need to be overly excessive. Log shipping and synchronous database mirroring are available in the standard edition of SQL Server. However, if your SQL Server instance hosts multiple databases and you require a true disaster recovery / high availability solution, AlwaysOn Availability Groups are an excellent, if not expensive alternative.

SQL Server HA/DR feature support

The following table summarises what SQL Server HA/DR features are supported in what versions:

2008 R2 2012 2014
Enterprise Standard Enterprise Standard Enterprise Standard
Log Shipping Yes Yes Yes Yes Yes Yes
Clustering Yes Yes (2 nodes) Yes Yes (2 nodes) Yes Yes (2 nodes)
Database Mirroring * Yes Yes (sync only) Yes Yes (sync only) Yes Yes (sync only)
Always On No No Yes (up to 4 replicas) No Yes (up to 8 replicas) No

* Microsoft advises customers that they are planning to remove this feature in future releases

Sean Young
Sean is an experienced SQL Server database administrator with over 10 years’ experience working with utility companies, retailers, banking and government.

RockSolid Automation Platform