Show TOC

Comprehensive Microsoft SQL Server High-Availability SolutionLocate this document in the navigation structure

Use

You can combine the following products from Microsoft to achieve a comprehensive high-availability solution:

For more information about the different solutions, see the SQL Server Books Online.

Integration

You can use either use each high-availability solution separately or you can combine them. For more information about how to set them up, see the SQL Server Books Online.

The following graphics give examples for various combinations:

Note

“(A)SCS” means either the ABAP central services instance (ASCS) or the Java central services instance (SCS).

Figure 1: Windows Server Failover Clustering and Database Mirroring
Figure 2: Windows Server Failover Clustering and Log Shipping
Figure 3: Log Shipping and Database Mirroring
Features

Each high availability solution has its own strengths and weaknesses, as shown in the following table.

This might help you to decide which solutions suits you best and whether you want to use them separately or combine them. For more information , see also the SQL Server Books Online.

Strengths

Weaknesses

Database Mirroring

  • Provide a mirrored database that is similar to the production database.

  • Offers a simple alternative solution to failover clustering.

  • Requires no special hardware

  • Enables the principal and the mirrored servers to be in geographically separate locations.

  • Only one mirrored database is possible. Unlike log shipping, in which transaction logs can be shipped to multiple servers, it is not possible to mirror a database to two or more different servers.

  • The mirrored database is always immediately updated. This means that errors in the principal database are immediately transferred to the mirrored database. You cannot have a time lag when applying the transaction logs to the mirrored database, to prevent errors being replicated in the mirrored database.

  • Requires good network infrastructure and bandwidth

Standby Database

  • Prevents data loss

  • Physically remote units

  • Separate and distinct SQL server and database instances

  • Separate disks and other key system resources

  • Good disaster recovery solution

  • Standby node can be used for read-only access

  • Requires manual intervention following failure

  • Not automatic

  • Slow recovery following failure

Windows Server Failover Clustering

  • Increases availability by reducing unplanned downtime

  • Rapid failover

  • Automatic failover

  • Good solution for frozen operating system or application

  • Shared disk is potential single point of failure (SPOF)

  • Not normally geographically separate

  • Transaction rollback to point in time not provided

  • Spare resources kept in waiting status