SQL Server High Availability Solutions Overview

This post discusses about high-availability solutions for SQL servers databases. High Availability solution addresses hardware or software failure and maintains the availability of applications with minimum downtime to users. It also discuss the considerations for choosing the available options.

Options for High Availability solution for a SQL Server or Database are

•    Failover clustering A Failover cluster is a combination of one or more nodes or servers with two or more shared disks. Cluster contains resource groups and each resource group is owned by only one node in cluster. Application connects to Failover cluster instance by referring it’s name.

SQL Server Failover cluster name appears in the network as a single computer and it provides failover from one node to another if the current node becomes unavailable.

Failover Cluster does protect against non-disk hardware failure, Operating System Failure or planned upgrades.
Clustering Requirements for Virtual Machines

Software:  SQL Server 2008 R2, at least Windows Server 2008 R2
Two virtual machines on two different physical machines (ESX hosts) run clustering software
Networking:  The virtual machines share a private network connection for the private heartbeat and a public network connection
Storage:  Each virtual machine is connected to shared storage, which must be on a SAN

•  Database Mirroring is primarily software solution to increase database availability by supporting instantaneous failover. Database mirroring configuration involves a principal server that contains the principal database, and a mirror server that contains the mirror database. The mirror server continuously brings the mirror database up to date with the principal database.

In its simplest configuration, database mirroring involves only the principal and mirror servers. In this configuration, if the principal server is lost, the mirror server can be used as a warm standby server, with possible data loss. High-safety mode supports an alternative configuration, high-safety mode with automatic failover. This configuration involves a third server instance, known as a witness, which enables the mirror server to act as a hot standby server. Failover from the principal database to the mirror database typically takes several seconds.

•    Log Shipping Operates at database level, you can use Log shipping to maintain one or more standby databases for a corresponding production database also referred as primary database. Standby databases are referred as secondary databases. Each secondary database is created by restoring a database backup of the primary database with no recovery, or with standby.

A log shipping configuration includes a single primary server that contains the primary database, one or more secondary servers that each have a secondary database, and a monitor server.

Each secondary server updates its secondary database at set intervals from log backups of the primary database. Log shipping involves a user-modifiable delay between when the primary server creates a log backup of the primary database and when the secondary server restores the log backup. Before a failover can occur, a secondary database must be brought fully up-to-date by manually applying any unrestored log backups.

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
•    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.

References http://msdn.microsoft.com/en-us/library/ms190202.aspx
Good reference to make a decision on clustering sql server
http://sqlskills.com/blogs/jonathan/post/Some-Thoughts-on-Clustering-SQL-Server-Virtual-Machines.aspx
Documentation on clustering SQL server 2008 r2 can be found in below link
http://www.ipsure.com/blog/2010/clustering-ms-sql-server-2008-r2-on-vmware-virtual-windows-server-2008-r2-3/

Share this post :