Share via

SQL Server 2016: High Availability Enhancements - AlwaysOn Availability Group

  • Article

Always On Availability Groups feature is a high-availability and disaster recovery solution that provides an enterprise-level alternative to database mirroring.

Always On Feature is there available since SQL Server 2012 and some enhancements in SQL Server 2014 is the increased maximum number of secondaries.

SQL Server 2012 supported a maximum of four secondary replicas. Always On Availability Groups on 2016 supports up to eight secondary replicas including one primary replica and two synchronous-commit secondary replicas (no Enhancement on this compared to SQL 2014). Also use SQL Server 2016 Always On Availability Groups to provide high availability for SQL Server databases hosted in Azure, etc.

Database Level Failover Trigger SQL Server 2016 is coming which solves pains on Failover. Currently, Failover is happening based on instance health. In 2016 it can failover based on database health as well. If any DB in an availability group fails the whole Group of Database will fail over to another.

More Auto Failover Targets: Another enhancement is an increase in the number of Automatic Failover partners which means you can have two more failover partners apart from the primary. In a three node scenario, if one node fails still we can have high availability with other two nodes.

Domain Independent Availability Group: You can create an availability group in Work Group Computers. Earlier all the nodes which are participating in an availability group must be in the same domain.

Load balancing on availability group secondaries: Earlier only first replica in secondaries get all the read-only traffic even if you have more than one replica. In 2016 you can distribute the Workload for read-only transaction across multiple secondaries.

Distributed transactions are supported with Always On Availability Groups.This applies to distributed transactions between databases hosted by two different SQL Server instances. It also applies to distributed transactions between SQL Server and another DTC-compliant server.

The following requirements must be met: Availability Groups must be running on Windows Server 2016 or Windows Server 2012 R2. For Windows Server 2012 R2, you must install the update in KB3090973 available at https://support.microsoft.com/en-us/kb/3090973. Availability Groups must be created with the CREATE AVAILABILITY GROUP command and the WITH DTC_SUPPORT = PER_DB clause. You cannot currently alter an existing Availability Group.

Basic Availability Group Feature will be supported by Standard Edition of SQL Server (two replicas only and one database per group). Basic Feature won't have a readable secondary so no backup on the secondary replica, etc. For those, you have to choose advanced Availability group feature on Enterprise Edition. The whole idea behind basic Availability group is to retire Mirroring. In future, Mirroring won't be deprecated in 2016 but may be in future versions.