Selecting a High Availability Solution

Selecting a High Availability Solution

SQL Server 2008 R2

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

      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.

      For information about how to use log shipping with database mirroring, see Database Mirroring and Log Shipping.
  • 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.

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