An overview of distributed SQL Server Always On Availability Groups

An overview of distributed SQL Server Always On Availability Groups

SQL Server 2016 provides a new feature Distributed Availability Group for disaster recovery purposes. It is a particular type of availability group that helps access the multiple failover clusters. In this article’s series, we configured a traditional Always On group. It has the following requirements.

Introduction

SQL Server 2016 provides a new feature Distributed Availability Group for disaster recovery purposes. It is a particular type of availability group that helps access the multiple failover clusters. In this article’s series, we configured a traditional Always On group. It has the following requirements.

  • A failover cluster configuration
  • Availability replicas should be part of the same failover configuration
  • We should configure a SQL listener that always connects to the primary replica

This article explores the concept of the Distributed Availability Group and configures it for the demonstration purpose

SQL Server Distributed Availability Group

Suppose we have two independent failover clusters in your infrastructure. These clusters are configured with two separate SQL Server Always On availability groups. Suppose one of the clusters is in your primary site and the second cluster is in the disaster recovery site. SQL Server distributed availability group provides a solution to configure the availability groups between these clusters.

In a traditional SQL Server Always On Availability Groups, all replicas should be part of the failover cluster. We can implement a disaster recovery solution in a traditional AG, but you need to configure the DR server in the same failover cluster. You can configure an asynchronous data commit for the DR Availability group. It requires a geographically stretched failover cluster, as shown below. You need to do complex networking, firewall configurations. It also requires additional configurations such as CrossSubnetDelay, CrossSubnetThreshold using Windows PowerShell.

SQL Server Always On Availability Groups representation

You can add multiple secondary replicas that receive the data from the primary SQL replica located in the leading site. In the below image, we see two DR nodes receiving asynchronous data from the primary replica. These four nodes are part of a single geographical cluster.

Single WFSC across multiple data centres

Now, suppose you have two separate sites, and you do not want to create a stretched cluster. Both clusters are independent of each other. You can configure the distributed availability group where the primary cluster is in the DC and secondary cluster in the DR.

As shown below, we create a distributed SQL Server Availability group with below configurations:

  • You have a failover cluster in your primary site, and it has a synchronous replica between the two nodes. You configured a SQL listener to point applications to the primary replica
  • You have another failover cluster in the DR site, and it also has synchronous data commit replica in its two nodes. You also configured another SQL listener in the DR cluster
  • We created a distributed AG that connects the listener of both failover clusters hosted in the DC and DR site
  • You can configure both synchronous or asynchronous data commit for a distributed availability group. In the synchronous data commit, primary replica waits for an acknowledgement from the secondary replica to commit the transaction. In case you prefer to configure the synchronous replica, you should consider the network bandwidth and transaction workload to avoid any impact on the application connecting to the primary replica in the DC site

Distributed SQL Server Always On Availability Groups

alwayson availability groups installation setup and configuration sql

Bootstrap 5 Complete Course with Examples

Bootstrap 5 Tutorial - Bootstrap 5 Crash Course for Beginners

Nest.JS Tutorial for Beginners

Hello Vue 3: A First Look at Vue 3 and the Composition API

Building a simple Applications with Vue 3

Deno Crash Course: Explore Deno and Create a full REST API with Deno

How to Build a Real-time Chat App with Deno and WebSockets

Convert HTML to Markdown Online

HTML entity encoder decoder Online

Introduction to Structured Query Language SQL pdf

SQL stands for Structured Query Language. SQL is a scripting language expected to store, control, and inquiry information put away in social databases. The main manifestation of SQL showed up in 1974, when a gathering in IBM built up the principal model of a social database. The primary business social database was discharged by Relational Software later turning out to be Oracle.

Add or remove a node from SQL Server Always On Availability Groups using T-SQL scripts

This article gives an overview of SQL Server Always On Availability Group features. We configured the SQL Server Always On Availability Group from the beginning of building virtual machines.

Configure Managed Service Accounts for SQL Server Always On Availability Groups

This article gives an overview of Configuring Managed Service Accounts for SQL Server Always On Availability Groups.

Install SQL Server 2019 on Windows Server 2016 with SQL Server

In this article, we will discuss configuring SQL Server Always On Availability Groups and perform failover validations. You should go through the following articles and build the infrastructure before proceeding with this article.

SQL Server Always On Availability Group on the domain-independent Failover Cluster

This article configures SQL Server Always On Availability Groups on the domain independent Windows Failover Clusters. In the previous article, Deploy a domain-independent Windows Failover Cluster for SQL Server Always On Availability Groups, we learned the new capability in Windows Server 2016 to configure a domain-independent Windows failover cluster.