• msurasky

SQL Server 2016 Series – High Availability

Time to look at higher availability, another area with a few improvements in SQL Server 2016

AlwaysOn Availability Groups

AlwaysOn Availability Groups, first introduced in SQL Server 2012 is a feature that is conceptually similar to database mirroring. I’m going to assume you already know what AlwaysOn Availability Groups are in general, their main purpose and how they are different (essential aspects at least) from other technologies to provide replication such as Database Mirroring.

In SQL Server 2014, the significant enhancement to availability groups was the increase in the number of supported secondary replicas from three to eight. SQL Server 2016 includes a number of new enhancements

  • You can now use basic availability groups in the Standard Edition to automatically fail over a single database. As I usually have said in previous articles, you can find the nitty-gritty details of restrictions and rules to implement this basic availability groups in other articles online, there is no point that I re-iterate the same here.

  • It is now possible to use Group Managed Service Accounts (gMSA) with both availability groups and failover clusters. If you don’t know what gMSA are, I’ll summarize in a line saying they are “managed domain accounts that provide automatic password management”, but you can read more about them here

  • In versions earlier than SQL Server 2016, the availability check that triggers a failover in AlwaysOn Availability Groups does not account for database-level failures. Beginning in SQL Server 2016, you can enable Database Level Health Detection when you create an availability grous. This way, any error that causes a database to be suspect or go offline also triggers a failover of the availability group.

  • Distributed Transaction Coordinator is required if your application performs transactions that must be consistent across multiple instances. When running SQL Server 2016 on Windows Server 2016, you can now implement support for distributed transactions when you create a new availability group.

  • There have been claims of improved throughoutput between replicas by means of better use of multi-thread processes and improved compression of data being replicated and also that the underlying processes responsible for synchronizing data between replicas were completely rewritten in SQL Server 2016. I have not come across any studies doing serious comparison of solutions pre and post SQL Server 2016 but there is no reason to think this is just marketing blub, I would think that 4 years is enough in technology to anticipate much better Always On performance in this new edition. Proving this (let alone testing it or measuring the improvement) is another story…

Introducing site-aware failover clusters in Windows Server 2016

This is not strictly a SQL Server feature, but SQL Server relies on clustering ability for Windows Server operating system for many of its functions and there are many Windows Server 2016 improvements that SQL can use. Windows Server 2016 Technical Preview introduces site-aware clusters. As a consequence, you can now group nodes in stretched clusters based on their physical location. This capability enhances key clustering operations such as failover behavior, placement policies, heartbeat between nodes, and quorum behavior.

One of the key features of interest to SQL Server professionals is failover affinity, which allows availability groups to fail over within the same site before failing to a node in a different site. Additionally, you can now configure the threshold and site delay for heartbeating, which is the network ping that ensures the cluster can talk to all its nodes.

In Summary

It appears that there are no radical changes here, as far as I have seen (so far) high availability in SQL 2016 has no changed much since the last incarnation, at least not the types of changes we have seen in the past (for example, when migrating from SQL Server 2008 R2 to SQL Server 2012, when AlwaysOn was introduced).

Having said that, the improvements available to SQL Server 2016 are probably going to be welcomed by those who, for any reasons, have been waiting for solutions to fail over single databases with AlwaysOn, have Domain-Independent Availability Groups, or make use of the increased number of auto failover targets which seems to be the most interesting new features on this field for this new edition.

As for the performance improvements when it comes to log transport I assume this will benefit those with bigger infrastructures being supported by AlwaysOn having lots of transactions being created that can potentially be slowing down response time.

20 views0 comments

Recent Posts

See All

©2019 by SQL Corner. Proudly created with

This site was designed with the
website builder. Create your website today.
Start Now