Call out to Distributed Always On Availability Groups

  • Article

 

We have all been introduced and hopefully implemented Always On Availability Groups, a feature that has been around since SQL Server 2012. Since then, this feature has evolved greatly by increasing the number of nodes, failover nodes and more importantly seeding and recovery performance.

In SQL 2016, there was a neat addition that may have been cast in the shadows due to the announcements in SQL 2017 around Linux support (my humble opinion).

This feature is called Distributed Always On Availability groups.

While you can read the instructions in the above link, I personally like to think of this as the ability to simply replicate the transactions from a completely separate group into its own group.

fig 1: Distributed AG Setup

 

fig 2: The primary of AG 2 is the forwarder

 

From fig 1 and fig 2, we can see that the Primary in AG 2 (known as the forwarder in the distributed AG) can receive replicated transactions from the Primary on AG 1 and pass it to its own Secondary on AG 2. Note that AG 1 and AG 2 have these properties:

  • do not have to be on the same network/VLAN
  • do not have to be in the same WSFC
  • Failovers only support the FORCE_FAILOVER_ALLOW_DATA_LOSS options
  • Supports only Enterprise edition – no mix of Standard + Enterprise
  • As of this post, only SQL 2016 + SQL 2017 is supported

 

The use cases for this feature are quite powerful, they include:

  • Disaster Recovery and simpler multi site configure
  • Since this is mostly SQL based, Migration of new hardware or OS is a great feature
  • Replace scenarios where Log shipping may have been used in the past
  • Extend your AG group to beyond 8 replicas in a single AG.
  • Extend your on premise to cloud architecture easily

A quick caveat is that you cannot as of this writing manage Distributed Availability Groups from SQL Server Management Studios. You can check from DMVs. Specifically look at sys.availability_replicas and sys.availability_groups. There is a great blog here.

I hope this gives you a good primer on the Distributed Availability Group feature.  Additional information around configuration of the Distributed AG may be found here.