Migration Strategies from On-Premises SQL to IAAS: DAG and Rolling Upgrades

By /

Listen to this Post

When migrating from on-premises SQL Server to IAAS (Infrastructure as a Service), several strategies ensure minimal downtime and compatibility. Below are key methods, including Distributed Availability Groups (DAG) and Rolling Upgrades, with verified commands and steps.

  1. Using DAG for Instant Migration (SQL 2017 to 2022)
    Reference: DAG Migration to Azure VM/AWS/GCP

Steps:

1. Set up DAG on Source SQL 2017:

-- Create a distributed availability group 
CREATE AVAILABILITY GROUP [DAG_Migration] 
WITH (DISTRIBUTED) 
AVAILABILITY GROUP ON 
'Source_AG' WITH ( 
LISTENER_URL = 'TCP://Source_AG_Listener:5022', 
AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, 
FAILOVER_MODE = MANUAL, 
SEEDING_MODE = AUTOMATIC 
), 
'Target_AG' WITH ( 
LISTENER_URL = 'TCP://Target_AG_Listener:5022', 
AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, 
FAILOVER_MODE = MANUAL, 
SEEDING_MODE = AUTOMATIC 
);

2. Join Target SQL 2022 to DAG:

ALTER AVAILABILITY GROUP [DAG_Migration] 
JOIN AVAILABILITY GROUP ON 
'Target_AG' WITH ( 
LISTENER_URL = 'TCP://Target_AG_Listener:5022', 
AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, 
FAILOVER_MODE = MANUAL, 
SEEDING_MODE = AUTOMATIC 
);

3. Monitor synchronization:

SELECT ag.name, drs.database_id, drs.synchronization_state_desc 
FROM sys.dm_hadr_database_replica_states drs 
JOIN sys.availability_groups ag ON drs.group_id = ag.group_id;
  1. Rolling Upgrade (SQL 2014/2016/2017 to 2022 with Same Listener)
    Reference: Rolling Upgrade Guide

Key Commands:

  • Add new node without cluster validation: 
    Add-ClusterNode -Name "NewNode" -NoStorage -Force
  • Evict old node: 
    Stop-ClusterNode -Name "OldNode" -Force 
Remove-ClusterNode -Name "OldNode" -Force
  1. Rolling Upgrade with DAG (Listener Name Change Allowed)
    Reference: DAG Rolling Upgrade

Steps:

1. Create new AG on SQL 2022:

CREATE AVAILABILITY GROUP [New_AG] 
WITH (CLUSTER_TYPE = NONE) 
FOR REPLICA ON 
N'NewNode' WITH ( 
ENDPOINT_URL = N'TCP://NewNode:5022', 
AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, 
SEEDING_MODE = AUTOMATIC, 
FAILOVER_MODE = MANUAL 
);

2. Migrate databases:

ALTER AVAILABILITY GROUP [Old_AG] 
MODIFY REPLICA ON 'NewNode' WITH (SEEDING_MODE = AUTOMATIC);

You Should Know:

  • OS Compatibility: Windows Server supports only N-1 versions for cluster nodes.
  • Cluster Validation Skip: Necessary to avoid conflicts during migration.
  • Time Limit: Rolling upgrades should not exceed 4 weeks.

What Undercode Say:

Migrating SQL workloads to IAAS requires careful planning. DAG provides near-zero downtime, while rolling upgrades ensure compatibility. Always verify network latency and storage performance before migration. Use PowerShell (Test-NetConnection) and SQL DMVs (sys.dm_os_performance_counters) for pre-checks.

Expected Output:

  • Successful AG synchronization status.
  • Seamless failover to new nodes.
  • Minimal application downtime.

Relevant URLs:

References:

Reported By: Chiranjeevi Vamsy – Hackers Feeds
Extra Hub: Undercode MoN
Basic Verification: Pass ✅

Join Our Cyber World:

💬 Whatsapp | 💬 TelegramFeatured Image

Related Posts: