This is a high-level description of the different High Availability Offerings in SQL Server 2016, meant to give a general idea of what is offered and the limitations for each offering.

AlwaysOn High Availability Groups

When most people use the terms AlwaysOn they are referring to AlwaysOn Availability Groups. This is a hybrid of the Windows Clustering technology and Database Mirroring. There are some very valuable features with this HA Technology, but it does come with a cost. This technology was introduced to address many of the user complaints about the limitation of Database Mirroring being for a single database and the need to program applications to have checks for server availability as well as the limitations of Windows\SQL clustering all or nothing approach.

Features of AlwaysOn High Availability Groups

Grouping of Databases

The ability to group 1 or more databases together to use the same connection and to ensure if one database needs to fail over to a replica, all databases in the group will fail over to the replica. This technology addresses the limitation of database mirroring being defined on a single database. And the limitation of Windows Clustering grouping all databases together causing all to failover even if only one database had an issue.

Listeners

This is an Alias created in DNS which will be updated automatically to point to the primary server during the failover process. No longer will application connection strings have to be defined to check to see which server is primary or secondary. All Applications will use the Listener Name in the connection string as the source of the data.

Read Only Replicas

With Read Only Replicas you can define connection strings in applications to use the read only replica to help offload workload from the primary databases. This is mainly used for Reporting Environments and Backups. In the past, users would define replication topologies to address the issue of offloading reports to a different server. With SQL 2016, you can have more than one secondary read-only replica where SQL will load balance which server you can read from.

Requirements

SQL Server Enterprise Edition is needed for this feature

No shared storage

Unlike AlwaysOn Failover Cluster Instances, AlwaysOn Availability Groups require separate storage. This eliminates the single source of failure experienced in traditional Windows\SQL clusters. If you lose a disk array, the system will fail over to one of the secondaries which has its own disk array.

Windows Clustering Failover Service configured to manage the listeners.

AlwaysOn Failover Cluster Instances

This IS Windows and SQL Server clustering many of us have used for years just with a “new” name. Instead of being called Windows cluster, SQL cluster, it is AlwaysOn Failover Cluster.

Cost is the main factor in choosing this option. SQL Server Standard is supported for 2 nodes. If more than 2 nodes are required, then SQL Server Enterprise Edition is still needed.

Shared storage is still needed for this feature. This can be beneficial from a cost standpoint, but it can also become a single source of failure.

Basic Availability Groups

Introduced in SQL Server 2016, this technology replaces Database Mirroring. It is supported in SQL Server Standard Edition but not Enterprise Edition. It does give you the ability to define a Listener Name which addresses the limitation of the old Database Mirroring technology of having to have 2 connection strings.

The limitation is only 1 database can be defined in the group. So if more than one database needs to failover, manual intervention or scripts would have to be put in place.

This technology also requires separate storage like the traditional database mirroring.

Microsoft has given us 3 very useful options to address different High Availability needs with cost as a consideration. Knowing the different options will help in making an informed decision —which technology meets your requirement for each system you implement.

Share This