IT Services: Distributed Availability Groups in SQL Server 2016

Nate Motyl

One of the best features in SQL Server 2016 is the distributed availability group (AG).  

This new feature gives us the option to scale the synchronization across forwarder nodes instead of on the primary replica only (see Figure 1 & 2 for distributed versus non-distributed AGs).

Figure 1. Non-distributed AG.
Figure 2: Distributed AG.

As you can imagine, the introduction of the forwarder node can not only reduce congestion on the primary node but offers a clear savings on network bandwidth. This is especially important over wide area networks, where bandwidth cost is at a premium.

Another important feature of the distributed availability group is the decoupling Windows Server Failover Cluster (WSFC). In previous versions, we had to set up a stretched WSFC to span all the nodes located in both production and DR datacenters to leverage AGs. In SQL Server 2016 and above, as Figure 2 shows, we now have a choice to build separate WSFCs in any network and then configure distributed availability groups between the SQL engines. This has the effect of simplifying deployments, administration, and build times for the DBAs.

Here is a high-level overview of the implementation:

  1. Create availability groups along with listener name staged in AD
  2. Create the endpoint on all replicas and grant service account connect permission to endpoint
  3. Create distributed availability group on the primary replica of primary availability group
  4. Join the secondary availability group to the distributed availability group on the primary replica of secondary availability group (so-called forwarder)
  5. Add databases to the primary availability group and join the databases on the secondary replicas in the same availability group
  6. Join the databases on both the forwarder and the secondary replicas on the secondary availability group

More detail on AG configuration can be found here.

Considerations:

  • Firewall needs to be opened for mirroring endpoint across all replicas
  • For better performance and separation, we can even configure dedicated network for data synchronization traffic
  • This is not specific to distributed availability group, but it’s not recommended to add large number of databases in the group, say it above 100 databases.

For monitoring, here is a T-SQL script we use to monitor the latency and throughput of distributed availability group (Run on primary replica of primary availability group):

Distributed availability groups are an excellent option to leverage when we want to do database upgrades, refresh hardware, or add disaster recovery environments; either on-premise or in a cloud infrastructure.

Share or like on social media: 

More from the blog

6 Tips for Effectively Managing Remote Work

Given the present working landscape of COVID-19, here are some tips to remotely work and learn effectively.

Read Story

Use Cases of Ansible AWX

Automating with AWX will help in scaling and making technology adjustments on time thus reducing human error. This will help in providing faster end-to-end solution from development to production to the customers, thus increasing workforce productivity.

Read Story

Automating IT infrastructure with Ansible AWX

Automation has become a necessity at an enterprise level in order to provide faster IT services. It has become an important aspect the need to provision platforms for building network and infrastructure without the need of human intervention.

Read Story

EIQ + IQ = Success in Project Management

The project managers who cultivate their Emotional Intelligence Quotient have an essential talent for leading people. It’s their characteristic empathy that allows them to understand the motivations and situations of those they work with. And they forge ahead with the full support of their project team.

Read Story

Building a Kubernetes Platform

Creating a production-ready Kubernetes platform, ready to accept application migrations from a traditional environment, is by no means a simple task. The scope of its reach is seen from software development, QA and infrastructure. In the last year, it’s been difficult to meet a single person who is working in IT and has not started to investigate this technology.

Read Story

IT Services: Distributed Availability Groups in SQL Server 2016

One of the best features in SQL Server 2016 is the distributed availability group (AG). This new feature gives us the option to scale the synchronization across forwarder nodes instead of on the primary replica only (see Figure 1 & 2 for distributed versus non-distributed AGs).

Read Story

Save Time and Money With ACTIVE’s New IT Services

ACTIVE Network is more than just your technology provider, it’s your partner in success. We know your needs extend beyond a software solution, so we are extremely excited to introduce our new IT services to help you achieve your business goals and build stronger communities.

Read Story

Schedule a personal consultation

See for yourself how ACTIVE can transform your organization.