What is AlwaysOn Availability Group

SQL Server Always On is just a term given by Microsoft for Availability Group & Failover Cluster Instances that are used to achieve HA, DR & Scale out solution. Always On Failover Cluster Instance is same which we call SQL Server Cluster Instances in earlier versions. Microsoft has renamed SQL Server Cluster Instance to Alwayson Failover Cluster Instances (FCI) to refer SQL Server Clustering solutions.

SQL Server Always On Availability Group is new concept introduced in SQL Server 2012. We generally refer Always On Availability Group as AOAG. AOAG is an advanced version of Database Mirroring technology.

As name suggests Availability Group is a group or set of databases that can be failed over together as one entity. Failover of AOAG will not occur if something will happen at the database level like database suspect due to a loss of a data file, deletion of a database, or corruption of a transaction log.

Terms used in AOAG

Availability Mode

The availability mode decides the mode of the data transfer between primary replica and its respective secondary replicas. AOAG supports two availability modes:

  • Synchronous-commit mode
  • Asynchronous-commit mode

Under Asynchronous-commit mode, the primary replica commits transactions without waiting for acknowledgement that an asynchronous-commit secondary replica has hardened the log whereas in synchronous-commit mode, before committing transactions, a synchronous-commit primary replica waits for a synchronous-commit secondary replica to acknowledge that it has finished hardening the log.

Availability Databases

Availability databases are those databases which are grouped as or part of availability group. We can categorize availability databases in to two types. Primary database that hosts on primary replica and secondary databases that host on secondary replicas.

Availability Replicas

Availability Replicas are those SQL Server instances that host the availability databases and are of two types.

  • Primary Replica
  • Secondary Replicas

Primary replica hosts primary databases of availability group where read-write operations will be performed and secondary replicas hosts secondary databases which will be synchronized with the primary databases hosted on primary replica. Secondary databases/replicas can be used for read operation and database maintenance like backups to offload the workload of primary replica.

Types of Availability Groups

There are three variant of Availability Groups that can be configured to achieve HA, DR or Scale-out solution.

  • AlwaysOn Availability Group
  • Distributed Availability Group
  • Read-scale Availability Group
AlwaysOn Availability Group

An availability group supports one set of primary databases and upto eight sets of corresponding secondary databases. Each set of availability database is hosted by an availability replica.

There is a single primary replica that hosts the primary databases and up to eight secondary replicas, each of which hosts a set of secondary databases and serves as a potential failover targets for the availability group. Data synchronization occurs between the primary database and each connected secondary database, independently of the other databases.

AOAG also has a feature to off load read only transactions from primary databases to secondary databases. We can also run backups on secondary replicas to reduce the backups overhead.

basic alwayson

Distributed Availability Groups

Alwayson Distributed availability group was introduced in SQL Server 2016. Distributed availability groups are little different than basic AOAG. A distributed availability group can be configured on top of two separate availability groups and on two different Windows Server Failover Clustering (WSFC) clusters.

We can configure two AOAG, one in each data center with two replicas and then we can configure distributed availability group between these two AOAGs.

Data movement in distributed availability groups can be configured either by synchronous or asynchronous mode. You can see the below image to understand the working of distributed availability group.

In below image, AG 1 is the primary availability group. Its primary replica sends transactions to both the secondary replicas of AG 1 and the primary replica of AG 2. The primary replica of AG 2 then keeps the secondary replicas of AG 2 updated.
distributed Availability group

Read-scale Availability Group

Read-scale Availability Group is introduced in SQL Server 2017. A Read-scale availability group provides replicas for read-only workloads but this availability groups does not provide high availability solution.

WSFC is not required to configure read-scale availability group. This is one of the benefit over basic AOAG configurations where we can use secondary replicas for read only workloads.

If you don’t need any HA or DR configuration and you want to offload your databases from read only transactions, then this is best solution because you don’t need to go with cluster setup. You can configure read-scale availability group without cluster configuration.

Read More:

I hope you like this article. Please follow us on our facebook page and on Twitter handle to get latest updates.

Follow me:

Manvendra Deo Singh

I am working as a Technical Architect in one of the top IT consulting firm. I have expertise on all versions of SQL Server since SQL Server 2000. I have lead multiple SQL Server projects like consolidation, upgrades, migrations, HA & DR. I love to share my knowledge. You can contact me on my social accounts for any consulting work.
Manvendra Deo Singh
Follow me:

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *