High Availability: RDS Multi-AZ for SQL Server

Sign in and create the Multi-AZ instance (about 40 minutes):

cd C:\aws-lab
aws login --profile saat-admin
$env:AWS_PROFILE = 'saat-admin'
$v = Get-Content C:\aws-lab\saat-vpc.json | ConvertFrom-Json
aws rds create-db-instance --db-instance-identifier saat-sql-ha --engine sqlserver-se --license-model license-included --db-instance-class db.m6i.large --multi-az --allocated-storage 20 --storage-type gp3 --storage-encrypted --master-username saatadmin --manage-master-user-password --db-subnet-group-name saat-db-subnets --vpc-security-group-ids $v.DbSg --no-publicly-accessible --backup-retention-period 1 --tags Key=course,Value=saatvedha-aws Key=lesson,Value=54 --query "DBInstance.DBInstanceStatus" --output text

You should see this:

creating

1Check the HA mode and create a database

Paste this whole block:

.\Start-SqlLab.ps1 -Db saat-sql-ha -LocalPort 11438
aws rds describe-db-instances --db-instance-identifier saat-sql-ha --query "DBInstances[0].[MultiAZ,AvailabilityZone,SecondaryAvailabilityZone,ListenerEndpoint.Address]" --output text
sqlcmd -S "127.0.0.1,11438" -U saatadmin -C -Q "EXEC msdb.dbo.rds_get_high_availability_option; CREATE DATABASE HaShop;"
sqlcmd -S "127.0.0.1,11438" -U saatadmin -C -d HaShop -Q "CREATE TABLE dbo.Pings (Id int IDENTITY PRIMARY KEY, At datetime2 DEFAULT SYSUTCDATETIME(), Note nvarchar(50)); SELECT recovery_model_desc FROM sys.databases WHERE name = 'HaShop';"

You should see something like this:

Tunnel ready on 127.0.0.1,11438 for saatadmin (password is in SQLCMDPASSWORD for this window)
True    ap-south-1a     ap-south-1b     saat-sql-ha.c1a2b3c4d5e6.ap-south-1.rds.amazonaws.com
high_availability_option
------------------------
Multi-AZ (AlwaysOn)
recovery_model_desc
-------------------
FULL

SQL Server 2022 Standard uses an Always On availability group, with the standby in ap-south-1b. HaShop joined the group automatically and is in FULL recovery, which Multi-AZ needs.

2Fail over while a client keeps writing

Paste this whole block. A background writer opens a fresh connection and inserts a row every second for 4 minutes; 30 seconds in, you force a failover:

function Get-SqlPw([string]$Db) {
  $a = aws rds describe-db-instances --db-instance-identifier $Db --query "DBInstances[0].MasterUserSecret.SecretArn" --output text
  (aws secretsmanager get-secret-value --secret-id $a --query SecretString --output text | ConvertFrom-Json).password
}
$b = New-Object System.Data.SqlClient.SqlConnectionStringBuilder
$b['Data Source'] = '127.0.0.1,11438'; $b['Initial Catalog'] = 'HaShop'; $b['User ID'] = 'saatadmin'; $b['Password'] = (Get-SqlPw saat-sql-ha)
$b['Encrypt'] = $true; $b['TrustServerCertificate'] = $true; $b['Pooling'] = $false; $b['Connect Timeout'] = 5
$cs = $b.ConnectionString
function Get-Az { aws rds describe-db-instances --db-instance-identifier saat-sql-ha --query "DBInstances[0].AvailabilityZone" --output text }
function Invoke-Failover { aws rds reboot-db-instance --db-instance-identifier saat-sql-ha --force-failover | Out-Null; Start-Sleep 60; aws rds wait db-instance-available --db-instance-identifier saat-sql-ha; Start-Sleep 30 }
$writer = {
  param($cs, $seconds)
  $end = (Get-Date).AddSeconds($seconds)
  while ((Get-Date) -lt $end) {
    $t = Get-Date
    try {
      $cn = New-Object System.Data.SqlClient.SqlConnection $cs; $cn.Open()
      $cm = $cn.CreateCommand(); $cm.CommandText = "INSERT dbo.Pings (Note) VALUES ('writer')"; [void]$cm.ExecuteNonQuery(); $cn.Close()
      [pscustomobject]@{ At = $t; Ok = $true }
    } catch { [pscustomobject]@{ At = $t; Ok = $false } }
    Start-Sleep -Seconds 1
  }
}
$before = Get-Az
$job = Start-Job -ScriptBlock $writer -ArgumentList $cs, 240
Start-Sleep 30
Invoke-Failover
$log = Receive-Job $job -Wait
$fails = @($log | Where-Object { -not $_.Ok })
$okCount = @($log | Where-Object { $_.Ok }).Count
if ($fails.Count) { "ok $okCount  failed $($fails.Count)  outage about $([int]($fails[-1].At - $fails[0].At).TotalSeconds + 1) s" } else { "ok $okCount  failed 0" }
"AZ before $before  after $(Get-Az)"
aws rds describe-events --source-type db-instance --source-identifier saat-sql-ha --duration 30 --query "Events[].Message" --output text

You should see something like this:

ok 176  failed 38  outage about 41 s
AZ before ap-south-1a  after ap-south-1b
Multi-AZ instance failover started.     Multi-AZ instance failover completed.

The client couldn’t write for roughly 40 seconds, then carried on against the same endpoint, now in the other zone. Every row that was acknowledged before the failover is still there, because commits were synchronous. An app with retry logic sees a pause; one without it sees errors and may need a restart.

✅ Checkpoint: rds_get_high_availability_option shows Multi-AZ (AlwaysOn), the writer recovered by itself after the forced failover, and the AZ changed from one zone to the other.