Always on Availability Groups

What is Always On Availability Group?

Always on Availability Group is a feature provided by SQL Server. This feature allows users to create a highly available and strong environment for databases. The way it works is that databases are copied several times on different servers. If, users’ primary database becomes unavailable, Always On Availability Group ensures the automatic transition to one of the copied databases so users won’t lose any data and can keep on going with their work.

Always On Availability Group: modes

3 different availability modes are supported by Always On Availability groups; Asynchronous-commit, synchronous-commit, and configuration-only mode.

Asynchronous-commit mode

Asynchronous-commit mode is an availability mode that’s a solution in case of an emergency recovery situation. It works best when the available replicas are distributed over significant distances. In case all secondary replicas are running under asynchronous-commit mode, the primary replica won’t wait for these secondary replicas to harden the log. Instead, straight after writing the log record to the local log file, the primary replica sends the confirmed transaction to the client.

Synchronous-commit mode

Synchronous-commit mode is an availability mode that values high availability over performance, which translates to an increasing transaction time. In this case, the confirmed transactions are sent to the client when the secondary replica has hardened the log to disk.

When data synchronization commences on a secondary database, the secondary replica initiates the process of applying incoming log records from the primary database it corresponds to. Once all log records have been securely established, the secondary database transitions into the SYNCHRONIZED state. Subsequently, every subsequent transaction undergoes hardening by the secondary replica before the corresponding log record is inscribed into the local log file.

Configuration only mode

Configuration-only mode is an availability mode that applies to certain groups that aren’t on a Windows Server Failover Cluster. Replicas created by configuration only don’t entail any user data. Configuration-only mode includes a replica master database that stores availability configuration metadata.

Always on Availability Group: Requirements

All devices participating in availability groups have to meet several requirements to support the Always on Availability Groups feature:

1. A system that’s not a domain controller.
2. All computers have to be nodes in a WSFC.
3. In order to support availability group configurations, the WSFC should contain the right number of nodes.

Which versions and editions of Microsoft SQL Server support Always On Availability Groups?

Always on Availability Groups (AG) is a feature available in certain editions and versions of Microsoft SQL Server. These versions and editions that support AG are listed below:

1. SQL Server 2012: Always On Availability Groups was introduced in SQL Server 2012. It is available in the Enterprise Edition.
2. SQL Server 2014: Always On Availability Groups remained available in SQL Server 2014 and is still limited to the Enterprise Edition.
3. SQL Server 2016: In SQL Server 2016, Always On Availability Groups is available in the Enterprise Edition, but it has also been extended to the Standard Edition with some restrictions, such as the number of databases per availability group.
4. SQL Server 2017: Always On Availability Groups is available in both the Enterprise Edition and Standard Edition of SQL Server 2017. However, in the Standard Edition there are limitations such as the number of replicas supported.
5. SQL Server 2019: Always On Availability Groups remains available in both the Enterprise Edition and Standard Edition of SQL Server 2019, with similar restrictions as in SQL Server 2017 Standard Edition.