AlWaysOn Availability Group Enhancements in SQL Server 2016

• AlWaysOn Feature is there available since SQL Server 2012 and There are some enhancements in SQL Server 2014 is the increased maximum number of secondaries. SQL Server 2012 supported a maximum of four secondary replicas. AlwaysOn Availability Groups on 2014 supports up to eight secondary replicas. Also use SQL Server 2014 AlwaysOn Availability Groups to provide high availability for SQL Server databases hosted in Windows Azure etc .

• Database Level Fail over Trigger SQL Server 2016 is coming with solving pains on Failover , Currently Failover is happening based on instance health, on 2016 it can fail-over based on Database health as well. If any DB in an availability group fails the whole Group of Database will fail over to other. 

• More Auto fail-over Targets : Another Enhancement is increase in number of Automatic Failover partners , Which means you can have two more fail-over partners apart from Primary. In this 3 node Scenario , 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 is participating in a availability group must be in 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 replicas. On 2016 you can Distribute the Workload for read only transaction across multiple secondaries 

• Distributed transactions are supported with AlwaysOn 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 ( 2 Replicas Only and One Database per Group) , Basic Feature wont have readable secondary , no Backup on secondary replica etc , for those you have to choose advanced Availability group feature on Enterprise Edition. Whole idea behind basic Availability group is to retire Mirroring in Future
• Mirroring wont be deprecating on 2016 May be in future versions