Know about AlwaysOn Availability Groups in SQL Server

Andrew Jackson | February 5th, 2016 | High Availability | February 5th, 2016 |

Overview

The Availability Groups were first introduced in SQL Server 2012 replacing the prior Database Mirroring techniques for configuring High Availability on SQL Server databases. The AlwaysOn Availability Group Feature was designed in SQL Server to meet the ever-increasing need for ‘High Availability’. The AlwaysOn Availability Groups is a high availability and disaster recovery solution that acts as an alternative to database mirroring. It allow user to configure groups of databases that can fail over together if there is issue in the host server.

The AlwaysOn Availability Groups in SQL Server can be used for multiple databases improving cross-database referencing during Failover while Traditional mirroring & clustering method works only for a single database. Another benefit is that user can create multiple failover targets, which is not possible in Database mirroring as it allows only one failover partner. Multiple replicas of an availability group in SQL Server can be generated allowing administrator to make one of them as read only, which can be used for reporting purpose.

More about AlwaysOn Availability Groups

An Availability Group in SQL Server supports a failover environment for a set of user databases, known as Availability Databases that fail over together. It supports set of primary databases and one to eight sets of corresponding secondary databases. Each Availability Database set is hosted by an Availability Replica. There are two types of Availability Replicas- Single Primary Replica that host the primary databases and One to Eight Secondary Replicas, each of which hosts a set of secondary databases. It acts as potential failover targets for the availability group in SQL Server. An Availability group fails over at the level of an availability replica.

Primary Replica makes primary database available for read-write connections from clients. The process of Data Synchronization occurs at database level where primary replica sends records of transaction log of each primary database to every secondary database. Transaction log records are cached by every secondary replica and are applied to its corresponding secondary database. Hence, failure of one secondary database will not affect other secondary databases and similarly for primary database.

How to Deploy AlwaysOn Availability Groups in SQL Server

A Windows Server Failover Clustering (WSFC) is required to deploy AlwaysOn Availability Groups. Every Availability Replica of given availability group must reside on a different mode of same WSFC cluster. The cluster will monitor WSFC Resource group created for each availability group in SQL Server made to check the primary replica’s health.

The Above example shows an availability group in SQL Server having one primary replica and four secondary replicas. The WSFC Resource Group is created for ‘MyAg’ Availability Group. All replicas stay at different nodes of the cluster.

Terminologies used in AlwaysOn Availability Groups

Availability Databases The database that needs to be added to the Availability Group in SQL Server should be online, read-write database that exists on server instance and host the primary replica. Once the database is added, it is joined as a primary database in the availability group. No secondary database is created until backup of new primary database is restored to server instance that host secondary replica. Availability Replicas An Availability Group in SQL Server defines sets of two/more failover partners known as availability replicas. A copy of availability database is hosted by every Availability replica. Replicas must be hosted by different instances on SQL Server residing on various nodes of WSFC cluster for a single Availability group. Role of primary replica is to host read-write primary database & secondary replica for read only Secondary database. Availability Modes The task of Availability mode in Alwayson Availability Group is to check if the primary replica waits to commit transactions on a database until secondary replica has written the records of transaction logs to disk. It has two modes: Asynchronous-Commit Mode- Asynchronous-commit replica is the availability replica in SQL Server that uses asynchronous-commit mode. In this mode, the primary replica commits transactions without the acknowledgement that an asynchronous-commit secondary replica has written transaction log records to disk. Synchronous-Commit Mode- Similarly, availability replica using synchronous-commit mode is called synchronous-commit replica. In this mode, the primary replica commit transactions after waiting for the acknowledgement that synchronous-commit replica has written transaction log records to disk. Failover types The role of primary and secondary replica can be interchanged in a method called failover. During a failover, the target secondary replica transitions to the primary role making the new primary replica. After that, the new replica database makes its database online like primary database so that client applications can connect to it. The former primary replica transitions to secondary role when it is available making it secondary database. The process of data synchronization continues.

Types of Failover are as follows-

Planned Manual Failover (No data loss): It occurs after a failover command is issued by database administrator and cause a transition from synchronized secondary replica to primary role & from primary replica to secondary role. Automatic Failover (No data loss): It happens in response to a failure causing transition of synchronized secondary replica to primary role and from primary replica to secondary role. Forced Failover (with possible data loss): It is the type of failover that may lead to loss of data. It can be initiated manually and can act as disaster recovery option.

Limitations on Using The WSFC With Alwayson Availability Groups

Windows Server Failover Cluster Manager should not be used for manipulating Alwayson Availability Groups such as: