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
    

    Note:
    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.
    

    Note:
    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.