Always On availability group

(Thanks to StarWind) 

Always On availability groups is a mechanism that creates a replicated environment (High Availability or Disaster Recovery depending on the availability mode) for a set of databases you specify. These databases are called Availability Databases. Hence, since they are in a group, such databases failover together and at the same time.

AG operates on a database level and each set of availability databases is hosted by two types of availability replicas: primary and secondary. Primary replica provides read and write access to the database and a set of up to eight secondary replicas is available to become primary in case of a failover. We’ve mentioned availability mode above and this basically determines if you’re going to build HA or DR for your databases as well as determines the failover options.

There are two types of availability mode: asynchronous-commit mode and synchronous-commit mode. To put it simply, with asynchronous, some data loss is possible, with synchronous – no data is lost but at the cost of higher transaction latency. It all depends on your data significance (RPO in other words).

 

AG requires Windows Server Failover Cluster (WSFC). Each availability replica is hosted on a separate node in WSFC and for each AG, a separate cluster role is created. There is no witness role in Always On availability groups as it was with Database Mirroring. The quorum now depends on the number of nodes in WSFC and all nodes take part, it doesn’t matter if they host replicas or not.

 

Benefits of Always On Availability Groups

The benefits of using Always On availability groups are probably obvious, but still, let’s summarize them:

·         AG supports up to 9 availability replicas (one primary and up to eight secondary);

·         You are flexible with failover: planned manual failover or automatic failover (if we’re talking about synchronous-commit mode with no data loss) and forced failover in case of asynchronous-commit mode where data loss is possible;

·         In order to make more use of your secondary replicas instead of just let them be and wait for a failover event, you can set them to Active and allow for example to perform backups from them or allow read-only mode to distribute the workload;

·         Supports encryption and compression;

·         Provides Always On Dashboard for monitoring Always On availability groups, availability replicas, and availability databases.

Restrictions of Always On Availability Groups

Of course, there are certain considerations or, better to say, restrictions to be taken into account when working with AG.

·         Availability replicas must be hosted by different nodes within the same WSFC;

·         You can use asynchronous-commit mode for all the replicas (one primary and up to eight secondary) or only up to five replicas (including primary) in synchronous-commit mode;

·         Failover Cluster Manager should not be used to move availability groups to other nodes or for failover;

·         SQL Server logins, linked servers, agent jobs etc. are not synchronized to the secondary databases;

lkk