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

  • Manual 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

    Notes

    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

  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.

    Notes

    For information about how to use log shipping with database mirroring, see Log shipping and Database Mirroring Interaction.

• Replication

  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.

  Notes

  For information about how to use other high-availability solutions with replication, see Replication and Database Mirroring and Replication and Log Shipping.