Create RDS for SQL Server

Install sqlcmd if you haven’t, then sign in in a new PowerShell window:

winget install Microsoft.Sqlcmd
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

1Create the subnet, parameter and option groups

Paste this whole block:

aws rds create-db-subnet-group --db-subnet-group-name saat-db-subnets --db-subnet-group-description "saat-vpc database subnets" --subnet-ids $v.DbA $v.DbB --tags Key=course,Value=saatvedha-aws --query "DBSubnetGroup.SubnetGroupStatus" --output text
aws rds create-db-parameter-group --db-parameter-group-name saat-sqlserver-ex-16 --db-parameter-group-family sqlserver-ex-16.0 --description "saat-sql parameters" --tags Key=course,Value=saatvedha-aws --query "DBParameterGroup.DBParameterGroupName" --output text
aws rds create-option-group --option-group-name saat-sqlserver-ex-16-og --engine-name sqlserver-ex --major-engine-version 16.00 --option-group-description "saat-sql options" --tags Key=course,Value=saatvedha-aws --query "OptionGroup.OptionGroupName" --output text

You should see this:

Complete
saat-sqlserver-ex-16
saat-sqlserver-ex-16-og

2Create saat-sql

Paste this whole block. Creation usually takes 15 to 25 minutes; the last command waits for it:

$v22 = aws rds describe-db-engine-versions --engine sqlserver-ex --query "DBEngineVersions[?starts_with(EngineVersion, '16.00')] | [-1].EngineVersion" --output text
aws rds create-db-instance --db-instance-identifier saat-sql --engine sqlserver-ex --engine-version $v22 --license-model license-included --db-instance-class db.t3.micro --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 --db-parameter-group-name saat-sqlserver-ex-16 --option-group-name saat-sqlserver-ex-16-og --backup-retention-period 1 --copy-tags-to-snapshot --enable-cloudwatch-logs-exports error agent --tags Key=course,Value=saatvedha-aws Key=Name,Value=saat-sql --query "DBInstance.DBInstanceStatus" --output text
aws rds wait db-instance-available --db-instance-identifier saat-sql
aws rds describe-db-instances --db-instance-identifier saat-sql --query "DBInstances[0].[DBInstanceStatus,Endpoint.Address,PubliclyAccessible,StorageEncrypted]" --output text

You should see something like this:

creating
available       saat-sql.c1a2b3c4d5e6.ap-south-1.rds.amazonaws.com    False   True

No public access, encrypted storage, and nobody ever typed the password. While you wait, write the two helper scripts in the next step.

3Write the tunnel helpers and connect

Paste this whole block. Start-SqlLab.ps1 starts what’s needed and opens the tunnel in a second window; Stop-SqlLab.ps1 cleans up:

@'
param([string]$Db = 'saat-sql', [int]$LocalPort = 11433)
$v = Get-Content C:\aws-lab\saat-vpc.json | ConvertFrom-Json
$env:Path += ';C:\Program Files\Amazon\SessionManagerPlugin\bin'
& C:\aws-lab\Start-SsmEndpoints.ps1
$st = aws rds describe-db-instances --db-instance-identifier $Db --query "DBInstances[0].DBInstanceStatus" --output text
if ($st -eq 'stopped') { aws rds start-db-instance --db-instance-identifier $Db | Out-Null }
aws rds wait db-instance-available --db-instance-identifier $Db
$ep = aws rds describe-db-instances --db-instance-identifier $Db --query "DBInstances[0].Endpoint.Address" --output text
$jump = aws ec2 describe-instances --filters "Name=tag:Name,Values=sqljump" "Name=instance-state-name,Values=running" --query "Reservations[0].Instances[0].InstanceId" --output text
if (-not $jump -or $jump -eq 'None') {
  $base = aws ssm get-parameter --name /aws/service/ami-amazon-linux-latest/al2023-ami-kernel-default-x86_64 --query Parameter.Value --output text
  $jump = aws ec2 run-instances --image-id $base --instance-type t3.micro --subnet-id $v.PrivateA --security-group-ids $v.AppSg --iam-instance-profile Name=saat-ec2-ssm-profile --metadata-options HttpTokens=required --tag-specifications 'ResourceType=instance,Tags=[{Key=Name,Value=sqljump},{Key=course,Value=saatvedha-aws}]' --query "Instances[0].InstanceId" --output text
}
do { Start-Sleep 15; $p = aws ssm describe-instance-information --filters "Key=InstanceIds,Values=$jump" --query "InstanceInformationList[0].PingStatus" --output text } until ($p -eq 'Online')
@{ host = @($ep); portNumber = @('1433'); localPortNumber = @("$LocalPort") } | ConvertTo-Json | Set-Content C:\aws-lab\tunnel.json
Start-Process powershell -ArgumentList '-NoExit', '-Command', "aws ssm start-session --target $jump --document-name AWS-StartPortForwardingSessionToRemoteHost --parameters file://C:/aws-lab/tunnel.json"
$secretArn = aws rds describe-db-instances --db-instance-identifier $Db --query "DBInstances[0].MasterUserSecret.SecretArn" --output text
$s = aws secretsmanager get-secret-value --secret-id $secretArn --query SecretString --output text | ConvertFrom-Json
$env:SQLCMDPASSWORD = $s.password
Start-Sleep 8
"Tunnel ready on 127.0.0.1,$LocalPort for $($s.username) (password is in SQLCMDPASSWORD for this window)"
'@ | Set-Content C:\aws-lab\Start-SqlLab.ps1
@'
param([string]$Db = 'saat-sql', [switch]$KeepDbRunning)
$jump = aws ec2 describe-instances --filters "Name=tag:Name,Values=sqljump" "Name=instance-state-name,Values=pending,running,stopped" --query "Reservations[].Instances[].InstanceId" --output text
if ($jump) { aws ec2 terminate-instances --instance-ids ($jump -split '\s+') | Out-Null }
& C:\aws-lab\Stop-SsmEndpoints.ps1
if (-not $KeepDbRunning) { aws rds stop-db-instance --db-instance-identifier $Db --query "DBInstance.DBInstanceStatus" --output text }
Remove-Item Env:SQLCMDPASSWORD -ErrorAction SilentlyContinue
"Close the tunnel window if it is still open."
'@ | Set-Content C:\aws-lab\Stop-SqlLab.ps1
.\Start-SqlLab.ps1
sqlcmd -S "127.0.0.1,11433" -U saatadmin -C -Q "SELECT SERVERPROPERTY('Edition') AS edition, SERVERPROPERTY('ProductVersion') AS version"

You should see something like this (and a second window showing “Waiting for connections…”):

Tunnel ready on 127.0.0.1,11433 for saatadmin (password is in SQLCMDPASSWORD for this window)
edition                     version
--------------------------- ------------
Express Edition (64-bit)    16.0.4265.3

-C trusts the server certificate for now; Lesson 44 sets up proper TLS checks. In SSMS you’d connect to 127.0.0.1,11433 with SQL Server authentication and the same login.

4Create a database and change a parameter

Paste this whole block:

sqlcmd -S "127.0.0.1,11433" -U saatadmin -C -Q "CREATE DATABASE SaatShop"
sqlcmd -S "127.0.0.1,11433" -U saatadmin -C -d SaatShop -Q "CREATE TABLE dbo.Orders (OrderId int IDENTITY PRIMARY KEY, Customer nvarchar(100), Amount decimal(10,2), CreatedAt datetime2 DEFAULT SYSUTCDATETIME()); INSERT dbo.Orders (Customer, Amount) VALUES (N'Asha', 2499.00), (N'Ravi', 1850.00), (N'Meena', 3200.00);"
sqlcmd -S "127.0.0.1,11433" -U saatadmin -C -d SaatShop -Q "SELECT OrderId, Customer, Amount FROM dbo.Orders"
'[ { "ParameterName": "optimize for ad hoc workloads", "ParameterValue": "1", "ApplyMethod": "immediate" } ]' | Set-Content adhoc.json
aws rds modify-db-parameter-group --db-parameter-group-name saat-sqlserver-ex-16 --parameters file://adhoc.json | Out-Null
Start-Sleep 60
sqlcmd -S "127.0.0.1,11433" -U saatadmin -C -h -1 -Q "SET NOCOUNT ON; SELECT value_in_use FROM sys.configurations WHERE name = 'optimize for ad hoc workloads'"

You should see something like this:

(3 rows affected)
OrderId     Customer   Amount
----------- ---------- ---------
1           Asha       2499.00
2           Ravi       1850.00
3           Meena      3200.00
1

The parameter is dynamic, so it applied without a restart. You couldn’t run sp_configure yourself on RDS for most settings; the parameter group is how you change them, and it applies the same settings to every instance that uses the group.

✅ Checkpoint: saat-sql is available, private and encrypted, the tunnel connects sqlcmd on 127.0.0.1,11433, SaatShop has three orders, and optimize for ad hoc workloads is 1. Keep everything for the break lab.