SQL Server High Availability and Disaster Recovery – An Overview

This post outlines a summary of the meaning and need of High Availability and Disaster Recovery in SQL Servers. The post further explains various High Availability solutions.

What are High Availability and Disaster Recovery?

High availability refers to the process of measuring how a system remains accessible in case a system component fails. Conventionally, to implement it, multiple levels of load balancing and/or fault tolerance capabilities are built.  Disaster recovery, on the other hand refers to the process that involves SQL recovery and restoring a system to a previously acceptable state after it has experienced some man-made or natural disaster.

Need of High Availability and Disaster Recovery in SQL Server

SQL Server High AvailabilityIt is important to ensure that SQL database system has required high availability and disaster recovery capacities as per the SLA (service level agreement) requirements. Azure provides high availability mechanisms like service healing for failure recovery detection and cloud services for Virtual machines, but it doesn’t guarantee that the SLA requirements will be met. The mechanism offers high availability for Virtual Machines, but not high availability of SQL server inside VMs.

It is possible that SQL Server instance may fail while the VM is functioning perfectly and is also online. In addition, high availability mechanisms offered by Azure provide for downtime of VMs, due to events like recovery from hardware or software failures as well as operating system upgrades.

Other than that, GRS (Geo Redundant Storage) in Azure, implemented with geo-replication feature may not be enough for disaster recovery of database. Recent updates may get lost in the event of a disaster since geo-replication sends data in an asynchronous manner.

High Availability Options:

There are several options to implement high availability in SQL Server, as follows:

  • Failover Clustering

It combines multiple servers which can be within the same subnet or can also be residing on multiple subnets. An instance is installed on multiple groups, but only one server remains active at a time. Ownership of the copy of data goes to active server. In case of failure of active server, the data ownership goes to a passive node.

  • AlwaysOn Availability GroupAlwaysOn Availability Groups

It is a database mirroring solution. Here, use databases failover as a single entity. There is a primary replica where database is available for read-write. The other replicas can be used optionally for read-only.

  • Database Mirroring

This offers high availability for single user database. This technique offers availability of database by mirroring the stream of transactions from the primary server to the mirror server.

  • Log Shipping

It happens to be a cost effective solution. It functions at the level of database. In log shipping configuration, there is one primary server. The secondary servers can be one or more than that. It doesn’t offer automatic failure recovery. However, it involves user-configurable interval between log backup of transactions in the primary server as well when secondary server restores the log backup.

  • Replication

It is a publish/subscribe model. This type of configuration involves a primary server which is called the publisher. It distributes data to secondary servers which are known as subscribers. This configuration supports:

  1. Snapshot: It is applied right when the subscription is created. It can also be applied according to a predefined schedule when publication is created.
  2. Transactional: Transactions made at the publisher can be applied at the subscriber or according to scheduled intervals.
  3. Merge: It is bi-directional replication that involves incremental updates synchronized and merged at both publisher and subscriber.

Author Introduction:

Peter Song is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including outlook recovery and PDF recovery software products. For more information, visit www.datanumen.com

Comments are closed.