Always On Availability Groups Across AZs on EC2

1Deploy the availability group with Launch Wizard

In the console, open AWS Launch Wizard in ap-south-1, choose Microsoft SQL Server, then Create deployment. Use these settings and leave the rest at their defaults:

• Deployment type: Always On availability groups, on Windows.
• Application name: saat-ag; turn on SNS notifications to saat-alerts if you like.
• Infrastructure: create a new VPC across two Availability Zones, and a new AWS Managed Microsoft AD with domain corp.saatvedha.lab and NetBIOS name SAAT.
• SQL Server: SQL Server 2022 Developer on Windows Server 2022, two nodes, availability group name saat-ag, listener saat-agl, and a database named AgShop if the form offers one.
• Instance size: the smallest the form allows (for example m6i.large) with gp3 volumes.

Review the cost estimate, deploy, and come back in a couple of hours. When it shows Completed, connect to the first SQL node with Fleet Manager Remote Desktop (or the Remote Desktop Gateway it built) as the domain admin, and open PowerShell as administrator.

2Check the cluster, the AG and the listener

On SQLNODE1, paste this whole block (use your node names if Launch Wizard named them differently):

$n1 = 'SQLNODE1'; $n2 = 'SQLNODE2'
Get-ClusterNode | Select-Object Name, State
Get-ClusterResource | Where-Object ResourceType -eq 'IP Address' | Select-Object Name, State
sqlcmd -S $n1 -E -W -Q "SELECT ar.replica_server_name, rs.role_desc, rs.synchronization_health_desc, ar.availability_mode_desc FROM sys.dm_hadr_availability_replica_states rs JOIN sys.availability_replicas ar ON ar.replica_id = rs.replica_id"

You should see something like this:

Name     State
----     -----
SQLNODE1    Up
SQLNODE2    Up

Name                 State
----                 -----
Cluster IP 10.0.1.100   Online
Cluster IP 10.0.3.100  Offline
saat-ag_10.0.1.200      Online
saat-ag_10.0.3.200     Offline

replica_server_name role_desc synchronization_health_desc availability_mode_desc
------------------- --------- --------------------------- ----------------------
SQLNODE1            PRIMARY   HEALTHY                     SYNCHRONOUS_COMMIT
SQLNODE2            SECONDARY HEALTHY                     SYNCHRONOUS_COMMIT

Two nodes in two subnets, synchronous commit, and for each cluster name and the listener, one IP online (the primary’s subnet) and one offline. That offline IP is normal, and it’s the reason for Break 1.

3Add a database and data

If AgShop isn’t in the group yet, this block creates it and adds it with automatic seeding; if it is, it just adds rows:

sqlcmd -S $n1 -E -Q "IF DB_ID('AgShop') IS NULL BEGIN CREATE DATABASE AgShop; ALTER DATABASE AgShop SET RECOVERY FULL; BACKUP DATABASE AgShop TO DISK = 'NUL'; END"
sqlcmd -S $n1 -E -Q "IF NOT EXISTS (SELECT 1 FROM sys.availability_databases_cluster WHERE database_name = 'AgShop') BEGIN ALTER AVAILABILITY GROUP [saat-ag] MODIFY REPLICA ON N'$n2' WITH (SEEDING_MODE = AUTOMATIC); ALTER AVAILABILITY GROUP [saat-ag] ADD DATABASE AgShop; END"
sqlcmd -S $n2 -E -Q "ALTER AVAILABILITY GROUP [saat-ag] GRANT CREATE ANY DATABASE"
Start-Sleep 30
sqlcmd -S saat-agl -E -d AgShop -Q "IF OBJECT_ID('dbo.Orders') IS NULL CREATE TABLE dbo.Orders (OrderId int IDENTITY PRIMARY KEY, Customer nvarchar(100), Amount decimal(10,2)); INSERT dbo.Orders (Customer, Amount) VALUES (N'Asha', 2499.00), (N'Ravi', 1850.00), (N'Meena', 3200.00);" -M
sqlcmd -S $n2 -E -W -Q "SELECT DB_NAME(database_id) AS db, synchronization_state_desc FROM sys.dm_hadr_database_replica_states WHERE is_local = 1"

You should see something like this:

db     synchronization_state_desc
------ --------------------------
AgShop SYNCHRONIZED

AgShop was seeded to SQLNODE2 automatically, with no backup files to copy, and is SYNCHRONIZED. The -M switch tells sqlcmd to use MultiSubnetFailover when it connects to the listener.

4Fail over by hand

Run the failover on the secondary, then check the roles:

sqlcmd -S $n2 -E -Q "ALTER AVAILABILITY GROUP [saat-ag] FAILOVER;"
Start-Sleep 20
sqlcmd -S $n2 -E -W -Q "SELECT ar.replica_server_name, rs.role_desc FROM sys.dm_hadr_availability_replica_states rs JOIN sys.availability_replicas ar ON ar.replica_id = rs.replica_id"
sqlcmd -S saat-agl -E -M -d AgShop -W -Q "SELECT @@SERVERNAME AS now_serving, COUNT(*) AS orders FROM dbo.Orders"

You should see something like this:

replica_server_name role_desc
------------------- ---------
SQLNODE1            SECONDARY
SQLNODE2            PRIMARY
now_serving orders
----------- ------
SQLNODE2         3

SQLNODE2 is primary, the listener followed it to the other subnet, and the data is all there. A planned failover like this is how you patch Windows and SQL Server on AG nodes one at a time with only seconds of interruption.

✅ Checkpoint: Both cluster nodes are Up, the AG is HEALTHY and synchronous, AgShop is SYNCHRONIZED on SQLNODE2, and after a manual failover SQLNODE2 serves the listener with 3 orders.