Configuring Distributed Availability Groups Between Failover Clusters on Different Active Directory Domains – Part 1

By: Edwin Sarmiento   |   Updated: 2022-05-17   |   Comments   |   Related: 1 | 2 | 3 | > Availability Groups

Problem

In a previous tip on Setup and Implement SQL Server 2016 Always On Distributed Availability Groups, we have seen how to configure a Distributed Availability Group as a disaster recovery strategy for SQL Server 2016 and higher instances. We want to leverage Distributed Availability Groups to migrate our databases to a different Active Directory domain. How do we go about it?

Solution

A very common scenario when migrating SQL Server databases is moving to a new environment. That new environment could be on a different network segment, on a public cloud, or even to a new organization as part of mergers and acquisitions. More often than not, these migrations are done in the context of the servers being in the same Active Directory domain.

As described in this tip, SQL Server 2016 introduced support for Always On Availability Groups without an Active Directory domain. However, due to the amount of effort needed to set it up and maintain it, majority of the deployments still leverage Active Directory for security and authentication. The availability of centralized directory service like Active Directory makes security and authentication a lot easier for high availability solutions like that of Always On Availability Groups.

With Microsoft's push to the cloud, most customers are now migrating their on-premises databases to Azure. And the most straight forward way to do this is by doing a lift-and-shift migration by taking existing environments and moving it to the cloud. One challenge with this approach for high availability solutions is minimizing downtime during the migration process. The other challenge is the complexity of configuring a site-to-site VPN between on-premises network and Microsoft Azure to extend the Active Directory domain. As outlined in this tip, the infrastructure team has already extended the on-premises Active Directory domain to Microsoft Azure. This made configuring Distributed Availability Group (AG) dependent on the same Active Directory domain. What if you need to migrate your SQL Server databases to a different Active Directory domain while keeping the high availability solution? This series of tips will walk you through the process of achieving this.

You can configure a Distributed AG as a migration strategy where different Active Directory domains are involved, as shown in the diagram below.

Configuring Distributed Availability Groups Between Failover Clusters on Different Active Directory Domains

Configuring Distributed AG as a migration strategy where different Active Directory domains are involved is very complicated and requires careful planning and implementation. It can cause a lot of confusion because it combines working with servers in the same Active Directory domain while treating them like servers in a workgroup. If not done properly, this could cause more problems and could potentially lead to an outage. Be very careful with following the steps outlined below.

Below are the details of the implementation for the scenario described:

Here's a high-level overview of the steps for your reference. Carefully read and understand every step to make sure you don't get confused as to why they are there.

1. Create the primary AG (TDPRDAGPROD01) with a corresponding listener name (TDPRDAGLN075)
2. Create the secondary AG (TDPRDAGPROD11) with a corresponding listener name (TDPRDAGLN077)
3. Configure certificates on the primary AG (TDPRDAGPROD01)
   1. Create a database master key on the primary replica of the primary AG (TDPRDAGPROD01)
   2. Create the certificate that will be used to authenticate while connecting to the primary replica of the primary AG (TDPRDAGPROD01)
   3. Modify the endpoint on the primary replica of the primary AG (TDPRDAGPROD01) and add the certificate for authentication
   4. Export the certificate generated on the primary replica of the primary AG (TDPRDAGPROD01) to a file and copy it to the replicas of the secondary AG (TDPRDAGPROD11)
   5. Repeat Steps (a) to (d) on the secondary replica of the primary AG (TDPRDAGPROD01)
4. Configure certificates on the secondary AG (TDPRDAGPROD11)
   1. Create a database master key on the primary replica of the secondary AG (TDPRDAGPROD11)
   2. Create the certificate that will be used to authenticate while connecting to the primary replica of the secondary AG (TDPRDAGPROD11)
   3. Modify the endpoint on the primary replica of the secondary AG (TDPRDAGPROD11) and add the certificate for authentication
   4. Export the certificate generated on the primary replica of the secondary AG (TDPRDAGPROD11) to a file and copy it to the replicas of the primary AG (TDPRDAGPROD01)
   5. Repeat Steps (a) to (d) on the secondary replica of the secondary AG (TDPRDAGPROD11)
5. Configure authentication on the primary AG (TDPRDAGPROD01) for use by the replicas of the secondary (TDPRDAGPROD11)
   1. Create a login on the primary replica of the primary AG (TDPRDAGPROD01)
   2. Create a user for the login
   3. Associate the certificates created on the replicas of the secondary AG (TDPRDAGPROD11) with the user
   4. Grant the CONNECT ON ENDPOINT permission on the login
   5. Repeat Steps (a) to (d) on the secondary replica of the primary AG (TDPRDAGPROD01)
6. Configure authentication on the secondary AG (TDPRDAGPROD11) for use by the replicas of the primary (TDPRDAGPROD01)
   1. Create a login on the primary replica of the secondary AG (TDPRDAGPROD11)
   2. Create a user for the login
   3. Associate the certificates created on the replicas of the primary AG (TDPRDAGPROD01) with the user
   4. Grant the CONNECT ON ENDPOINT permission on the login
   5. Repeat Steps (a) to (d) on the secondary replica of the secondary AG (TDPRDAGPROD11)
7. Create the Distributed AG (DistAG_DC1_DC2) on the primary AG (TDPRDAGPROD01)
8. Join the secondary AG (TDPRDAGPROD11) to the Distributed AG

There are several things to consider when configuring a Distributed AG between failover clusters on different Active Directory domains:

• The traditional AGs will use Active Directory domain accounts for authentication. Steps #1 and #2 are configured to use their own Active Directory domain accounts. Authentication between AG replicas occur within the context of their corresponding Active Directory domain. This is how you would typically configure AGs.
• The primary AG and secondary AG will use certificates to authenticate with one another. Since the primary AG (TDPRDAGPROD01) is on a different Active Directory domain - TESTDOMAIN.COM - than the secondary AG (TDPRDAGPROD11) – 15C-INC.COM – authentication needs to happen using certificates. Until support for trust between Active Directory domains is included with AGs, this is the only way to do this. Refer to this tip on implementing an Always On Availability Group without Active Directory for reference.
• All replicas of the secondary AG (TDPRDAGPROD11) can become a replica of the primary AG (TDPRDAGPROD01) and vice versa. This can be a bit confusing so let's simplify. Think of the secondary AG (TDPRDAGPROD11) as a replica of the primary AG (TDPRDAGPROD01) in addition to its own replicas. Let's look at the primary AG (TDPRDAGPROD01) first. If the primary replica of the primary AG (TDPRDAGPROD01) is TDPRD071, the secondary replicas include TDPRD072.TESTDOMAIN.COM and the replicas of the secondary AG (TDPRDAGPROD11), namely 15C-TDPRD071.15C-INC.COM and 15C-TDPRD072.15C-INC.COM. You don't need to worry about TDPRD072.TESTDOMAIN.COM since they are both in the same Active Directory domain. However, since the replicas of the secondary AG (TDPRDAGPROD11) are in a totally different Active Directory domain, they need to authenticate with TDPRD071 using certificates. In case a failover happens and TDPRD072 becomes the primary replica of the primary AG (TDPRDAGPROD01), the replicas of the secondary AG (TDPRDAGPROD11) need to authenticate using certificates. You can apply the same logic with the secondary AG (TDPRDAGPROD11) having the primary AG (TDPRDAGPROD01) as replica.
• Leverage SQL Server authentication for configuration and administration. The Active Directory domain account that you use to manage SQL Server will not work on the other Active Directory domain unless there is a trust relationship between the two. In the screenshot below, a SQL Server login is used to connect to the SQL Server instances.
• Connect to the SQL Server instance using a fully qualified domain name (FQDN). Notice also that the fully qualified domain name is used to connect to the SQL Server instance instead of just the simple instance name. DNS resolution is very important especially when working with servers in different Active Directory domains.

Before proceeding with the configuration, make sure that each server can access the other servers – both via IP address and fully qualified domain name. Do this for the server hostnames as well as the Availability Group listener names. Domain name resolution is one of the most common issue you will encounter. A simple PING and TELNET tests can be used for verification.

Both the primary AG (TDPRDAGPROD01) with the corresponding listener name and the secondary AG (TDPRDAGPROD11) have already been created. Refer to this tip on how to configure a traditional Availability Group. For the secondary AG (TDPRDAGPROD11), you can create a dummy database to test that everything works. Just make sure that you delete the dummy database prior to creating the Distributed AG.

Do not proceed with Step #3 unless both the primary AG and the secondary AG are fully functional.

Step #3a: Create a database master key on the primary replica of the primary AG (TDPRDAGPROD01)

Use the T-SQL script below to create the database master key on the primary replica of the primary AG (TDPRDAGPROD01)

NOTE: Use SQLCMD mode when running the T-SQL scripts to make sure you are connected to the correct SQL Server instance using a SQL Server login and the FQDN.

--Run this on the primary replica of the primary Availability Group
:CONNECT TDPRD071.TESTDOMAIN.COM -U sqladmin -P $ecUr3PAssw0rd
USE master  
 
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'y0ur$ecUr3PAssw0rd';  
GO

Step #3b: Create the certificate that will be used to authenticate while connecting to the primary replica of the primary AG (TDPRDAGPROD01)

Use the T-SQL script below to create the certificate that will be used to authenticate to the primary replica of the primary AG (TDPRDAGPROD01). Note that only the replicas in the secondary AG (TDPRDAGPROD11) will use this.

--Run this on the primary replica of the primary Availability Group
:CONNECT TDPRD071.TESTDOMAIN.COM -U sqladmin -P $ecUr3PAssw0rd
USE master  
 
CREATE CERTIFICATE TDPRD071_cert 
WITH SUBJECT = 'TDPRD071 certificate for Distributed Availability Group' 
GO  

Step #3c: Modify the endpoint on the primary replica of the primary AG (TDPRDAGPROD01) and add the certificate for authentication

This is a bit tricky since you already have a fully functional endpoint. What you're doing here is modifying the endpoint to include both Windows authentication using the Active Directory domain account and the certificate. Use the T-SQL script below to update the endpoint of the primary replica of the primary AG (TDPRDAGPROD01). The section containing both authentication types is highlighted in the script for emphasis.

NOTE: Be very careful with this. A misconfiguration can prevent the secondary replica of your primary AG from connecting, preventing the databases from being synchronized.

--Run this on the primary replica of the primary Availability Group
:CONNECT TDPRD071.TESTDOMAIN.COM -U sqladmin -P $ecUr3PAssw0rd
USE master  
 
ALTER ENDPOINT [Hadr_endpoint] 
   STATE=STARTED
   AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
   FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE CERTIFICATE TDPRD071_cert, 
ENCRYPTION = REQUIRED ALGORITHM AES)
GO    

Step #3d: Export the certificate generated on the primary replica of the primary AG (TDPRDAGPROD01) to a file and copy it to the replicas of the secondary AG (TDPRDAGPROD11)

Use the T-SQL script below to export the certificate to a file. This assumes that you already have a C:\MSSQL folder available.

--Run this on the primary replica of the primary Availability Group
:CONNECT TDPRD071.TESTDOMAIN.COM -U sqladmin -P $ecUr3PAssw0rd
USE master  
 
BACKUP CERTIFICATE TDPRD071_cert TO FILE = 'C:\MSSQL\TDPRD071_cert.cer'; 
GO     

Once the certificate is created, copy it to the replicas of the secondary AG (TDPRDAGPROD11) – 15C-TDPRD071 and 15C-TDPRD072. You will need this for Step #6c.

Step #3e: Repeat Steps (3a) to (3d) on the secondary replica of the primary AG (TDPRDAGPROD01)

A complete script for the secondary replica of the primary AG (TDPRDAGPROD01) is shown below.

--Run this on the secondary replica of the primary Availability Group
:CONNECT TDPRD072.TESTDOMAIN.COM -U sqladmin -P $ecUr3PAssw0rd
 
USE master  
 
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'y0ur$ecUr3PAssw0rd';  
GO
 
CREATE CERTIFICATE TDPRD072_cert 
WITH SUBJECT = 'TDPRD072 certificate for Distributed Availability Group' 
GO
 
ALTER ENDPOINT [Hadr_endpoint] 
   STATE=STARTED
   AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
   FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE CERTIFICATE TDPRD072_cert, 
ENCRYPTION = REQUIRED ALGORITHM AES)
GO
 
BACKUP CERTIFICATE TDPRD072_cert TO FILE = 'C:\MSSQL\TDPRD072_cert.cer'; 
GO      

Once this is complete, you can proceed to do the same thing for the secondary AG (TDPRDAGPROD11).

Step #4a: Create a database master key on the primary replica of the secondary AG (TDPRDAGPROD11)

Use the T-SQL script below to create the database master key on the primary replica of the secondary AG (TDPRDAGPROD11).

--Run this on the primary replica of the secondary Availability Group
:CONNECT 15C-TDPRD071.15C-INC.COM -U sqladmin -P $ecUr3PAssw0rd
USE master  
 
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'y0ur$ecUr3PAssw0rd';  
GO

Step #4b: Create the certificate that will be used to authenticate while connecting to the primary replica of the secondary AG (TDPRDAGPROD11)

Use the T-SQL script below to create the certificate that will be used to authenticate to the primary replica of the secondary AG (TDPRDAGPROD11). Note that only the replicas in the primary AG (TDPRDAGPROD01) will use this.

--Run this on the primary replica of the secondary Availability Group
:CONNECT 15C-TDPRD071.15C-INC.COM -U sqladmin -P $ecUr3PAssw0rd
USE master  
GO
CREATE CERTIFICATE [15C-TDPRD071_cert] 
WITH SUBJECT = '15C-TDPRD071 certificate for Distributed Availability Group' 
GO

Step #4c: Modify the endpoint on the primary replica of the secondary AG (TDPRDAGPROD01) and add the certificate for authentication

Similar to Step #3c, use the T-SQL script below to update the endpoint of the primary replica of the secondary AG (TDPRDAGPROD11). The section containing both authentication types is highlighted in the script for emphasis.

Unlike with the primary AG (TDPRDAGPROD01), any misconfiguration on this step does not pose risks to the availability of your SQL Server databases. If you made a mistake, you can always reconfigure the secondary AG.

--Run this on the primary replica of the secondary Availability Group
:CONNECT 15C-TDPRD071.15C-INC.COM -U sqladmin -P $ecUr3PAssw0rd
USE master  
GO
ALTER ENDPOINT [Hadr_endpoint] 
   STATE=STARTED
   AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
   FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE CERTIFICATE [15C-TDPRD071_cert], 
ENCRYPTION = REQUIRED ALGORITHM AES)
GO

Step #4d: Export the certificate generated on the primary replica of the secondary AG (TDPRDAGPROD11) to a file and copy it to the replicas of the primary AG (TDPRDAGPROD01)

Use the T-SQL script below to export the certificate to a file. This assumes that you already have a C:\MSSQL folder available.

--Run this on the primary replica of the secondary Availability Group
:CONNECT 15C-TDPRD071.15C-INC.COM -U sqladmin -P $ecUr3PAssw0rd
USE master  
GO
BACKUP CERTIFICATE [15C-TDPRD071_cert] TO FILE = 'C:\MSSQL\15C-TDPRD071_cert.cer'; 
GO 

Once the certificate is created, copy it to the replicas of the primary AG (TDPRDAGPROD01) – TDPRD071 and TDPRD072. You will need this for Step #5c.

Step #4e: Repeat Steps (4a) to (4d) on the secondary replica of the secondary AG (TDPRDAGPROD11)

A complete script for the secondary replica of the secondary AG (TDPRDAGPROD11) is shown below.

--Run this on the primary replica of the secondary Availability Group
:CONNECT 15C-TDPRD072.15C-INC.COM -U sqladmin -P $ecUr3PAssw0rd
USE master  
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'y0ur$ecUr3PAssw0rd';  
GO
CREATE CERTIFICATE [15C-TDPRD072_cert] 
WITH SUBJECT = '15C-TDPRD072 certificate for Distributed Availability Group' 
GO
ALTER ENDPOINT [Hadr_endpoint] 
   STATE=STARTED
   AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
   FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE CERTIFICATE [15C-TDPRD072_cert], 
ENCRYPTION = REQUIRED ALGORITHM AES)
GO
BACKUP CERTIFICATE [15C-TDPRD072_cert] TO FILE = 'C:\MSSQL\15C-TDPRD072_cert.cer'; 
GO 

After completing Steps #3 and #4, you should have a copies of the certificates on each of the servers similar to the screenshot below.

• On the primary AG (TDPRDAGPROD01) replicas, both TDPRD071.TESTDOMAIN.COM and TDPRD072.TESTDOMAIN.COM should have a copy of the certificates from 15C-TDPRD071 and 15C-TDPRD072
• On the secondary AG (TDPRDAGPROD11) replicas, both 15C-TDPRD071.15C-INC.COM and 15C-TDPRD072.15C-INC.COM should have a copy of the certificates from TDPRD071 and TDPRD072

I did mention in the beginning of this tip that this is a very complicated process that requires careful planning and implementation. Steps #3 and #4 involve the preliminary work needed to configure authentication between the two AGs across different Active Directory domains.

In the next tip, I will cover how to create the SQL Server logins that will use the certificates (Steps #5 and #6) for both the primary AG (TDPRDAGPROD01) and secondary AG (TDPRDAGPROD11).

Next Steps

• Review the previous tips on SQL Server Availability Group
  • SQL Server AlwaysOn Availability Groups - Part 1 configuration
  • SQL Server AlwaysOn Availability Groups - Part 2 Availability Groups Setup
  • Setup and Implement SQL Server 2016 Always On Distributed Availability Groups
  • Implement a SQL Server 2016 Availability Group without Active Directory Part 1
  • Implement a SQL Server 2016 Availability Group without Active Directory Part 2
• Read about the following topics from the Microsoft Documentation
  • Distributed availability groups
  • Configure an Always On distributed availability group
  • Create a domain-independent availability group

About the author

Edwin M Sarmiento is a Microsoft SQL Server MVP and Microsoft Certified Master from Ottawa, Canada specializing in high availability, disaster recovery and system infrastructures.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips