Community Event – HADR with SQL server 2016 AlwaysON – Part 2

After the discussion on the AlwaysON FCI in this post, the focus was on AlwaysON AG. The biggest USP for AlwaysON AG was – unlike AlwaysON FCI , it didn’t need any shared storage.

It’s build on Windows Cluster (For failover) and enhanced database Mirroring (for data replication) features. That’s the reason it’s most usable feature of SQL server 2012/2014.

The only caveat which we had was, the supportability of distributed and cross database transactions(However, cross database reference on the same instance is still not supported). It’s now supported on SQL 2016 AlwaysON AG hosted on only windows 2016 and windows 2012 R2 (KB3090973 installed) + WITH DTC_SUPPORT = PER_DB. Check this article for more information.

The reason of not supporting the distributed and cross databases transactions was – there was no functionality with DTC to support the AlwaysON AG cross database and distributed transaction. Therefore, either SQL database with lower DBID or the SQL instance itself used to play the role of transaction coordinator. During the failover, there were the challenges to locate the transactions for roll forward/back. This article explains this problem in detail.

Let’s move on to AlwaysON AG jargon :

1. Availability replica  -> Instances involved in AlwaysON
2. Availability Databases –> Databases being replicated for AlwaysON
3. Availability Group listener –> It’s like a SQL cluster Network Name which is used for the connection to the SQL server. if failover happens client is still able to connect without changing the connection strings.
4. Availability Mode – Synchronous and Asynchronous (Same like mirroring)

For AlwaysON setup check this article –

1. The new change in SQL 2016 AlwaysON is, now we can have 3 synchronous replicas out of 8 total replicas. Prior to SQL 2016, the count of the replicas was same but we could have 2 synchronous replicas only.

2. Due to the one additional AlwaysON synchronous replica, we can now have failover to next secondary if the first secondary is not able to bring database online due to some reason.

3. Prior to SQL Server 2016, we could route our read traffic to one secondary replica at a time. Through read only routing, we could define which secondary will take over the load if the current secondary is unavailable. Starting SQL 2016 , we can load balance the routing of the read workloads. The load will be distributed across the selected replicas in the round robin fashion.

4. Starting SQL server 2016 we have got Improved health monitoring. Now the failover will happen depending on database health degradation. In the earlier versions, it was based on instance level failover policies.

5. SQL Server 2016 support Group Managed Service accounts (gMSA).The advantage of gMSA over MSA is, it can be mapped to multiple machines and can support Kerberos authentication.


PPTs can be found here – SQL 2016 AlwaysON AG enhancements  AlwaysON Basics

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s