Show TOC

Function documentationComprehensive Microsoft SQL Server High-Availability Solution Locate this document in the navigation structure

 

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 Note

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

End of the note.

This graphic is explained in the accompanying text.

MSCS and Database Mirroring

This graphic is explained in the accompanying text.

MSCS and Log Shipping

This graphic is explained in the accompanying text.

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.

Comprehensive Microsoft High Availability Solution with Database Mirror, Standby Database and MS Cluster

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

MSCS

  • 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