Selecting a High Availability Solution
The following list presents considerations for selecting a high-availability solution:
Failover clustering and database mirroring both provide the following:
Automatic detection and failover
Transparent client redirect
Failover clustering has the following constraints:
Operates at the server instance scope
Requires signed hardware
Has no reporting on standby
Utilizes a single copy of the database
Does not protect against disk failure
Database mirroring offers the following benefits:
Operates at the database scope.
Uses a single, duplicate copy of the database
If you require additional copies, you can use log shipping on the database in addition to database mirroring.
Uses standard servers
Provides limited reporting on the mirror server by using database snapshots.
When it operates synchronously, provides for zero work loss through delayed commit on the principal database.
Database mirroring offers a substantive increase in availability over the level previously possible with SQL Server and offers an easy-to-manage alternative to failover clustering.
Log shipping can be a supplement or an alternative to database mirroring. Although similar in concept, asynchronous database mirroring and log shipping have key differences. Log shipping offers the following distinct capabilities:
Supports multiple secondary databases on multiple server instances for a single primary database.
Allows a user-specified delay between when the primary server backs up the log of the primary database and when the secondary servers must restore the log backup. A longer delay can be useful, for example, if data is accidentally changed on the primary database. If the accidental change is noticed quickly, a delay can let you retrieve still unchanged data from a secondary database before the change is reflected there.
Asynchronous database mirroring has the potential advantage over log shipping of a shorter time between when a given change is made in the primary database and when that change is reflected to the mirror database.
An advantage of database mirroring over log shipping is that high-safety mode is a no data loss configuration that is supported as a simple failover strategy.
For information about how to use log shipping with database mirroring, see Log shipping and Database Mirroring Interaction.
Replication offers the following benefits:
Allows filtering in the database to provide a subset of data at the secondary databases because it operates at the database scope
Allows more than one redundant copy of the database
Allows real-time availability and scalability across multiple databases, supporting partitioned updates
Allows complete availability of the secondary databases for reporting or other functions, without query recovery.