Wednesday, 23 December 2015

SQL Server 2016 AlwaysOn Availablility Enhancements

blogger tricks

In this Article I have listed out SQL Server 2016 AlwaysOn Availability Enhancements.
SQL Server 2016 [CTP 3.2] SQL Server 2016 CTP Standard Edition now supports AlwaysOn Basic Availability Groups.
AlwaysOn Basic Availability Groups replaces the deprecated Database Mirroring feature for SQL Server 2016 Standard Edition. 
AlwaysOn Basic Availability Groups provide a high availability solution for SQL Server 2016 Standard Edition or higher.
To create a basic availability group, use the CREATE AVAILABILITY GROUP transact-SQL command and specify the WITH BASIC option (the default is ADVANCED). 
A basic availability group supports a fail-over environment for a single database. It is created and managed much like traditional (advanced) AlwaysOn Availability Groups (SQL Server) with Enterprise Edition. 
Basic availability groups enable a primary database to maintain a single replica. This replica can use either synchronous-commit mode or asynchronous-commit mode

Limitations of Basic availability groups

  • Limit of two replicas (primary and secondary).
  • No read access on secondary replica.
  • No backups on secondary replica.
  • No support for replicas hosted on servers running a version of SQL Server prior to SQL Server 2016 Community Technology Preview 3 (CTP3).
  • No support for adding or removing a replica to an existing basic availability group.
  • Support for one availability database.
  • Basic availability groups cannot be upgraded to advanced availability groups. The group must be dropped and re-added to a group that contains servers running only SQL Server 2016 Enterprise Edition. 

Load-balancing of read-intent connection requests is now supported across a set of read-only replicas. The previous behavior always directed connections to the first available read-only replica in the routing list. 
The number of replicas that support automatic failover has been increased from two to three.
Group Managed Service Accounts are now supported for AlwaysOn Failover Clusters. 
AlwaysOn Availability Groups supports distributed transactions and the DTC on Windows Server 2016.
You can now configure AlwaysOn Availability Groups to failover when a database goes offline. This change requires the setting the DB_FAILOVER option to ON
SQL Server [CTP 2.2] AlwaysOn now supports encrypted databases. The Availability Group wizards now prompt you for a password for any databases that contain a database master key when you create a new Availability Group or when you add databases or add replicas to an existing Availability Group.

Reference: BOL

Ganapathi varma
Senior SQL Engineer, MCP


  1. This is one technology that I would love to be able to use for myself. It’s definitely a cut above the rest and I can’t wait until my provider has it. Your insight was what I needed. Thanks

    Server 2016 Migration Experts

    1. yes. Most interesting feature is standard edition now supports AG group with one database part of it.