Microsoft SQL Server High Availability Technologies

MicrosoftWe offer several high-availability solutions for Microsoft SQL Server, which differ in the level of initial and ongoing investments and reliability. Note that high-availability is not a replacement for either data backup, replication or load-balancing technologies, which must be used in addition to high-availability if needed.

Log Shipping

The simplest approach to the high availability is Log Shipping. It requires 2 SQL Server instances, each Web Edition or higher. This process copies transaction logs of the specific databases from the primary to secondary server. The maximum data loss time depends on the transaction log backup interval. The fail over to the replica is manual and requires reconfiguring both the SQL Server and the clients. If the version of both SQL Server instances is identical, the replica database can be used in a read-only mode as a reporting source thus adding the load balancing capabilities to the configuration.

Mirroring

While Log Shipping provides a decent level of protection, there are two serious drawbacks mentioned earlier. There is a lag between synchronization of the primary and replica database, and the fail over requires manual intervention. Both are addressed in Mirroring. The changes in the Principal database are replicated to the Mirror database in real time. In addition, a third installation of SQL Server is set up as a Witness, making the fail over completely automatic and virtually invisible for the clients. Mirroring requires at least two Standard Edition SQL Server instances (the Witness can be an Express edition). Finally, Mirroring must be set up on a per database basis. Mirroring does not allow using the Mirror database as a reporting source.

ARCserve HA

Mirroring works fine when the number of databases is limited as it is setup on per-database basis. If the databases need to be added/removed often, it is better to have an instance replication. In this scenario, we recommend using CA ARCserve HA (previosly known as XOsoft). This software works with any edition of SQL Server and does an application-aware file replication, which means that it assures that the data files on the Replica are in a consistent state and the Replica SQL Server instance can be brought up and start using those files in case the Master goes down. Unlike Log Shipping and Mirroring this technology requires the Master and Replica instances to have an identical drive layout configurations.

Summary

The following table summarizes the strengths and weaknesses of each approach.


Log Shipping Mirroring Mirroring with Witness ARCserve HA
Real-time No Yes Yes Yes
Automatic Failover No No Yes Yes
Cost (SQL Server + Additional) Low, One-time Medium, One-time Medium, One-time High, Subscription
Load Balancing Limited, requires identical versions No No No
Minimum # of Nodes 2 2 3 2

Go back