Distributed Availability Groups in SQL Server

Hello,

AlwaysOn has become standard as high availability solution in SQL Server. Availability groups and listeners made building high available systems much easier. But traditional availability groups require all nodes to be part of the same failover cluster. This can be challenging when dealing with two data centers with two different subnets, even two different domains. Distributed AG structure can be used in these situations as a disaster recovery solution.

You can find a good explanation about distributed availability groups on the Microsoft website;

Distributed availability group is a special type of availability group that spans two separate availability groups. The availability groups that participate in a distributed availability group do not need to be in the same location. They can be physical, virtual, on-premises, in the public cloud, or anywhere that supports an availability group deployment. This includes cross-domain and even cross-platform — such as between an availability group hosted on Linux and one hosted on Windows. As long as two availability groups can communicate, you can configure a distributed availability group with them.

A traditional availability group has resources configured in a WSFC cluster. A distributed availability group does not configure anything in the WSFC cluster. Everything about it is maintained within SQL Server. (Microsoft Document)

So we don’t have to put nodes on the same WSFC. Also, when we configure two different availability groups, these groups can be at different data centers; also, they can be at different domains even different platforms. As long as the other AG’s SQL Server version stays same or higher than primary, they can be at different OS versions.

In Trendyol, multi-dc application architectures have become important. Because of that, multi-dc database architectures have become very important. This story will discuss how we are configuring multi-dc SQL Server Distributed Availability Group architecture even for different domains.

Our architecture will look like this;

Let’s start. I have configured 2 different clusters, and each one has 2 nodes. They are on the same domain. On every 4 nodes, Windows Server 2019 and SQL Server 2019 are installed. 2 Availability groups and 2 listeners exist. On one side, we have;

dbadag-ag1-n1, dbadag-ag1-n2 nodes configured with WSFC1=dbadag-ag1-cls failover cluster and dbadaglistener1 as the listener and DBADAGAG1 as availability group.

Other side;

dbadag-ag2-n1, dbadag-ag2-n2 nodes configured with WSFC2=dbadag-ag2-cls failover cluster and dbadaglistener2 as the listener DBADAGAG2 as availability group.

All mirroring endpoints configured on port 5022

If you have this configuration, we can create our distributed availability group. Our architecture will look like this;

On dbadag-ag1-n1 instance, we will execute this command;

With this command, we are creating a distributed availability group named “DBADistributedAG” with two availability groups: DBADAGAG1 and DBADAGAG2. We defined their listeners and mirroring endpoint ports.

We defined availability mode as async commit to secure our primary transaction performance. Failover mode is manual because of the async commit. (You can change it to sync commit when you have to failover to other AG).

The seeding mode is AUTOMATIC. So we don’t have to perform backup restore operations manually. You can use it if your database is not too big. With automatic seeding, it takes VDI backup from primary and restores to secondary on then sync databases on the fly like AlwaysON automatic seeding.

On dbadag-ag2-n1 we execute this command;

When you execute this command on forwarder, you should see the database restoring state for a while. If you can’t see maybe you should give permissions if the availability group doesn’t have to create database rights.

ALTER AVAILABILITY GROUP [DBADAGAG1] GRANT CREATE ANY DATABASE
ALTER AVAILABILITY GROUP [DBADAGAG2] GRANT CREATE ANY DATABASE

You can monitor the status of backup on primary with this script;

SELECT r.session_id, r.status, r.command, r.wait_type, r.percent_complete, r.estimated_completion_time FROM sys.dm_exec_requests r JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id WHERE r.session_id <> @@SPID AND s.is_user_process = 0 AND r.command like ‘VDI%’ and wait_type =’BACKUPTHREAD’

And you can monitor secondary with this script;

SELECT r.session_id, r.status, r.command, r.wait_type, r.percent_complete, r.estimated_completion_time FROM sys.dm_exec_requests r JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id WHERE r.session_id <> @@SPID AND s.is_user_process = 0 AND r.command like ‘REDO%’ and wait_type =’BACKUPTHREAD’

After the process is completed, you can see your database on forwarder with the status “SYNCHRONIZING”.

If your secondary AG (DBADAGAG2) has “Automatic seeding” selected, the database will be populated to other nodes. Otherwise, you should manually restore and add the database to DBADAGAG2 then it will stay in sync.

What if your database too big and you can’t use AUTOMATIC SEEDING?

You can create DBADistributedAG with SEEDING_MODE = MANUAL from primary and join on forwarder with SEEDING_MODE = MANUAL.

You have to take backup full from primary and restore the database on forwarder with no recovery option, and you should restore transaction logs up to now with norecovery option, after that, you can join database to DBADAGAG2 with command;

ALTER AVAILABILITY GROUP DBADAGAG2 ADD DATABASE AdventureWorks;

So what if our secondary AG is on a different domain?

You should have trust between domains. If you have trust, SQL server service accounts must be in the Administrator group on other instances.

If you don’t have trust you should configure endpoints with the certificate option. To do that, you have to complete some steps;

1. For each instance that will host primary replica, create a master key;

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘Strong Password’;

2. Create a login on all instances;

CREATE LOGIN [userfordag] WITH PASSWORD = ‘Strong Password’;

3. Create user from login;

CREATE USER userfordag FOR LOGIN userfordag;

4. Assign “connect on endpoint” rights to the user. Here, we will use HA_ENDPOINT, bound on 5022 port.

GRANT CONNECT ON ENDPOINT::HA_ENDPOINT TO [userfordag];

5. We will create a certificate for this DAG cluster. The certificate will be used for authentication. After that, we will take a backup from it;

CREATE CERTIFICATE NameofCertificate WITH SUBJECT = 'Some meaningful subject for certificate';
BACKUP CERTIFICATE NameofCertificate TO FILE = 'D:\CertFile\NameofCertificate.cer' WITH PRIVATE KEY (FILE = 'D:\CertFile\NameofCertificate.pvk' ,ENCRYPTION BY PASSWORD = 'Some Strong Password'  );

Next, copy the certificate to other instances.Repeat step 1–2–3–4 on other servers in this DAG cluster (dbadag-ag1-n1,dbadag-ag1-n1,dbadag-ag2-n1,dbadag-ag2-n2). And restore certificate with this command from files on every server after other steps;

CREATE CERTIFICATE NameofCertificate AUTHORIZATION userfordag FROM FILE = ‘D:\CertFile\NameofCertificate.cer’ WITH PRIVATE KEY (FILE = ‘D:\CertFile\NameofCertificate.pvk’,DECRYPTION BY PASSWORD = ‘Some Strong Password’)

Now we can configure instance mirroring endpoints on all instances to use our certificate if windows negotiation fails. So availability groups could use windows negotiate auth for internal communication and between availability groups certificate could be used.

ALTER ENDPOINT [HA_ENDPOINT] STATE=STARTED AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL) FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE CERTIFICATE NameofCertificate, ENCRYPTION = REQUIRED ALGORITHM AES)

Finally, we can create distributed availability group on primary and join other AG on forwarder. As a result, you should see distributed AG on primary and forwarder nodes.

You can check distributed AG health with command;

The storage state is offline because we didn’t add storage when we create WSFCs

Or using DMVs;

SELECT ag.[name] AS [AG Name], ag.is_distributed, ar.replica_server_name AS [Underlying AG], ars.role_desc AS [Role], ars.synchronization_health_desc AS [Sync Status] FROM sys.availability_groups AS ag INNER JOIN sys.availability_replicas AS ar ON ag.group_id = ar.group_id INNER JOIN sys.dm_hadr_availability_replica_states AS ars ON ar.replica_id = ars.replica_id WHERE ag.is_distributed = 1

SELECT ag.[name] AS [Distributed AG Name], ar.replica_server_name AS [Underlying AG], dbs.[name] AS [Database], ars.role_desc AS [Role], drs.synchronization_health_desc AS [Sync Status], drs.log_send_queue_size, drs.log_send_rate, drs.redo_queue_size, drs.redo_rate FROM sys.databases AS dbs INNER JOIN sys.dm_hadr_database_replica_states AS drs ON dbs.database_id = drs.database_id INNER JOIN sys.availability_groups AS ag ON drs.group_id = ag.group_id INNER JOIN sys.dm_hadr_availability_replica_states AS ars ON ars.replica_id = drs.replica_id INNER JOIN sys.availability_replicas AS ar ON ar.replica_id = ars.replica_id WHERE ag.is_distributed = 1 GO

SELECT ag.name AS group_name, ag.is_distributed, ar.replica_server_name AS replica_name, ar.availability_mode_desc, ar.failover_mode_desc, ar.primary_role_allow_connections_desc AS allow_connections_primary, ar.secondary_role_allow_connections_desc AS allow_connections_secondary, ar.seeding_mode_desc AS seeding_mode FROM sys.availability_replicas AS ar JOIN sys.availability_groups AS ag ON ar.group_id = ag.group_id

To failover from the first AG to the second AG. We will change the availability mode to sync commit. Execute this command on Primary and Forwarder;

ALTER AVAILABILITY GROUP DBADistributedAG MODIFY AVAILABILITY GROUP ON ‘DBADAGAG1’ WITH ( AVAILABILITY_MODE = SYNCHRONOUS_COMMIT ),‘DBADAGAG2’ WITH ( AVAILABILITY_MODE = SYNCHRONOUS_COMMIT);

Now our distributed AG in sync-commit mode.

We should check last_hardened_lsn it has to be the same for all databases on and both AG state should be in “SYNCHRONIZED” status;

SELECT ag.name, drs.database_id, drs.group_id, drs.replica_id, drs.synchronization_state_desc, drs.end_of_log_lsn FROM sys.dm_hadr_database_replica_states drs, sys.availability_groups ag WHERE drs.group_id = ag.group_id

On the primary query, the result should look like this;

On secondary query result should look like this;

With this query, we will set the distributed AG role on the primary to SECONDARY. Then, run the query on the primary.

ALTER AVAILABILITY GROUP [DBADistributedAG] SET (ROLE = SECONDARY);

!! Now distributed AG went offline and client connections are terminated.

With this query failover, the distributed AG to the secondary Availability Group. Run the query on the forwarder.

ALTER AVAILABILITY GROUP [DBADistributedAG] FORCE_FAILOVER_ALLOW_DATA_LOSS;

With this command, old secondary AG (DBADAGAG2) becomes online and our new primary AG.

!! FORCE_FAILOVER_ALLOW_DATA_LOSE is the only supported argument for failover of AG.

Now, clients should be able to connect the second AG via its listener.

You can use this method for OS and SQL Version upgrades and also migrations.

Thanks to Ahmet Rende for his contributions!

Thanks for reading.

You can find me at LinkedIn: https://www.linkedin.com/in/alpaykurbal/

Feel free to ask any questions and discuss technology topics.

Alpay

References:

What is a distributed availability groups - SQL Server Always On

Create a domain-independent availability group - SQL Server Always On

Manual Failover of SQL Server Always On Distributed Availability Group for Disaster Recovery…

Automatic Seeding in Always On Availability Groups