SQL Server Migration using Distributed Availability Groups

Introduction

SQL Server Distributed Availability Group (DAG)  is a special type of AG that sits on top of existing Availability Groups configured on On-Premise SQL Server instances or Azure VM’s or AWS EC2. DAG enables cross-region, cross-cloud, cross datacenter DR for SQL Server databases. Also this helps in the ease of migrations of large scale SQL Server Databases to desired cloud platforms with minimal cutover window and downtime. In this document we will learn how we can migrate from SQL Server on Azure/AWS or on-premises to Tessell SQL Server HA using a Distributed Availability Group.

Prerequisites

1. In case the source is SQL Server on Azure VM and is configured with DNN (distributed network name) then DAG cannot be configured on top of AG.
2. Source - Availability Group to be configured.
3. Source Availability Group Listener.
4. Connectivity between Source Infra (Cloud / On prim)and Tessell VPC. VPC peering or  AWS Direct or Azure ExpressRoute etc should be configured. Network Ports Whitelist - 5022 (DB Mirroring), 1433 SQL Port,  59999 (AG Listener Port or whichever we assign).

Create DAG and Migrate to Tessell SQLServer in 3 steps:

In below illustration, we will use following resources:

1. Source Node 1 (SQL Server 2022 Dev Edition on Azure VM)- 10.10.0.4
2. Source Node 2 (Primary) (SQL Server 2022 Dev Edition on Azure VM)- 10.10.0.5
3. Source Availability Group - SourceAAG
4. Source databases with total combined size of 55 GB:  [db1] [tessell] [stackoverflow]
5. Source Availability Listener - source-sql-2019
6. Tessell Node 1 (Primary) (Tessell SQL Server 2022 Dev Edition on Azure VM) - 10.10.0.6
7. Tessell Node 2 (Tessell SQL Server 2022 Dev Edition on Azure VM) - 10.10.0.7
8. Tessell HA Service DNS  priyanksqlazure2022-tfyqi.cs.tessell-stage.com
9. Tessell Availability Group - TessellAAG

<p class="info">Source and Target Availability Listeners which are are used for automatic routing to Primary node in  High Availability AlwaysON Cluster also needs to be configured.</br></br>
If the source is SQL Server in Azure VM, we can configure AG listener with Azure Load Balancer using this link.</br></br>
In this illustration we configured source availability listener source-sql-2019 with Azure Load Balancer.</br></br>
Tessell SQL Server comes by default with High Availability Service DNS which routes read/write traffic automatically to Primary node within Tessell HA for SQL Server essentially providing the feature similar to AG Listener.
</p>

1. Provision Tessell SQL Server with Multi Availability Zone (HA) and make it ready for DAG

Once the instance is Ready, we need to modify the Tessell instance to make it suitable for DAG Target.

For DAG, no user databases should exist in target and hence we need to drop default databases that are created in Tessell. Follow below steps:

<p class="info">Following actions will disable Tessell AM/Dataflix for Target Tessell SQL Server Instances and SLA may be affected. This is only for migration. We need to restore the Triggers, Metadata info once the migration/cutover is complete to Tessell.</p>

1. Obtain SA login or sysadmin privilege or Genie (Can be obtained from Tessell Support)
2. Connect to instance via SSMS and disable the following Server Level Triggers. (Triggers are in place to restrict the user for creating/alter the database other than Tessell UI. This restriction helps in  keeping the Tessell Availabilty Machine / Dataflix and SLA.)
3. Remove and Drop databases
   • db1 or user-specified-db-during-provisioning and tessell  database (used for internal login audits).
   • Remove the database from Availability Group first and then Delete or Drop the databases.

2. CREATE Distributed Availability Group and initiate SEEDING to Target

Run below T-SQL script in Source - Primary Replica to create DISTRIBUTED AG with name as tessell_dag

CREATE AVAILABILITY GROUP tessell_dag  
   WITH (DISTRIBUTED)   
   AVAILABILITY GROUP ON  
      'SourceAAG' WITH    
      (   
         LISTENER_URL = 'tcp://source-sql-2019.tessell-internal.com:5022',    
         AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,   
         FAILOVER_MODE = MANUAL,   
         SEEDING_MODE = AUTOMATIC   
      ),   
      'TessellAAG' WITH    
      (   
         LISTENER_URL = 'tcp://priyanksqlazure2022-tfyqi.cs.tessell-stage.com:5022',   
         AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,   
         FAILOVER_MODE = MANUAL,   
         SEEDING_MODE = AUTOMATIC   
      );    
GO

Distributed AG tessell_dag is a combination of Source AG - SourceAAG and Target AG - TessellAAG

‍

<p class="info">We are creating DAG with SEEDING_MODE = AUTOMATIC.  This method replicates the database via automatic backup from source and restore to target.</br></br>
Alternatively we can use SEEDING_MODE = MANUAL where we need to copy the backup files and subsequent log backup files and restore to target.</br></br>
We can consider AUTOMATIC when the combined database size is less then 1 TB. Over above 1TB we can consider faster approach of shipping the backups (like storing the backups directly to BLOB storage and Retrieving to target).</br></br>
More info in this link.
</p>

Run Below T-SQL Script on Tessell Primary Instance

ALTER AVAILABILITY GROUP tessell_dag  
   JOIN   
   AVAILABILITY GROUP ON  
      'SourceAAG' WITH    
      (   
         LISTENER_URL = 'tcp://source-sql-2019.tessell-internal.com:5022',    
         AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,   
         FAILOVER_MODE = MANUAL,   
         SEEDING_MODE = AUTOMATIC   
      ),   
      'TessellAAG' WITH    
      (   
         LISTENER_URL = 'tcp://priyanksqlazure2022-tfyqi.cs.tessell-stage.com:5022',   
         AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,   
         FAILOVER_MODE = MANUAL,   
         SEEDING_MODE = AUTOMATIC   
      );    
GO

This will join the Distributed AG we created in above step. And this will also start the SEEDING of databases from source to target. Refer below screenshots:

Once all the databases are in sync with both the Tessell instances, we are now ready for failover.

3. Failover Distributed Availability group tessell_dag  to Tessell Primary Replica

Run below T-SQL on both Source - Primary (10.10.0.5) and Tessell - Primary (10.10.0.6)

-- sets the distributed availability group to synchronous commit 
 ALTER AVAILABILITY GROUP tessell_dag 
 MODIFY 
 AVAILABILITY GROUP ON
 'SourceAAG' WITH 
  ( 
  AVAILABILITY_MODE = SYNCHRONOUS_COMMIT 
  ), 
  'TessellAAG' WITH  
  ( 
  AVAILABILITY_MODE = SYNCHRONOUS_COMMIT 
  );

Run below T-SQL to check Status if the AGs are in SYNCHRONIZED State in tessell_dag.

‍

 -- verifies the commit state of the distributed availability group
 select ag.name, ag.is_distributed, ar.replica_server_name, ar.availability_mode_desc, ars.connected_state_desc, ars.role_desc, 
 ars.operational_state_desc, ars.synchronization_health_desc from sys.availability_groups ag  
 join sys.availability_replicas ar on ag.group_id=ar.group_id
 left join sys.dm_hadr_availability_replica_states ars
 on ars.replica_id=ar.replica_id
 where ag.is_distributed=1
 GO

We will now stop the transactions in Source Primary. Below T-SQL command will make distributed AG unavailable in the Source - Primary

ALTER AVAILABILITY GROUP tessell_dag SET (ROLE = SECONDARY); 

Run below T-SQL to get the last_hardened_lsn of databases in both AG's in DAG tessell_dag  and Failover Readiness of DAG

  -- Run this query on the Global Primary and the forwarder
 -- Check the results to see if the last_hardened_lsn is the same per database on both the global primary and forwarder 
 -- The availability group is ready to fail over when the last_hardened_lsn is the same for both availability groups per database
 --
 SELECT ag.name, 
     drs.database_id, 
     db_name(drs.database_id) as database_name,
     drs.group_id, 
     drs.replica_id,
     drs.last_hardened_lsn
 FROM sys.dm_hadr_database_replica_states drs
 INNER JOIN sys.availability_groups ag ON drs.group_id = ag.group_id;

Run below T-SQL to Tessell - Primary  to perform failover

ALTER AVAILABILITY GROUP tessell_dag FORCE_FAILOVER_ALLOW_DATA_LOSS;

We can now drop Distributed AG.

DROP AVAILABILITY GROUP [tessell_dag]

We successfully migrated the databases from Source to Tessell SQL Server using above steps

References

What is a distributed availability group - SQL Server Always On

Configure a distributed availability group - SQL Server Always On