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
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 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 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.
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.
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.
- how to fix AlwaysOn automatic failover issue.
- How to Upgrade or Apply Patches on AOAG Instances?
- Restore a Database participating in AOAG
- SQL Server AOAG Interview Question and Answers
Latest posts by Manvendra Deo Singh (see all)
- Understanding Hybrid Buffer Pool in SQL Server - December 30, 2018
- Fix:VIEW SERVER STATE permission was denied on object ‘server’, database ‘master’ - August 30, 2018
- SQL Server Error 5184: Cannot use file ‘I:\Path\Techyaz.mdf’ for clustered server - August 29, 2018