In this blog post, we will discuss how to configure SQL Server Distributed Availability Groups (AGs) for database migration. Distributed AGs can be used as a disaster recovery strategy and can also be leveraged to migrate databases to a different Active Directory domain.

Step 1: Configuring Certificates for Authentication

The first step in configuring Distributed AGs is to create certificates for authentication. These certificates will be used to authenticate the primary and secondary replicas of the AGs. You can use the following T-SQL script to create the certificates:

CREATE CERTIFICATE [CertificateName]
    WITH SUBJECT = 'CertificateSubject'
    , EXPIRY_DATE = 'CertificateExpiryDate'
    , START_DATE = 'CertificateStartDate'
    , ENCRYPTION BY PASSWORD = 'CertificatePassword'
    , ALGORITHM = 'CertificateAlgorithm'
    , CERTIFICATE_SOURCE = 'CertificateSource';

Make sure to replace the placeholders with the actual values for your certificates.

Step 2: Configuring Authentication between AG Replicas

Once the certificates are created, you need to configure authentication between the primary and secondary replicas of the AGs. This involves modifying the endpoints on the replicas and adding the certificates for authentication. You can use the following T-SQL script to configure authentication:

ALTER ENDPOINT [EndpointName]
    FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE [CertificateName]);

Replace [EndpointName] with the name of the endpoint and [CertificateName] with the name of the certificate.

Step 3: Creating the Distributed AG

Once authentication is configured, you can proceed to create the Distributed AG. This involves specifying the primary and secondary replicas, along with their corresponding listener names. Use the following T-SQL script to create the Distributed AG:

CREATE AVAILABILITY GROUP [DistributedAGName]
    WITH (DISTRIBUTED)
    AVAILABILITY GROUP ON
    'PrimaryReplica' WITH
    (
      LISTENER_URL = 'PrimaryListenerURL',
      AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
      FAILOVER_MODE = MANUAL
    ),
    'SecondaryReplica' WITH
    (
      LISTENER_URL = 'SecondaryListenerURL',
      AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
      FAILOVER_MODE = MANUAL
    );

Replace [DistributedAGName] with the name of the Distributed AG, ‘PrimaryReplica’ with the name of the primary replica, ‘PrimaryListenerURL’ with the URL of the primary replica’s listener, ‘SecondaryReplica’ with the name of the secondary replica, and ‘SecondaryListenerURL’ with the URL of the secondary replica’s listener.

Step 4: Joining the Secondary AG to the Distributed AG

After creating the Distributed AG, you need to join the secondary AG to the Distributed AG. Use the following T-SQL script to join the secondary AG:

ALTER AVAILABILITY GROUP [DistributedAGName]
    JOIN
    AVAILABILITY GROUP ON
    'PrimaryReplica' WITH
    (
      LISTENER_URL = 'PrimaryListenerURL',
      AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
      FAILOVER_MODE = MANUAL
    ),
    'SecondaryReplica' WITH
    (
      LISTENER_URL = 'SecondaryListenerURL',
      AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
      FAILOVER_MODE = MANUAL
    );

Replace [DistributedAGName] with the name of the Distributed AG, ‘PrimaryReplica’ with the name of the primary replica, ‘PrimaryListenerURL’ with the URL of the primary replica’s listener, ‘SecondaryReplica’ with the name of the secondary replica, and ‘SecondaryListenerURL’ with the URL of the secondary replica’s listener.

Step 5: Performing the Database Migration

Once the Distributed AG is configured, you can proceed with the database migration. This involves modifying the availability mode of the Distributed AG, setting the Distributed AG role on the primary replica to SECONDARY, and failing over the Distributed AG to the secondary replica. Use the appropriate T-SQL scripts provided in the example article to perform these steps.

Step 6: Removing the Distributed AG

After the database migration is complete and the new environment is stable, you can remove the Distributed AG. Use the following T-SQL script to remove the Distributed AG:

DROP AVAILABILITY GROUP [DistributedAGName];

Replace [DistributedAGName] with the name of the Distributed AG.

Conclusion

Configuring SQL Server Distributed Availability Groups for database migration is a complex process that requires careful planning and attention to detail. By following the steps outlined in this blog post, you can successfully configure Distributed AGs and migrate your databases to a different Active Directory domain.