Database Reference
In-Depth Information
Table 31-2.
Comparison of SQL Server High Availability technologies
Failover Cluster
Log Shipping
Database Mirroring
AlwaysOn AG
Replication
SQL Server
version
2005-2014
2005-2014
2005-2014
Deprecated in
2012-2014
2012-2014
2005-2014
Standard edition
support
2 nodes only
Supported
Synchronous only
Not supported
Supported
Unit of
protection
Instance
Database
Database
Group of
databases
Publication
(Subset of data)
Data Loss
No data loss
Data loss based
on log backup
schedule
No data loss with
synchronous
mirroring
No data loss with
synchronous
commit
Data loss based
on latency
Single point of
failure
Storage
No
No
No
No
Failover
Automatic
Manual
Automatic
(Requires witness)
Automatic
Manual
Failover time
(best-case
scenario)
Minutes (crash-
recovery of all
databases in the
instance)
N/A
Seconds
(crash-recovery of
a single database)
Seconds
(crash-recovery
of all databases
in AG)
N/A
Performance
overhead
No overhead
No overhead
Overhead of
synchronous
commit
Overhead of
synchronous
commit
Additional load
to transaction
log
Obviously, you are not restricted to the use of a single high availability technology. It is often beneficial to combine
technologies, using a few of them together to be protected from different kinds of failures. For example, if AlwaysOn
is not an option due to SQL Server version or edition incompatibility, you can use a Failover Cluster together with
Database Mirroring or Log Shipping. A Failover Cluster will protect you from a server malfunction, while the second
technology protects you against a storage system failure.
In cases where data loss is not allowed; the choices are limited either to Database Mirroring or AlwaysOn
Availability Groups with synchronous commit. Even though a Failover Cluster uses a single copy of the database,
and therefore you cannot lose data due to replication (or synchronization) latency, it is not protected against storage
failure. Unfortunately, synchronous commit could introduce unacceptable latency in some of the edge-cases.
■
this is one time where you need to work with the stakeholders and reach a compromise. For example, in some
cases, it could be
good enough
to have a Failover Cluster with the data stored on a highly redundant disk array with
asynchronous Data Mirroring to another server.
Tip
The unit of protection is another very important factor to consider. If AlwaysOn is unavailable, synchronous
Database Mirroring could be a great option that guarantees zero data loss and does not have a single point of failure.
However, it works within the scope of a single database, which could be problematic if the system consists of multiple
databases that should reside on the same server. A Failover Cluster is the only option besides AlwaysOn, which
guarantees that multiple databases will always fail over together.
