Azure SQL Database → AWS RDS for SQL Server PRODUCTION

Overview & Scope

This runbook covers a live schema-plus-data migration from Azure SQL Database (PaaS) to AWS RDS for SQL Server using MigrationBridge. The same connection profile supports the reverse direction (RDS → Azure SQL) by swapping the Source and Target fields in Step 1.

What This SOP Covers

• Full-copy (bulk insert) migration of all user tables
• Live (Change-Tracking–based) CDC catch-up after full load
• Schema assessment: compatibility blockers, unsupported features
• Post-migration RDS hardening: backup retention, parameter groups, Multi-AZ, CloudWatch alarms
• Data integrity verification (row counts + checksum spot-checks)

What This SOP Does NOT Cover

• SSIS packages, Reporting Services, or Analysis Services objects
• Cross-database views or synonyms (must be recreated manually — see Known Issues)
• Azure SQL features unavailable on RDS: Always Encrypted column keys, Ledger tables, Hyperscale
• DNS cutover / application connection-string changes

Estimated Timelines

Prerequisites — Source (Azure SQL Database)

1. Create a Contained Migration User

Run the following against the source database (not master). Contained users are scoped to the database and do not require a server-level login — the preferred pattern on Azure SQL PaaS.

-- ─────────────────────────────────────────────────────────────────────────────
-- Azure SQL: create contained migration user with minimum required permissions
-- Run in: source database context  (NOT master)
-- ─────────────────────────────────────────────────────────────────────────────
USE [YourSourceDatabase];
GO

-- Create contained user mapped to Azure AD or SQL auth
CREATE USER [mb_migration] WITH PASSWORD = N'Str0ng!Pass#2026';
GO

-- Minimum read permissions
ALTER ROLE [db_datareader] ADD MEMBER [mb_migration];
GO

-- Required for assessment queries (blocking, sessions, query stats)
GRANT VIEW DATABASE STATE TO [mb_migration];
GO

-- Required for Change Tracking (live migration mode only)
GRANT VIEW CHANGE TRACKING TO [mb_migration];
GO

-- Verify
SELECT dp.name, dp.type_desc, drm.role_principal_id
FROM sys.database_principals dp
LEFT JOIN sys.database_role_members drm
    ON dp.principal_id = drm.member_principal_id
WHERE dp.name = N'mb_migration';

2. Azure SQL Firewall — Allow MigrationBridge Host

In the Azure portal or via Azure CLI, add the public IP of the MigrationBridge host.

# Azure CLI — add server-level firewall rule
az sql server firewall-rule create \
  --resource-group <your-rg> \
  --server <azure-sql-server-name> \
  --name MigrationBridgeHost \
  --start-ip-address <mb-host-public-ip> \
  --end-ip-address   <mb-host-public-ip>

3. Readiness Verification Queries

Run all three checks. All must return expected values before proceeding.

-- ① Connectivity & edition check
SELECT
    SERVERPROPERTY('Edition')        AS Edition,
    SERVERPROPERTY('EngineEdition')  AS EngineEdition,   -- 5 = Azure SQL DB
    SERVERPROPERTY('ProductVersion') AS ProductVersion,
    DB_NAME()                          AS CurrentDatabase;

-- ② Confirm user has required permissions
SELECT
    HAS_PERMS_BY_NAME(DB_NAME(), 'DATABASE', 'VIEW DATABASE STATE') AS has_view_db_state,
    IS_ROLEMEMBER('db_datareader')                                  AS is_datareader;

-- ③ Count user tables eligible for migration
SELECT
    COUNT(*) AS total_user_tables,
    SUM(p.rows) AS total_rows_approx
FROM sys.tables t
JOIN sys.partitions p
    ON t.object_id = p.object_id AND p.index_id IN (0,1)
WHERE t.is_ms_shipped = 0;

-- ④ Check Change Tracking status (required for live mode)
SELECT
    is_change_tracking_on,
    retention_period,
    retention_period_units_desc
FROM sys.change_tracking_databases
WHERE database_id = DB_ID();

Prerequisites — Target (AWS RDS for SQL Server)

1. Open VPC Security Group — TCP 1433

The RDS instance's Security Group must allow inbound TCP 1433 from the MigrationBridge host IP.

# AWS CLI — authorize inbound TCP 1433 from MigrationBridge host
aws ec2 authorize-security-group-ingress \
  --group-id <rds-sg-id> \
  --protocol tcp \
  --port 1433 \
  --cidr <mb-host-public-ip>/32

# Verify rule was added
aws ec2 describe-security-groups \
  --group-ids <rds-sg-id> \
  --query 'SecurityGroups[0].IpPermissions'

2. Create SQL Login on RDS

RDS for SQL Server does not support contained users in the same way as Azure SQL PaaS. Create a standard server-level login, then map it to a database user.

-- ─────────────────────────────────────────────────────────────────────────────
-- RDS: create server login + database user (NOT a contained user)
-- Run as: master admin login  (the one created during RDS provisioning)
-- ─────────────────────────────────────────────────────────────────────────────

-- Step A: create login at server scope
USE [master];
GO
CREATE LOGIN [mb_migration] WITH PASSWORD = N'Str0ng!Pass#2026',
    CHECK_POLICY = ON, CHECK_EXPIRATION = OFF;
GO

-- Step B: map to target database
USE [YourTargetDatabase];
GO
CREATE USER [mb_migration] FOR LOGIN [mb_migration];
GO

-- Step C: grant write + schema access for migration inserts
ALTER ROLE [db_datareader]  ADD MEMBER [mb_migration];
ALTER ROLE [db_datawriter]  ADD MEMBER [mb_migration];
ALTER ROLE [db_ddladmin]    ADD MEMBER [mb_migration];
GRANT VIEW DATABASE STATE TO [mb_migration];
GO

-- Verify
SELECT dp.name, rp.name AS role_name
FROM sys.database_role_members drm
JOIN sys.database_principals dp ON drm.member_principal_id = dp.principal_id
JOIN sys.database_principals rp ON drm.role_principal_id   = rp.principal_id
WHERE dp.name = N'mb_migration';

3. Test Connectivity from MigrationBridge Host

# PowerShell — test TCP 1433 reachability to RDS endpoint
Test-NetConnection -ComputerName <rds-endpoint>.rds.amazonaws.com `
                   -Port 1433 -InformationLevel Detailed

# Also test Azure SQL endpoint
Test-NetConnection -ComputerName <server>.database.windows.net `
                   -Port 1433 -InformationLevel Detailed

4. RDS Instance Checklist

Enter connection details for both databases. MigrationBridge tests both connections before allowing you to proceed to assessment. All credentials are encrypted in memory and never written to disk.

MigrationBridge interrogates both engines and flags objects that require manual remediation before or after migration. Resolve all Blocker items before proceeding to Step 3.

Assessment SQL — Run Manually for Cross-DB References

-- Find views and procedures with 3-part (cross-database) object references
SELECT
    o.type_desc        AS ObjectType,
    o.name             AS ObjectName,
    sm.definition
FROM sys.sql_modules sm
JOIN sys.objects     o ON sm.object_id = o.object_id
WHERE sm.definition LIKE '%[DatabaseName]%.%'   -- replace [DatabaseName]
   OR sm.definition LIKE '%].dbo.[%'
ORDER BY o.type_desc, o.name;

Choose which tables to include and select the migration mode. Full Copy performs a complete bulk-insert of all rows. Incremental (CT) applies only changes since the last sync version (requires Change Tracking). Schema Only creates tables without migrating data.

Table Selection (9 of 47 shown)

47 total tables · 47 selected · 1 requires temporal prep · Estimated total: 33.8 GB

Click Start Migration to begin the full-copy transfer. MigrationBridge streams rows in configurable batches (default 10,000) using SqlBulkCopy with table-lock hints for maximum throughput. Do not close the application during migration.

Live Log Panel

Monitor from CLI (Headless)

# PowerShell — tail the MigrationBridge log file in real time
Get-Content -Path "C:\MigrationT\Migrationtool\Output\migration_$(Get-Date -f yyyyMMdd).log" `
            -Wait -Tail 20

After migration completes, run the row-count spot-check and optional checksum verification to confirm data fidelity. Do not perform cutover until all critical tables pass verification.

Row Count Spot-Check — Source (Azure SQL)

-- Run on SOURCE (Azure SQL Database)
SELECT
    QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' + QUOTENAME(t.name) AS TableName,
    SUM(p.rows)                                                       AS RowCount_Source,
    SUM(a.total_pages) * 8 / 1024.0                                  AS SizeMB_Source
FROM sys.tables t
JOIN sys.partitions      p ON t.object_id = p.object_id AND p.index_id IN (0,1)
JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE t.is_ms_shipped = 0
GROUP BY t.schema_id, t.name
ORDER BY SUM(p.rows) DESC;

Row Count Spot-Check — Target (RDS)

-- Run on TARGET (AWS RDS for SQL Server) — identical query
SELECT
    QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' + QUOTENAME(t.name) AS TableName,
    SUM(p.rows)                                                       AS RowCount_Target,
    SUM(a.total_pages) * 8 / 1024.0                                  AS SizeMB_Target
FROM sys.tables t
JOIN sys.partitions      p ON t.object_id = p.object_id AND p.index_id IN (0,1)
JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE t.is_ms_shipped = 0
GROUP BY t.schema_id, t.name
ORDER BY SUM(p.rows) DESC;

Checksum Verification (Critical Tables)

-- Compute checksum for a critical table — run on BOTH source and target
-- Results must match exactly
SELECT
    COUNT(*)                      AS RowCount,
    CHECKSUM_AGG(BINARY_CHECKSUM(*)) AS TableChecksum
FROM dbo.BgClaim;

-- For large tables: checksum a representative sample
SELECT
    CHECKSUM_AGG(BINARY_CHECKSUM(ClaimId, MemberId, ClaimDate, TotalAmount, StatusCode))
        AS SampleChecksum
FROM (
    SELECT TOP 100000 ClaimId, MemberId, ClaimDate, TotalAmount, StatusCode
    FROM dbo.BgClaim
    ORDER BY ClaimId
) s;

Verification Results

After successful verification, harden the RDS instance for production: extend backup retention, tune the parameter group, enable Multi-AZ, and configure CloudWatch alarms.

1. Extend Automated Backup Retention to 35 Days

# AWS CLI — extend backup retention for PITR coverage
aws rds modify-db-instance \
  --db-instance-identifier acmecorp-rds \
  --backup-retention-period 35 \
  --apply-immediately

# Verify new retention setting
aws rds describe-db-instances \
  --db-instance-identifier acmecorp-rds \
  --query 'DBInstances[0].BackupRetentionPeriod'

2. Custom Parameter Group — SQL Server Tuning

# Step A: create custom parameter group
aws rds create-db-parameter-group \
  --db-parameter-group-name acmecorp-sqlserver-2019-pg \
  --db-parameter-group-family sqlserver-se-15.0 \
  --description "Acme Corp production tuning — SQL Server 2019 SE"

# Step B: set cost threshold for parallelism (default 5 is too low for OLAP)
aws rds modify-db-parameter-group \
  --db-parameter-group-name acmecorp-sqlserver-2019-pg \
  --parameters \
    ParameterName="cost threshold for parallelism",ParameterValue="50",ApplyMethod="immediate"

# Step C: cap max degree of parallelism (8 vCPU instance)
aws rds modify-db-parameter-group \
  --db-parameter-group-name acmecorp-sqlserver-2019-pg \
  --parameters \
    ParameterName="max degree of parallelism",ParameterValue="4",ApplyMethod="immediate"

# Step D: attach parameter group to instance
aws rds modify-db-instance \
  --db-instance-identifier acmecorp-rds \
  --db-parameter-group-name acmecorp-sqlserver-2019-pg \
  --apply-immediately

# Step E: verify parameters applied
aws rds describe-db-parameters \
  --db-parameter-group-name acmecorp-sqlserver-2019-pg \
  --query "Parameters[?ParameterName=='cost threshold for parallelism' || ParameterName=='max degree of parallelism']"

3. Enable Multi-AZ Standby

# Enable Multi-AZ (synchronous standby in secondary AZ)
# NOTE: This triggers a brief failover (~60s) during the conversion window
aws rds modify-db-instance \
  --db-instance-identifier acmecorp-rds \
  --multi-az \
  --apply-immediately

# Monitor status until "available"
aws rds describe-db-instances \
  --db-instance-identifier acmecorp-rds \
  --query 'DBInstances[0].{Status:DBInstanceStatus,MultiAZ:MultiAZ}'

4. CloudWatch Alarms — Three Critical Metrics

# Alarm 1: High CPU Utilization (> 80% for 5 consecutive minutes)
aws cloudwatch put-metric-alarm \
  --alarm-name "acmecorp-rds-cpu-high" \
  --alarm-description "RDS SQL Server CPU > 80% for 5 min" \
  --metric-name CPUUtilization \
  --namespace AWS/RDS \
  --statistic Average \
  --period 60 \
  --evaluation-periods 5 \
  --threshold 80 \
  --comparison-operator GreaterThanThreshold \
  --dimensions Name=DBInstanceIdentifier,Value=acmecorp-rds \
  --alarm-actions arn:aws:sns:us-east-1:<account-id>:dba-alerts \
  --ok-actions     arn:aws:sns:us-east-1:<account-id>:dba-alerts

# Alarm 2: Low Free Storage (< 20 GB)
aws cloudwatch put-metric-alarm \
  --alarm-name "acmecorp-rds-storage-low" \
  --alarm-description "RDS SQL Server free storage < 20 GB" \
  --metric-name FreeStorageSpace \
  --namespace AWS/RDS \
  --statistic Average \
  --period 300 \
  --evaluation-periods 3 \
  --threshold 21474836480 \
  --comparison-operator LessThanThreshold \
  --dimensions Name=DBInstanceIdentifier,Value=acmecorp-rds \
  --alarm-actions arn:aws:sns:us-east-1:<account-id>:dba-alerts

# Alarm 3: High Connection Count (> 450 connections)
aws cloudwatch put-metric-alarm \
  --alarm-name "acmecorp-rds-connections-high" \
  --alarm-description "RDS SQL Server connection count > 450" \
  --metric-name DatabaseConnections \
  --namespace AWS/RDS \
  --statistic Average \
  --period 60 \
  --evaluation-periods 3 \
  --threshold 450 \
  --comparison-operator GreaterThanThreshold \
  --dimensions Name=DBInstanceIdentifier,Value=acmecorp-rds \
  --alarm-actions arn:aws:sns:us-east-1:<account-id>:dba-alerts

# Verify all three alarms
aws cloudwatch describe-alarms \
  --alarm-name-prefix "acmecorp-rds-" \
  --query 'MetricAlarms[].{Name:AlarmName,State:StateValue}'

Change Tracking Setup (Live Migration)

Change Tracking (CT) enables MigrationBridge to apply row-level changes (INSERT / UPDATE / DELETE) that occurred on the source during the full-copy phase, minimizing the cutover window. The sequence is: enable CT → start full copy → after full copy, apply CT delta → cutover.

Step A — Enable CT on the Source Database

-- Run on SOURCE (Azure SQL Database) — in source database context
USE [EndriasBridgeWH];
GO

ALTER DATABASE [EndriasBridgeWH]
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 30 DAYS, AUTO_CLEANUP = ON);
GO

Step B — Enable CT on Each Table

-- Enable CT on all user tables (run once per table; adjust schema/name)
ALTER TABLE dbo.BgClaim         ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);
ALTER TABLE dbo.BgMember        ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);
ALTER TABLE dbo.BgProvider      ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);
ALTER TABLE dbo.BgAuthorization ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);
ALTER TABLE ref.DrugFormulary   ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);
-- Repeat for all tables requiring live sync (not needed for static reference tables)

Step C — Grant VIEW CHANGE TRACKING to Migration User

-- Grant the migration user access to read change tracking data
GRANT VIEW CHANGE TRACKING ON SCHEMA::dbo   TO [mb_migration];
GRANT VIEW CHANGE TRACKING ON SCHEMA::ref   TO [mb_migration];
GRANT VIEW CHANGE TRACKING ON SCHEMA::hist  TO [mb_migration];
GRANT VIEW CHANGE TRACKING ON SCHEMA::audit TO [mb_migration];
GRANT VIEW CHANGE TRACKING ON SCHEMA::cfg   TO [mb_migration];

Step D — Validate CT State

-- Confirm CT is active on database
SELECT
    d.name                        AS DatabaseName,
    ct.is_change_tracking_on,
    ct.retention_period,
    ct.retention_period_units_desc,
    ct.is_auto_cleanup_on,
    CHANGE_TRACKING_CURRENT_VERSION() AS CurrentVersion
FROM sys.databases d
JOIN sys.change_tracking_databases ct ON d.database_id = ct.database_id
WHERE d.name = DB_NAME();

-- Confirm CT is enabled on specific tables
SELECT
    SCHEMA_NAME(t.schema_id) + '.' + t.name AS TableName,
    ct.is_track_columns_updated_on,
    ct.min_valid_version
FROM sys.tables t
JOIN sys.change_tracking_tables ct ON t.object_id = ct.object_id
ORDER BY t.name;

UI Busy / Unresponsive During Migration

During large table transfers (tens of millions of rows), the MigrationBridge UI may become temporarily unresponsive as the worker threads are CPU- and I/O-bound. This is expected behavior and does not indicate a crash or hang.

Symptoms

• Progress bar appears frozen for 1–5 minutes
• Windows reports "Not Responding" in the title bar
• Log panel stops scrolling
• Throughput tiles show stale values

Recommended Actions

1. Wait at least 5 minutes before concluding the application is hung
2. Check the log file directly — if it is still being written, migration is running normally
3. Check Task Manager: if migrationbridge.exe CPU is above 20%, transfer is active
4. Do NOT close or force-terminate the process — this will interrupt the current table transfer
5. If the log file has not changed for > 15 minutes AND CPU is at 0%, the process may be deadlocked — see below

Headless CLI Tip — Run Without the UI

For very large migrations, run MigrationBridge in headless mode from a terminal to avoid UI overhead entirely. All progress is written to the log file and can be monitored with PowerShell.

# Launch MigrationBridge in headless mode using a saved profile
python "C:\MigrationT\Migrationtool\launch_gui.pyw" --headless --profile EndriasBridgeWH_AzureToRDS.json

# Monitor progress in real time
Get-Content -Path "C:\MigrationT\Migrationtool\Output\migration_$(Get-Date -f yyyyMMdd).log" `
            -Wait -Tail 20

# Check for errors only
Select-String -Path "C:\MigrationT\Migrationtool\Output\*.log" -Pattern "ERROR|FAIL"

If the Process Is Truly Hung

# PowerShell — check if MigrationBridge has an open SQL connection
Get-Process migrationbridge | Select-Object Name, CPU, WorkingSet64, Responding

-- T-SQL on target RDS — check for blocking sessions
SELECT
    r.session_id,
    r.blocking_session_id,
    r.command,
    r.status,
    r.wait_type,
    r.wait_time / 1000.0 AS wait_sec,
    SUBSTRING(st.text, r.statement_start_offset / 2 + 1,
        (CASE r.statement_end_offset WHEN -1 THEN LEN(CONVERT(nvarchar(max), st.text)) * 2
             ELSE r.statement_end_offset END - r.statement_start_offset) / 2
    ) AS CurrentStatement
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) st
WHERE r.session_id > 50  -- exclude system sessions
ORDER BY r.blocking_session_id DESC;

Known Issues & Limitations

Data Type Conversions — Azure SQL → RDS

Azure SQL Database and AWS RDS for SQL Server are both SQL Server engines; the vast majority of data types are identical. The table below documents 1:1 pass-throughs and the auto-upgrades applied by MigrationBridge for deprecated types.