What’s the difference between Disaster Recovery and High Availability?

published: 2013-04-21 00:00

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 upon 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 does not need to 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 is available in both standard and enterprise editions. This is a tried and trusted disaster recovery implementation and has been an option within SQL Server for many years. It consists of copying 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 upon the configuration options chosen. For example how often 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 does expose 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.

Disaster recovery or High Availability

Database Mirroring

One important point to note is 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 (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 AlwaysOn is an enterprise edition feature only. It uses Windows clustering technology to manage the network name and the IP address.

However, one important point to note is that is 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. You can configure one primary and 1-4 secondary replica groups. 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.

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.