Distributed Availability Groups to migrate SQL Server databases

Problem

In Part 1 of this tutorial series we looked at how to configure a Distributed Availability Group as a disaster recovery strategy for SQL Server 2016 and higher instances. We covered steps 1 thru 4 and in this article we will cover steps 5 thru 6.

Solution

To continue this series on Configuring Distributed Availability Groups Between Failover Clusters on Different Active Directory Domains, we will look at configuring authentication between the primary AG (TDPRDAGPROD01) and the secondary AG (TDPRDAGPROD11) prior to creating the Distributed AG.

In Part 1, you learned how to create the certificates for use with authentication. You've also learned how to update the endpoints to use those certificates. This is in addition to Windows authentication between replicas within the same Active Directory domain. This tip will walk you through leveraging those certificates to authenticate between the primary AG (TDPRDAGPROD01) and the secondary AG (TDPRDAGPROD11).

For reference, a review of the high-level steps is shown below, the first 4 steps were covered in this article. This tip will continue the process starting on Step #5.

Create the primary AG (TDPRDAGPROD01) with a corresponding listener name (TDPRDAGLN075)
Create the secondary AG (TDPRDAGPROD11) with a corresponding listener name (TDPRDAGLN077)
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)
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)
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)
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)
Create the Distributed AG (DistAG_DC1_DC2) on the primary AG (TDPRDAGPROD01)
Join the secondary AG (TDPRDAGPROD11) to the Distributed AG

Step #5a: Create a login on the primary replica of the primary AG (TDPRDAGPROD01)

When working with certificates, the SQL Server login created on the primary replica of the AG is used by the secondary replicas to connect to the endpoint. You can think of the secondary replicas as client applications that require credentials to connect to the SQL Server instance – the primary replica.

However, since the replicas of the primary AG are in the same Active Directory domain, you don't need an additional SQL Server login to authenticate the connection to the endpoint. The SQL Server service account is enough for this, provided that you granted the proper permissions.

So, why would you need an additional SQL Server login? Since the primary AG and the secondary AG are on two different Active Directory domains, this login will be used by the replicas of the secondary AG (TDPRDAGPROD11) to connect to the replicas of the primary AG. This is the same approach that you use when connecting a client application via SQL Server authentication. It just so happens that the client applications are the replicas of the secondary AG.

Use the T-SQL script below to create a login on the primary replica of the primary AG (TDPRDAGPROD01).

:CONNECT TDPRD071.TESTDOMAIN.COM -U sqladmin -P $ecUr3PAssw0rd
USE master  
GO
CREATE LOGIN login_AvailabilityGroup 
WITH PASSWORD = 'y0ur$ecUr3PAssw0rd';  
GO

Step #5b: Create a user for the login

Use the T-SQL script below to create a user for the login. Note that only the replicas in the secondary AG (TDPRDAGPROD11) will use this login to connect to the Availability Group endpoint.

--Run this on the primary replica of the primary Availability Group
:CONNECT TDPRD071.TESTDOMAIN.COM -U sqladmin -P $ecUr3PAssw0rd
USE master  
GO
CREATE USER login_AvailabilityGroup  
FOR LOGIN login_AvailabilityGroup  
GO

Step #5c: Associate the certificates created on the replicas of the secondary AG (TDPRDAGPROD11) with the user

The replicas of the secondary AG (TDPRDAGPROD11) will use the database user created in the previous step to connect to the primary replica of the primary AG (TDPRDAGPROD01). Since each replica of the secondary AG (TDPRDAGPROD11) has a corresponding certificate, you need to associate those certificates with the user. The script below creates two certificates based on the ones generated by the replicas of the secondary AG (TDPRDAGPROD11) and assigns both to the user.

NOTE: Be sure that the certificates from the replicas of the secondary AG (TDPRDAGPROD11) - 15C-TDPRD071_cert.cer and 15C-TDPRD072_cert.cer – have been copied over to the C:\MSSQL folder.

--Run this on the primary replica of the primary Availability Group
:CONNECT TDPRD071.TESTDOMAIN.COM -U sqladmin -P $ecUr3PAssw0rd
USE master  
GO
--Associate certificate from 15C-TDPRD071.15C-INC.COM with user
CREATE CERTIFICATE [15C-TDPRD071_cert]  
AUTHORIZATION login_AvailabilityGroup  
FROM FILE = 'C:\MSSQL\15C-TDPRD071_cert.cer'  
GO
 
--Associate certificate from 15C-TDPRD072.15C-INC.COM with user
CREATE CERTIFICATE [15C-TDPRD072_cert]  
AUTHORIZATION login_AvailabilityGroup  
FROM FILE = 'C:\MSSQL\15C-TDPRD072_cert.cer'  
GO

Step #5d: Grant the CONNECT ON ENDPOINT permission on the login

Use the T-SQL script below to grant the CONNECT ON ENDPOINT permissions on the login.

--Run this on the primary replica of the primary Availability Group
:CONNECT TDPRD071.TESTDOMAIN.COM -U sqladmin -P $ecUr3PAssw0rd
USE master  
GO
GRANT CONNECT ON ENDPOINT::Hadr_Endpoint 
TO [login_AvailabilityGroup];    
GO

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

At any time, the primary AG will failover to the secondary replica (TDPRD072), making it the new primary replica. The same process needs to be done so that the replicas of the secondary AG (TDPRDAGPROD11) can connect to new primary replica of the primary AG. Hence, the commands are exactly the same. The only thing different is the connection information.

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

NOTE: Be sure that the certificates from the replicas of the secondary AG (TDPRDAGPROD11) - 15C-TDPRD071_cert.cer and 15C-TDPRD072_cert.cer – have been copied over to the C:\MSSQL folder.

--Run this on the secondary replica of the primary Availability Group
:CONNECT TDPRD072.TESTDOMAIN.COM -U sqladmin -P $ecUr3PAssw0rd
USE master  
GO
--Create a login on the secondary replica of the primary AG (TDPRDAGPROD01)
CREATE LOGIN login_AvailabilityGroup 
WITH PASSWORD = 'y0ur$ecUr3PAssw0rd';  
GO
 
--Create a user for the login
CREATE USER login_AvailabilityGroup  
FOR LOGIN login_AvailabilityGroup  
GO
 
--Associate certificate from 15C-TDPRD071.15C-INC.COM with user
CREATE CERTIFICATE [15C-TDPRD071_cert]  
AUTHORIZATION login_AvailabilityGroup  
FROM FILE = 'C:\MSSQL\15C-TDPRD071_cert.cer'  
GO
 
--Associate certificate from 15C-TDPRD072.15C-INC.COM with user
CREATE CERTIFICATE [15C-TDPRD072_cert]  
AUTHORIZATION login_AvailabilityGroup  
FROM FILE = 'C:\MSSQL\15C-TDPRD072_cert.cer'  
GO
 
--Grant the CONNECT ON ENDPOINT permission on the login
GRANT CONNECT ON ENDPOINT::Hadr_Endpoint 
TO [login_AvailabilityGroup];    
GO

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

Step #6a: Create a login on the primary replica of the secondary AG (TDPRDAGPROD11)

Use the T-SQL script below to create a login 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  
GO
CREATE LOGIN login_AvailabilityGroup 
WITH PASSWORD = 'y0ur$ecUr3PAssw0rd';  
GO

Step #6b: Create a user for the login

Use the T-SQL script below to create a user for the login. Note that only the replicas in the primary AG (TDPRDAGPROD01) will use this login to connect to the Availability Group endpoint.

--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 USER login_AvailabilityGroup  
FOR LOGIN login_AvailabilityGroup  
GO

Step #6c: Associate the certificates created on the replicas of the primary AG (TDPRDAGPROD01) with the user

Similar to Step #5c, the replicas of the primary AG (TDPRDAGPROD01) will use the database user created in the previous step to connect to the primary replica of the secondary AG (TDPRDAGPROD11). Since each replica of the primary AG (TDPRDAGPROD01) has a corresponding certificate, you need to associate those certificates with the user. The script below creates two certificates based on the ones generated by the replicas of the primary AG (TDPRDAGPROD01) and assigns both to the user.

NOTE: Be sure that the certificates from the replicas of the primary AG (TDPRDAGPROD01) - TDPRD071_cert.cer and TDPRD072_cert.cer – have been copied over to the C:\MSSQL folder.

--Run this on the primary replica of the secondary Availability Group
:CONNECT 15C-TDPRD071.15C-INC.COM -U sqladmin -P $ecUr3PAssw0rd
USE master  
GO
 
--Associate certificate from TDPRD071.TESTDOMAIN.COM with user
CREATE CERTIFICATE TDPRD071_cert
AUTHORIZATION login_AvailabilityGroup  
FROM FILE = 'C:\MSSQL\TDPRD071_cert.cer'  
GO
 
--Associate certificate from TDPRD072.TESTDOMAIN.COM with user
CREATE CERTIFICATE TDPRD072_cert
AUTHORIZATION login_AvailabilityGroup  
FROM FILE = 'C:\MSSQL\TDPRD072_cert.cer'  
GO

Step #6d: Grant the CONNECT ON ENDPOINT permission on the login

Use the T-SQL script below to grant the CONNECT ON ENDPOINT permissions on the login.

--Run this on the primary replica of the secondary Availability Group
:CONNECT 15C-TDPRD071.15C-INC.COM -U sqladmin -P $ecUr3PAssw0rd
USE master  
GO
GRANT CONNECT ON ENDPOINT::Hadr_Endpoint 
TO [login_AvailabilityGroup];    
GO

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

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

NOTE: Be sure that the certificates from the replicas of the primary AG (TDPRDAGPROD01) - TDPRD071_cert.cer and TDPRD072_cert.cer – have been copied over to the C:\MSSQL folder.

--Run this on the secondary replica of the secondary Availability Group
:CONNECT 15C-TDPRD072.15C-INC.COM -U sqladmin -P $ecUr3PAssw0rd
USE master  
GO
 
--Create a login on the secondary replica of the secondary AG (TDPRDAGPROD11)
CREATE LOGIN login_AvailabilityGroup 
WITH PASSWORD = 'y0ur$ecUr3PAssw0rd';  
GO
 
--Create a user for the login 
CREATE USER login_AvailabilityGroup  
FOR LOGIN login_AvailabilityGroup  
GO
 
--Associate certificate from TDPRD071.TESTDOMAIN.COM with user
CREATE CERTIFICATE TDPRD071_cert
AUTHORIZATION login_AvailabilityGroup  
FROM FILE = 'C:\MSSQL\TDPRD071_cert.cer'  
GO
 
--Associate certificate from TDPRD072.TESTDOMAIN.COM with user
CREATE CERTIFICATE TDPRD072_cert
AUTHORIZATION login_AvailabilityGroup  
FROM FILE = 'C:\MSSQL\TDPRD072_cert.cer'  
GO
 
--Grant the CONNECT ON ENDPOINT permission on the login
GRANT CONNECT ON ENDPOINT::Hadr_Endpoint 
TO [login_AvailabilityGroup];    
GO

Observe that Step #5 and Step #6 are almost identical. The only difference is the association of certificates. In Step #5c, you are associating the certificates from the replicas of the secondary AG (TDPRDAGPROD11) to the primary AG (TDPRDAGPROD01). That's because the replicas of the secondary AG (TDPRDAGPROD11) need to authenticate with the primary replica of the primary AG (TDPRDAGPROD01). In Step #6c, it' the other way around - you are associating the certificates from the replicas of the primary AG (TDPRDAGPROD01) to the secondary AG (TDPRDAGPROD11).

As I mentioned in the beginning of this tip series, this is a very complicated process that requires careful planning and implementation. Complicated as this already is, Step #1 up to Step #6 are just to prepare the security requirements of authenticating the logins and securing the endpoints across AGs in different Active Directory domains. It doesn't even cover the creation and configuration of the Distributed AG just yet.

In the next tip, I will cover how to create and configure a Distributed AG. This also includes failing over to the secondary AG once you are ready for the production cutover as part of the database migration process.

Next Steps

Review the previous tips on SQL Server Availability Group
Read about the following topics from the Microsoft Documentation

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

Article Last Updated: 2022-05-26

Build Distributed Availability Groups to migrate SQL Server databases to a different AD Domain - Part 2