Aaron Bertrand

SQL Server 2016 : Availability Group Enhancements

December 17, 2015 by in SQL Server 2016 | 45 Comments
SentryOne eBooks

In these books, you will find useful, hand-picked articles that will help give insight into some of your most vexing performance problems. These articles were written by several of the SQL Server industry’s leading experts, including Paul White, Paul Randal, Jonathan Kehayias, Erin Stellato, Glenn Berry, Aaron Bertrand, and Joe Sack.

Free Download

Featured Author

Paul White is an independent SQL Server consultant specializing in performance tuning, execution plans, and the query optimizer.

Paul’s Posts

At MS Ignite this week, several details were revealed about the changes in Availability Groups that will ship in SQL Server 2016. I wanted to provide a very quick list of bullets on the major highlights at a high level, to get you excited about these AG enhancements:

  • Optional setting to fail over based on database failure – in 2012 and 2014, failover is determined almost entirely at the instance level. If a database goes offline, suspect, or corrupt, the AG keeps humming along. In SQL Server 2016, you will be able to have certain database metrics to initiate failover for the entire group.
  • Distributed Transaction Coordinator support – in current versions, MSDTC is not supported for AG databases, but it will be fully supported in SQL Server 2016 (it will require an operating system update as well – it is possible that you will need the most recent version of Windows Server for full support across all scenarios).
  • Group Managed Service Accounts are fully supported – these "worked" in SQL Server 2012/2014, but were not fully supported, and had some issues (see background information here, here, and here).
  • Load Balancing for Readable Secondaries – you will be able to use a round-robin mechanism for routing read-only requests through the listener to take balanced advantage of all secondaries, versus the current approach of requests always going to the "first" available secondary.
  • Additional automatic failover targets – you'll be able to specify up to three total secondaries for automatic failover; this matches the number of synchronous replicas allowed.
  • Improved log transport performance – this entire pipeline was overhauled and refactored for lower CPU usage and higher throughput.
  • Basic Availability Group – this has finally been confirmed as of CTP 3.2 to be an official option for Standard Edition customers in SQL Server 2016. For feature details and limitations, see Overview of AlwaysOn Basic Availability Groups .
  • Domainless Availability Groups – as Microsoft has described here, you will be able to host AGs across domains with no trust and with no domain at all. (Note that this change requires Windows Server 2016 as well.)