Export (0) Print
Expand All
9 out of 10 rated this helpful - Rate this topic

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

      NoteNote

      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.

      NoteNote

      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.

    NoteNote

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

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.