Database migration
Plan and rehearse a SQL Server migration to AWS: choose between RDS for SQL Server, Babelfish for Aurora PostgreSQL and a full conversion; convert schemas with DMS Schema Conversion; move data with AWS DMS full load plus CDC; and prove correctness with independent validation before cut-over.
Assumes: .NET 10 LTS · Microsoft.Data.SqlClient 7.1.2 · Npgsql 10.0.3 · AWS SDK for .NET v4 (AWSSDK.DatabaseMigrationService 4.1.0.2) · SQL Server 2022 · PostgreSQL 17 · Babelfish for Aurora PostgreSQL 6.x · Region us-east-1. Verified against official sources on 2026-11-18.
Learning objectives
By the end of this lesson you can:
- Choose a migration path for a SQL Server database — rehost, replatform to RDS for SQL Server, Babelfish for Aurora PostgreSQL, or convert to Aurora/RDS PostgreSQL — with licence, effort and risk trade-offs.
- Use DMS Schema Conversion to assess and convert T-SQL schemas and code, including its generative-AI and offline options, and know what still needs a human.
- Configure AWS DMS for full load plus change data capture (CDC) from SQL Server, with table mappings, LOB settings and built-in validation.
- Build an independent validation tool in C# that reconciles row counts, control totals and row hashes between source and target.
- Write a cut-over and rollback runbook that a bank's change board will approve.
Prerequisites
- Transactions, isolation levels and consistency — isolation differences matter after migration
- Performance optimisation — plans change when engines change
- The RDS, Aurora and EF Core lessons of this module (see the module overview)
- Module 13: Microsoft workload migration for the wider application-migration picture
- Docker Desktop (SQL Server 2022 Developer and PostgreSQL 17 containers), .NET 10 SDK
Why this matters in banking
Harbourline's HarbourlineLedger database runs on SQL Server 2016 Enterprise in the data centre: 600 GB, 180 tables, 420 stored procedures, 30 SQL Agent jobs, change tracking for a reporting feed, and a hardware refresh due next year. Licences are a large line item. The CIO wants it "in AWS by Q3".
A bank cannot afford a migration that loses a posting, changes a rounding rule, or produces a different month-end total. Equally, a "big bang" weekend with four hours of downtime may not be acceptable for card authorisations. The regulators expect a tested plan, evidence of data integrity, a rollback path and controls over who touched production data during the move.
This lesson is the playbook: choose the path, convert the schema, replicate the data with CDC, prove it is identical, and cut over with a rollback plan.
The idea in plain language
A database migration has three separate problems:
- Schema and code: tables, indexes, constraints, views, stored procedures, functions, triggers, jobs. If the engine changes (SQL Server → PostgreSQL), these must be converted.
- Data: copy what exists (full load), then keep copying changes made while you test (CDC — change data capture), until the moment you switch.
- Application and operations: connection strings, drivers, SQL inside the application, isolation behaviour, monitoring, backups, runbooks.
AWS tools map to these: DMS Schema Conversion for (1), AWS Database Migration Service (DMS) for (2), and your engineering for (3). Babelfish for Aurora PostgreSQL shrinks (1) and (3) by letting PostgreSQL understand T-SQL and SQL Server's wire protocol (TDS) — for the parts it supports.
Analogy: Moving a bank branch to a new building. Converting the schema is re-fitting the vault for the new building's walls. DMS full load is the armoured van moving the boxes; CDC is a courier running between the old and new branch carrying every new deposit while the old one stays open. Cut-over is the morning you lock the old door — after counting every box in both buildings and getting the same total.
What problem each AWS tool solves
| Tool | Solves | Honest limits (checked 2026-11-18) |
|---|---|---|
| AWS DMS (replication instances or DMS Serverless) | Full load, CDC, ongoing replication between many engines; built-in data validation; premigration assessments | Migrates data and basic table structure, not procedures, users, jobs or most secondary objects; LOB handling needs tuning; CDC needs source prerequisites |
| DMS Schema Conversion | Assessment report and automatic conversion of schema and code (SQL Server → Aurora/RDS PostgreSQL, among others) inside the DMS console; no additional charge for the feature | Complex T-SQL needs manual work; generative-AI conversion (since December 2024) marks results for human review and uses cross-Region inference — a data-residency question for banks |
| DMS Schema Conversion offline mode (July 2026) | Assess and convert from DDL scripts in S3 (e.g. exported with SSMS/SMO) without connecting to the source | You must export complete, current DDL; no live metadata |
| AWS Schema Conversion Tool (SCT) | The older desktop tool | Still documented; AWS's investment is in DMS Schema Conversion ⚠ VERIFY current SCT status before relying on it |
| Babelfish for Aurora PostgreSQL | Aurora PostgreSQL accepts TDS connections (port 1433) and much of T-SQL, so many apps connect with minimal changes | Not full SQL Server: some features, system procedures and behaviours are unsupported or differ; assess with the Babelfish Compass tool; migration mode (single-db vs multi-db) is fixed at creation |
| DMS Fleet Advisor | Database inventory | End of support 20 May 2026 — do not plan with it; AWS points customers to Migration Evaluator ⚠ VERIFY current inventory tooling |
Microsoft / Azure comparison
| You know (Microsoft/Azure) | AWS equivalent | What's different |
|---|---|---|
| Data Migration Assistant (DMA) assessment | DMS Schema Conversion assessment report / premigration assessments; Babelfish Compass | Cloud-hosted; also targets PostgreSQL, not only SQL Server |
| SQL Server Migration Assistant (SSMA) | DMS Schema Conversion | Managed in the console; generative-AI assisted conversion |
| Azure Database Migration Service (online migration) | AWS DMS full load + CDC | Similar concept; DMS supports many source/target pairs |
| Log shipping / backup-restore to Azure SQL MI | Native backup to S3 and restore into RDS for SQL Server | Best for like-for-like SQL Server moves; minimal downtime needs CDC or log shipping alternatives |
| Transactional replication / CDC | DMS CDC reading MS-CDC or MS-Replication from the source | DMS reads the transaction log via these features |
| Azure SQL MI (near 100% compatibility) | RDS for SQL Server | Licence-included or bring-your-own options vary by edition ⚠ VERIFY; some features (e.g. certain server-level permissions) are restricted |
| Azure Database for PostgreSQL | RDS for PostgreSQL / Aurora PostgreSQL / Babelfish | Babelfish has no Azure equivalent |
Choosing the path
| Path | Licence cost | Code change | Risk | Typical Harbourline use |
|---|---|---|---|---|
| Rehost (SQL Server on EC2) | Highest; you manage everything | None | Low technical, high operational | Only for vendor products that require OS access |
| Replatform (RDS for SQL Server) | Licence-included (or bring-your-own where eligible ⚠ VERIFY); Enterprise edition is expensive | Minimal | Low | First move under deadline; ledger stays T-SQL |
| Babelfish | PostgreSQL pricing (no SQL Server licence) | Low to medium; unsupported features need rewrite | Medium: subtle behaviour differences | Internal apps with mostly standard T-SQL |
| Convert to Aurora/RDS PostgreSQL | PostgreSQL pricing | High: procedures, app SQL, data types, tests | Medium–high, best long-term | Strategic platform; new services already on PostgreSQL |
Harbourline's decision: replatform the ledger to RDS for SQL Server first (deadline and risk), convert the reporting database to Aurora PostgreSQL now (simpler code, big licence saving), and evaluate Babelfish for three internal tools.
Architecture
Security-boundary view
- The migration account is separate from production workloads; access is time-bound and logged.
- DMS endpoints read credentials from Secrets Manager (DMS supports secret-based endpoint credentials with an IAM role), not from typed passwords.
- Source connectivity over private links (VPN/Direct Connect); DMS replication instance not publicly accessible.
- TLS on source and target endpoints (
ssl-mode/SslMode) and KMS encryption for the replication instance storage and target. - DMS task logs to CloudWatch with retention; CloudTrail records who started, stopped or modified tasks.
- Generative-AI conversion features send schema code (not data) to a model via cross-Region inference — get a data-residency decision before enabling it.
Step 1 — assess
- Inventory objects and sizes: tables, row counts, LOB columns, procedures, functions, triggers, jobs, linked servers, CLR assemblies, cross-database queries.
- Run the DMS Schema Conversion assessment (online, or offline from DDL scripts) — it produces a report with conversion percentages and action items by complexity.
- For Babelfish, run Babelfish Compass on the exported DDL to list unsupported features.
- Run a DMS premigration assessment for the planned task (checks unsupported data types, missing primary keys, LOB settings, CDC prerequisites).
Step 2 — convert schema and code
Typical T-SQL → PostgreSQL conversions you will review:
| SQL Server | PostgreSQL | Watch out for |
|---|---|---|
NVARCHAR(n) / VARCHAR(n) | varchar(n) or text (UTF-8) | Length semantics (characters vs bytes); text is fine and common |
DATETIME / DATETIME2 | timestamp (or timestamptz for UTC instants) | DATETIME rounds to ~3 ms; decide time zones explicitly |
MONEY | numeric(19,4) | Never double precision; check rounding in calculations |
BIT | boolean | App code that treats it as 0/1 |
UNIQUEIDENTIFIER | uuid | Sort order differs |
IDENTITY(1,1) | GENERATED ALWAYS AS IDENTITY | Reseed sequences after data load (setval) |
Case-insensitive collation (…_CI_AS) | citext or an ICU non-deterministic collation | Unique constraints and LIKE behave differently; test lookups by email/name |
TOP (n) | LIMIT n | — |
ISNULL(a,b) | COALESCE(a,b) | Type resolution differs |
GETDATE() / SYSUTCDATETIME() | now() / clock_timestamp() | now() is transaction start time |
TRY…CATCH | BEGIN … EXCEPTION WHEN … END | Exception blocks create subtransactions: cost and semantics |
@@ROWCOUNT | GET DIAGNOSTICS n = ROW_COUNT | — |
| Procedure returning result sets | Function RETURNS TABLE or procedure with refcursor | Changes how the app calls it |
#temp tables | CREATE TEMP TABLE | Catalogue bloat if created per call at high rate |
WITH (NOLOCK) | Remove — PostgreSQL MVCC readers do not block | Behaviour difference: no dirty reads (lesson 9) |
| SQL Agent jobs | EventBridge Scheduler + Lambda/ECS, or pg_cron on RDS/Aurora | Ownership, alerting and logging move too |
A converted procedure, for illustration:
CREATE PROCEDURE dbo.PostFee @AccountId VARCHAR(20), @Amount MONEY
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
BEGIN TRAN;
UPDATE dbo.Accounts SET Balance = Balance - @Amount
WHERE AccountId = @AccountId AND Balance >= @Amount;
IF @@ROWCOUNT = 0 THROW 50001, 'Insufficient funds', 1;
INSERT dbo.LedgerEntries (AccountId, Amount, Type, CreatedAt)
VALUES (@AccountId, -@Amount, 'fee', SYSUTCDATETIME());
COMMIT;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK;
THROW;
END CATCH
ENDCREATE OR REPLACE PROCEDURE ledger.post_fee(p_account_id text, p_amount numeric(19,4))
LANGUAGE plpgsql
AS $$
DECLARE
v_rows int;
BEGIN
UPDATE ledger.accounts
SET balance = balance - p_amount
WHERE account_id = p_account_id AND balance >= p_amount;
GET DIAGNOSTICS v_rows = ROW_COUNT;
IF v_rows = 0 THEN
RAISE EXCEPTION 'Insufficient funds' USING ERRCODE = 'P0001';
END IF;
INSERT INTO ledger.ledger_entries (account_id, amount, type, created_at)
VALUES (p_account_id, -p_amount, 'fee', clock_timestamp());
-- No explicit COMMIT: the caller's transaction (CALL inside the app's transaction) controls it,
-- and any exception rolls the whole call back.
END;
$$;Reviewer notes a converter will not write for you: MONEY became numeric(19,4) (agree the precision with Finance), SYSUTCDATETIME() became clock_timestamp() (wall clock, not transaction start), and the error code mapping must match what the application expects (50001 vs P0001).
Step 3 — move data with DMS
Prepare the source for CDC
For an on-premises or EC2 SQL Server source, DMS reads changes using MS-Replication (tables with primary keys) or MS-CDC. The database must use the full recovery model with log backups that DMS can read ⚠ VERIFY the exact prerequisites for your SQL Server version in the DMS source documentation. For an RDS for SQL Server source, enable MS-CDC with the RDS procedures:
EXEC msdb.dbo.rds_cdc_enable_db 'HarbourlineLedger';
-- then per table:
EXEC sys.sp_cdc_enable_table @source_schema = N'dbo', @source_name = N'Transactions', @role_name = NULL;Every table should have a primary key — CDC and DMS validation both depend on it.
Create endpoints with Secrets Manager credentials
aws dms create-endpoint --endpoint-identifier hl-m06-src-sqlserver --endpoint-type source --engine-name sqlserver \
--microsoft-sql-server-settings "SecretsManagerAccessRoleArn=arn:aws:iam::111122223333:role/hl-m06-dms-secrets,SecretsManagerSecretId=hl-m06/dms/source,DatabaseName=HarbourlineLedger" \
--ssl-mode require --profile learn-sandbox --region us-east-1
aws dms create-endpoint --endpoint-identifier hl-m06-tgt-aurora --endpoint-type target --engine-name aurora-postgresql \
--postgre-sql-settings "SecretsManagerAccessRoleArn=arn:aws:iam::111122223333:role/hl-m06-dms-secrets,SecretsManagerSecretId=hl-m06/dms/target,DatabaseName=harbourline" \
--ssl-mode verify-full --profile learn-sandbox --region us-east-1Define table mappings
{
"rules": [
{
"rule-type": "selection", "rule-id": "1", "rule-name": "ledger-tables",
"object-locator": { "schema-name": "dbo", "table-name": "%" },
"rule-action": "include"
},
{
"rule-type": "selection", "rule-id": "2", "rule-name": "exclude-staging",
"object-locator": { "schema-name": "dbo", "table-name": "tmp_%" },
"rule-action": "exclude"
},
{
"rule-type": "transformation", "rule-id": "3", "rule-name": "dbo-to-ledger",
"rule-target": "schema", "object-locator": { "schema-name": "dbo" },
"rule-action": "rename", "value": "ledger"
},
{
"rule-type": "transformation", "rule-id": "4", "rule-name": "lowercase-tables",
"rule-target": "table", "object-locator": { "schema-name": "dbo", "table-name": "%" },
"rule-action": "convert-lowercase"
},
{
"rule-type": "transformation", "rule-id": "5", "rule-name": "lowercase-columns",
"rule-target": "column", "object-locator": { "schema-name": "dbo", "table-name": "%", "column-name": "%" },
"rule-action": "convert-lowercase"
}
]
}PostgreSQL folds unquoted identifiers to lower case; without rules 4–5 you get quoted "Transactions" tables that nobody wants to type. In practice you pre-create the target schema from DMS Schema Conversion output and let DMS load into it (TargetTablePrepMode = DO_NOTHING or TRUNCATE_BEFORE_LOAD).
Task settings: CDC, LOBs, validation
{
"TargetMetadata": {
"TargetSchema": "ledger",
"SupportLobs": true,
"FullLobMode": false,
"LimitedSizeLobMode": true,
"LobMaxSize": 64
},
"FullLoadSettings": {
"TargetTablePrepMode": "DO_NOTHING",
"MaxFullLoadSubTasks": 8,
"CommitRate": 10000
},
"ValidationSettings": {
"EnableValidation": true,
"ValidationMode": "ROW_LEVEL",
"ThreadCount": 5,
"FailureMaxCount": 10000
},
"Logging": { "EnableLogging": true }
}LimitedSizeLobModewithLobMaxSize(KB) is far faster than full LOB mode; find the largest LOB first (MAX(DATALENGTH(col))) — anything bigger is truncated, which the premigration assessment and validation should flag.EnableValidationmakes DMS compare source and target rows after full load and during CDC, and report mismatches in the table statistics and a validation-failures table on the target.
Create and start the task
aws dms create-replication-task --replication-task-identifier hl-m06-ledger-full-cdc \
--source-endpoint-arn "$SRC_ARN" --target-endpoint-arn "$TGT_ARN" \
--replication-instance-arn "$RI_ARN" --migration-type full-load-and-cdc \
--table-mappings file://table-mappings.json --replication-task-settings file://task-settings.json \
--tags Key=Project,Value=cea-lab Key=Lab,Value=m06-migration \
--profile learn-sandbox --region us-east-1
aws dms start-replication-task --replication-task-arn "$TASK_ARN" \
--start-replication-task-type start-replication --profile learn-sandbox --region us-east-1With DMS Serverless, you create a replication config instead of an instance and task; capacity (DMS capacity units) scales between a minimum and maximum you set.
Watch CDC latency and validation
aws dms describe-table-statistics --replication-task-arn "$TASK_ARN" \
--query "TableStatistics[].[TableName,FullLoadRows,Inserts,Updates,Deletes,ValidationState,ValidationFailedRecords]" \
--output table --profile learn-sandbox --region us-east-1In CloudWatch, CDCLatencySource and CDCLatencyTarget (seconds) tell you how far behind the target is. Cut-over waits until both are near zero and validation is clean.
Step 4 — validate independently
DMS validation is valuable, but a bank's change board will want independent evidence produced by code the migration tool did not write. The tool below compares SQL Server and PostgreSQL for each table on three levels:
- Row counts.
- Control totals per business day: count and
SUM(amount)of transactions perbooking_date— the numbers Finance recognises. - Row hashes in key-ordered chunks: a SHA-256 over a canonical text form of every row, compared chunk by chunk, so you can pinpoint differences.
AWS Console instructions
- Schema conversion: Console → AWS DMS → Migrate → Schema conversion → Instance profiles (create one with a VPC, subnet group and S3 bucket for reports), Data providers (source SQL Server, target Aurora PostgreSQL; or an offline source from S3 DDL), then Migration projects → Create. Open the project, select the source schema, Assess to download the assessment report, then Convert and Export as SQL (or apply to target).
- Generative AI conversion: in the migration project settings, it is an opt-in option ⚠ VERIFY the label. Record the data-residency approval before switching it on.
- Data migration: AWS DMS → Replication instances → Create (smallest class, not publicly accessible, Multi-AZ off for a rehearsal) or Serverless replications → Create; Endpoints → Create using Secrets Manager; Test connection; Database migration tasks → Create with full load + CDC, the JSON above, Premigration assessment enabled.
- Monitoring: task → Table statistics (validation state per table), CloudWatch metrics (CDC latency), Logs.
AWS CLI
# Replication instance (rehearsal size), private, encrypted with your KMS key.
aws dms create-replication-instance --replication-instance-identifier hl-m06-dms \
--replication-instance-class dms.t3.medium --allocated-storage 50 --no-publicly-accessible \
--replication-subnet-group-identifier hl-m06-dms-subnets --vpc-security-group-ids sg-0dms00000000000001 \
--kms-key-id alias/hl-m06-dms --tags Key=Project,Value=cea-lab Key=Lab,Value=m06-migration \
--profile learn-sandbox --region us-east-1
# Test both endpoints from the replication instance.
aws dms test-connection --replication-instance-arn "$RI_ARN" --endpoint-arn "$SRC_ARN" --profile learn-sandbox --region us-east-1
aws dms test-connection --replication-instance-arn "$RI_ARN" --endpoint-arn "$TGT_ARN" --profile learn-sandbox --region us-east-1
# Run a premigration assessment before starting the task.
aws dms start-replication-task-assessment-run --replication-task-arn "$TASK_ARN" \
--service-access-role-arn arn:aws:iam::111122223333:role/hl-m06-dms-assessment \
--result-location-bucket hl-m06-dms-reports-111122223333 --assessment-run-name rehearsal-1 \
--profile learn-sandbox --region us-east-1
# Stop CDC after cut-over.
aws dms stop-replication-task --replication-task-arn "$TASK_ARN" --profile learn-sandbox --region us-east-1Infrastructure as Code
using Amazon.CDK;
using Amazon.CDK.AWS.DMS;
using Amazon.CDK.AWS.EC2;
using Constructs;
namespace Harbourline.Infra;
public sealed class DmsStack : Stack
{
public DmsStack(Construct scope, string id, IVpc vpc, ISecurityGroup dmsSg, string kmsKeyArn,
string secretsRoleArn, IStackProps? props = null) : base(scope, id, props)
{
var subnets = new CfnReplicationSubnetGroup(this, "Subnets", new CfnReplicationSubnetGroupProps
{
ReplicationSubnetGroupIdentifier = "hl-m06-dms-subnets",
ReplicationSubnetGroupDescription = "Private subnets for DMS",
SubnetIds = vpc.SelectSubnets(new SubnetSelection { SubnetType = SubnetType.PRIVATE_WITH_EGRESS }).SubnetIds
});
var instance = new CfnReplicationInstance(this, "Instance", new CfnReplicationInstanceProps
{
ReplicationInstanceIdentifier = "hl-m06-dms",
ReplicationInstanceClass = "dms.t3.medium",
AllocatedStorage = 50,
PubliclyAccessible = false,
MultiAz = false, // rehearsal; true for the production run
KmsKeyId = kmsKeyArn,
ReplicationSubnetGroupIdentifier = subnets.ReplicationSubnetGroupIdentifier,
VpcSecurityGroupIds = new[] { dmsSg.SecurityGroupId }
});
instance.AddResourceDependency(subnets);
_ = new CfnEndpoint(this, "Source", new CfnEndpointProps
{
EndpointIdentifier = "hl-m06-src-sqlserver",
EndpointType = "source",
EngineName = "sqlserver",
DatabaseName = "HarbourlineLedger",
SslMode = "require",
MicrosoftSqlServerSettings = new CfnEndpoint.MicrosoftSqlServerSettingsProperty
{
SecretsManagerAccessRoleArn = secretsRoleArn,
SecretsManagerSecretId = "hl-m06/dms/source"
}
});
Tags.SetTag("Project", "cea-lab");
Tags.SetTag("Lab", "m06-migration");
Tags.SetTag("Environment", "learn");
}
}Complete code
The validator runs locally against a SQL Server 2022 container (source) and a PostgreSQL 17 container (target), so you can rehearse without AWS.
docker run --name hl-m06-mssql -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=LocalOnly_ChangeMe1!" -e "MSSQL_PID=Developer" -p 1433:1433 -d mcr.microsoft.com/mssql/server:2022-latest
docker run --name hl-m06-pg -e POSTGRES_PASSWORD=LocalOnly_ChangeMe1 -p 5432:5432 -d postgres:17Create a Transactions table in SQL Server (dbo.Transactions: TransactionId uniqueidentifier PK, AccountId varchar(20), BookingDate date, Amount decimal(18,2), Currency char(3), Status varchar(10)) and the equivalent ledger.transactions in PostgreSQL, load the same fictional rows into both (for example with the Module 7 generator's CSV and BULK INSERT / COPY), then change one amount in PostgreSQL to see the validator catch it.
<Project Sdk="Microsoft.NET.Sdk">
<PropertyGroup>
<OutputType>Exe</OutputType>
<TargetFramework>net10.0</TargetFramework>
<Nullable>enable</Nullable>
<ImplicitUsings>enable</ImplicitUsings>
<TreatWarningsAsErrors>true</TreatWarningsAsErrors>
</PropertyGroup>
<ItemGroup>
<PackageReference Include="Microsoft.Data.SqlClient" Version="7.1.2" />
<PackageReference Include="Npgsql" Version="10.0.3" />
</ItemGroup>
</Project>using System.Data.Common;
using System.Globalization;
using System.Security.Cryptography;
using System.Text;
using Microsoft.Data.SqlClient;
using Npgsql;
// Credentials come from the environment (in AWS: Secrets Manager). Never hard-code them.
var sourceCs = Environment.GetEnvironmentVariable("SOURCE_SQLSERVER")
?? "Server=localhost,1433;Database=HarbourlineLedger;User Id=sa;Password=LocalOnly_ChangeMe1!;TrustServerCertificate=True";
var targetCs = Environment.GetEnvironmentVariable("TARGET_POSTGRES")
?? "Host=localhost;Database=postgres;Username=postgres;Password=LocalOnly_ChangeMe1";
await using var src = new SqlConnection(sourceCs);
await using var tgt = new NpgsqlConnection(targetCs);
await src.OpenAsync();
await tgt.OpenAsync();
var failures = 0;
// 1. Row counts
var srcCount = await ScalarAsync<int>(src, "SELECT COUNT(*) FROM dbo.Transactions");
var tgtCount = (int)await ScalarAsync<long>(tgt, "SELECT count(*) FROM ledger.transactions");
Report("row count", srcCount.ToString(CultureInfo.InvariantCulture), tgtCount.ToString(CultureInfo.InvariantCulture));
// 2. Control totals per booking date (count and sum) — the numbers Finance signs off.
var srcTotals = await DailyTotalsAsync(src,
"SELECT BookingDate, COUNT(*), SUM(Amount) FROM dbo.Transactions GROUP BY BookingDate");
var tgtTotals = await DailyTotalsAsync(tgt,
"SELECT booking_date, count(*), sum(amount) FROM ledger.transactions GROUP BY booking_date");
foreach (var day in srcTotals.Keys.Union(tgtTotals.Keys).Order())
{
srcTotals.TryGetValue(day, out var s);
tgtTotals.TryGetValue(day, out var t);
if (s != t) Report($"totals {day:yyyy-MM-dd}", $"{s.Count}/{s.Sum:0.00}", $"{t.Count}/{t.Sum:0.00}");
}
// 3. Row hashes in chunks of 10,000 rows ordered by primary key.
// Both sides are read in the same key order; each row is rendered canonically before hashing.
const int chunkSize = 10_000;
await using (var sr = await ReaderAsync(src,
"SELECT TransactionId, AccountId, BookingDate, Amount, Currency, Status FROM dbo.Transactions ORDER BY CAST(TransactionId AS char(36)) COLLATE Latin1_General_BIN2"))
await using (var tr = await ReaderAsync(tgt,
"SELECT transaction_id, account_id, booking_date, amount, currency, status FROM ledger.transactions ORDER BY transaction_id::text COLLATE \"C\""))
{
var chunk = 0;
while (true)
{
var (srcHash, srcFirst, srcRows) = await HashChunkAsync(sr, chunkSize);
var (tgtHash, tgtFirst, tgtRows) = await HashChunkAsync(tr, chunkSize);
if (srcRows == 0 && tgtRows == 0) break;
if (srcHash != tgtHash)
Report($"hash chunk {chunk} (first key {srcFirst ?? tgtFirst})", srcHash[..12], tgtHash[..12]);
chunk++;
}
Console.WriteLine($"Compared {chunk} chunk(s) of up to {chunkSize} rows.");
}
Console.WriteLine(failures == 0 ? "VALIDATION PASSED" : $"VALIDATION FAILED: {failures} difference(s)");
return failures == 0 ? 0 : 1;
// ---------- helpers ----------
void Report(string check, string source, string target)
{
var ok = source == target;
if (!ok) failures++;
Console.WriteLine($"{(ok ? "OK " : "DIFF")} {check,-40} source={source} target={target}");
}
static async Task<T> ScalarAsync<T>(DbConnection conn, string sql)
{
await using var cmd = conn.CreateCommand();
cmd.CommandText = sql;
return (T)(await cmd.ExecuteScalarAsync() ?? throw new InvalidOperationException("No result"));
}
static async Task<DbDataReader> ReaderAsync(DbConnection conn, string sql)
{
var cmd = conn.CreateCommand();
cmd.CommandText = sql;
cmd.CommandTimeout = 0; // large tables; run against a replica or snapshot, not the busy primary
return await cmd.ExecuteReaderAsync(System.Data.CommandBehavior.SequentialAccess);
}
static async Task<Dictionary<DateOnly, (long Count, decimal Sum)>> DailyTotalsAsync(DbConnection conn, string sql)
{
var result = new Dictionary<DateOnly, (long, decimal)>();
await using var r = await ReaderAsync(conn, sql);
while (await r.ReadAsync())
{
var day = r.GetValue(0) switch
{
DateOnly d => d,
DateTime dt => DateOnly.FromDateTime(dt),
var other => throw new InvalidCastException($"Unexpected date type {other.GetType()}")
};
result[day] = (Convert.ToInt64(r.GetValue(1), CultureInfo.InvariantCulture), r.GetDecimal(2));
}
return result;
}
// Canonical row text: lower-case GUID, ISO date, amount with exactly two decimals, trimmed strings.
static async Task<(string Hash, string? FirstKey, int Rows)> HashChunkAsync(DbDataReader r, int size)
{
using var sha = IncrementalHash.CreateHash(HashAlgorithmName.SHA256);
string? first = null;
var rows = 0;
while (rows < size && await r.ReadAsync())
{
var key = r.GetGuid(0).ToString("D");
first ??= key;
var date = r.GetValue(2) is DateOnly d ? d : DateOnly.FromDateTime(r.GetDateTime(2));
var line = string.Join('|',
key,
r.GetString(1).Trim(),
date.ToString("yyyy-MM-dd", CultureInfo.InvariantCulture),
r.GetDecimal(3).ToString("0.00", CultureInfo.InvariantCulture),
r.GetString(4).Trim(),
r.GetString(5).Trim()) + "\n";
sha.AppendData(Encoding.UTF8.GetBytes(line));
rows++;
}
return (Convert.ToHexString(sha.GetHashAndReset()), first, rows);
}Example output after deliberately changing one amount in PostgreSQL:
OK row count source=60000 target=60000
DIFF totals 2026-09-14 source=2000/-199842.17 target=2000/-199832.17
DIFF hash chunk 3 (first key 4c1f…) source=9A1C0D7E33B2 target=5E0B7741AC09
Compared 6 chunk(s) of up to 10000 rows.
VALIDATION FAILED: 2 difference(s)Monitoring the DMS task from C#
using Amazon.DatabaseMigrationService;
using Amazon.DatabaseMigrationService.Model;
namespace Harbourline.Migration;
public static class DmsTaskMonitor
{
// Prints per-table progress and validation state; returns true when every table is validated.
public static async Task<bool> ReportAsync(IAmazonDatabaseMigrationService dms, string taskArn, CancellationToken ct)
{
var allValidated = true;
string? marker = null;
do
{
var page = await dms.DescribeTableStatisticsAsync(new DescribeTableStatisticsRequest
{
ReplicationTaskArn = taskArn, Marker = marker, MaxRecords = 100
}, ct);
foreach (var t in page.TableStatistics ?? [])
{
Console.WriteLine($"{t.SchemaName}.{t.TableName,-30} load={t.FullLoadRows,10} ins={t.Inserts,8} upd={t.Updates,8} del={t.Deletes,8} validation={t.ValidationState} failed={t.ValidationFailedRecords}");
if (t.ValidationState != "Validated") allValidated = false;
}
marker = page.Marker;
} while (!string.IsNullOrEmpty(marker));
return allValidated;
}
}Code explained line by line
- Connection strings from environment variables with local defaults — in AWS, read them from Secrets Manager; the validation role needs read-only access on both sides.
- Row counts first — cheap and catches gross failures (a table not loaded).
- Daily control totals with
GROUP BY booking_date— the business-level check; a difference here is something Finance will understand and must sign off. srcTotals.Keys.Union(tgtTotals.Keys)— catches days that exist on one side only.- Ordering by the text form of the GUID with a binary collation on both sides (
Latin1_General_BIN2,"C") — the engines sortuniqueidentifier/uuiddifferently natively, and linguistic collations can treat hyphens specially. Byte order on the 36-character text is identical on both sides (SQL Server returns upper-case hex, PostgreSQL lower-case, but digits sort before letters either way and the canonical row text lower-cases the key). CommandBehavior.SequentialAccessand streaming readers — the validator never holds a whole table in memory.HashChunkAsync— builds a canonical text line per row (lower-case GUID, ISO date, two-decimal amount, trimmed strings) so engine-specific formatting does not cause false differences, then hashes 10,000 rows at a time.- Reporting the first key of a mismatching chunk — narrows a difference to 10,000 rows, which you can then diff row by row.
- Exit code 1 on failure — run it in the cut-over pipeline as a gate.
DescribeTableStatisticsAsyncwithMarkerpaging — reads DMS's own per-table counters and validation state, which complement the independent check.
Step 5 — cut-over and rollback
A runbook Harbourline's change board approved (abridged):
| T | Action | Owner | Go/no-go evidence |
|---|---|---|---|
| T−14 d | Full rehearsal on a production-sized copy: full load + CDC + validation + app smoke tests | Migration lead | Timings, validation report, performance baseline |
| T−1 d | Freeze schema changes on the source | Change manager | Freeze notice |
| T−2 h | Confirm CDC latency under 5 s and DMS validation "Validated" for all tables | DBA | Screenshot/CLI output |
| T−0 | Stop application writes (maintenance page; queue inbound payments) | Ops | Zero active write sessions on source |
| T+5 min | Wait for CDC latency = 0; stop the DMS task | DBA | Task stopped, last LSN recorded |
| T+10 min | Run the independent validator; Finance checks daily totals | DBA + Finance | VALIDATION PASSED; signed totals |
| T+20 min | Reset identity/sequence values on target (setval) | DBA | Next IDs above source max |
| T+25 min | Switch application connection strings (Secrets Manager / parameter) and restart | App team | Health checks green |
| T+40 min | Smoke tests: post a test transfer between internal test accounts; read balances | QA | Results logged |
| T+60 min | Go/no-go: reopen traffic | Incident commander | Decision recorded |
| Rollback | If no-go before reopening: point apps back to source (unchanged since T−0). If after: reverse replication target → source (a pre-built DMS task), or forward-fix | Incident commander | Pre-approved criteria |
The rollback decision point matters most: once new transactions are posted on the target, going back requires replicating them to the source. Build and test that reverse task in rehearsal, or define a clear forward-fix-only policy and get it approved.
Security considerations
⚠ Learning shortcut — not for production
The local rehearsal uses sa and postgres superusers with passwords in environment variables and TrustServerCertificate=True. In a real migration: dedicated least-privilege DMS users (read and CDC permissions on the source; DDL/DML on the target schema only), credentials in Secrets Manager with DMS's secret integration, TLS with certificate verification on both endpoints, a non-public replication instance in private subnets, KMS-encrypted storage, and every task start/stop recorded through CloudTrail and the change ticket.
- Data in transit across networks: source-to-AWS over VPN or Direct Connect, plus TLS on the database connection.
- Copies multiply: DMS replication instance storage, S3 assessment buckets, rehearsal databases, validator logs. Inventory them and delete them on schedule; rehearsal copies of production personal data need the same controls as production (or use masked data for rehearsals that do not need real values).
- Generative-AI conversion: sends source code to a model with cross-Region inference. Procedure code can contain business logic and, occasionally, hard-coded data. Review and approve before use; never paste customer data into prompts.
- Separation of duties: the people who run the migration should not be the only ones who validate it. Finance signs control totals; a second DBA reviews the validator output.
Banking use case
Harbourline's reporting database (180 GB, 60 procedures) migration to Aurora PostgreSQL:
- Assess: DMS Schema Conversion offline mode on DDL exported with SSMS (the source team would not open firewall rules to the migration VPC during assessment). Report: 92% of storage objects and 71% of code objects converted automatically; 18 procedures need manual work (dynamic SQL,
MERGEwithOUTPUT, case-insensitive lookups). - Convert: automatic conversion + manual rewrites; unit tests for each procedure comparing outputs on both engines with the same fictional fixtures.
- Load: DMS full load + CDC for two weeks while reports were tested in parallel on both databases.
- Validate: DMS validation plus the independent validator nightly; Finance compared month-end report outputs from both systems — identical to the penny after one rounding fix (
MONEYarithmetic vsnumeric). - Cut-over: 25 minutes of read-only window on a Sunday. Licence savings funded the ledger's later replatforming.
Common mistakes
- Treating DMS as a schema tool → it creates basic tables without the right indexes, defaults, constraints or procedures → pre-create the target schema from DMS Schema Conversion and load into it.
- Ignoring LOB settings → full LOB mode is slow; limited mode silently truncates if
LobMaxSizeis too small → measure the largest LOB, set limits, check validation. - Tables without primary keys → CDC updates/deletes and validation fail → add keys before migration.
- Not resetting sequences → first insert after cut-over fails with duplicate key →
setvaltomax(id)per table in the runbook. - Case sensitivity surprises →
WHERE email = 'A@example.com'stops matching →citextor ICU non-deterministic collations, and tests. - Only DMS validation → the tool checks itself → add independent control totals that the business recognises.
- No rehearsal at production scale → full load takes 3× longer than estimated → rehearse with production-sized (masked) data and record timings.
Troubleshooting
| Symptom | Likely cause | Fix |
|---|---|---|
| Endpoint test fails: timeout | Network path or security group | Check VPN/DX routes, SGs and source firewall for the replication instance's private IP |
| Endpoint test fails: login | Secret contents or permissions | Check the secret JSON keys DMS expects and the IAM role's secretsmanager:GetSecretValue |
| CDC task errors about MS-CDC / MS-Replication | Source not prepared or recovery model not full | Enable CDC per the DMS SQL Server source prerequisites; full recovery with log backups |
CDCLatencyTarget keeps rising | Target too slow (indexes, triggers, small instance) or large transactions | Scale target or DMS; disable target triggers during load; batch apply settings |
Validation Mismatched records | Data type conversion (e.g. DATETIME rounding, trailing spaces in CHAR) or real loss | Inspect the awsdms_validation_failures_v1 table on the target; fix mapping or data |
| LOB columns truncated | LobMaxSize too small | Increase it, or use inline/full LOB mode for those tables |
| Duplicate key on first insert after cut-over | Sequences/identity not advanced | SELECT setval(…, (SELECT max(id) FROM …)) |
| App errors on stored procedure calls | Procedures converted to functions/procedures with different calling conventions | Update data-access code; test every procedure |
Cost and cleanup
The local rehearsal is free (SQL Server Developer edition and PostgreSQL containers). In AWS:
- DMS Schema Conversion: no additional charge for the feature; you pay for the S3 bucket and any network path.
- DMS replication instance: billed per hour by instance class plus storage; a
dms.t3.mediumfor a short rehearsal costs well under USD 1 per hour ⚠ VERIFY current price; DMS Serverless bills per DMS capacity unit-hour. Data transfer into AWS is free; out of AWS is charged. - Target databases: Aurora/RDS instance-hours and storage — the main cost of a rehearsal.
docker rm -f hl-m06-mssql hl-m06-pgaws dms stop-replication-task --replication-task-arn "$TASK_ARN" --profile learn-sandbox --region us-east-1
aws dms delete-replication-task --replication-task-arn "$TASK_ARN" --profile learn-sandbox --region us-east-1
aws dms delete-endpoint --endpoint-arn "$SRC_ARN" --profile learn-sandbox --region us-east-1
aws dms delete-endpoint --endpoint-arn "$TGT_ARN" --profile learn-sandbox --region us-east-1
aws dms delete-replication-instance --replication-instance-arn "$RI_ARN" --profile learn-sandbox --region us-east-1
aws dms delete-replication-subnet-group --replication-subnet-group-identifier hl-m06-dms-subnets --profile learn-sandbox --region us-east-1
aws resourcegroupstaggingapi get-resources --tag-filters Key=Project,Values=cea-lab \
--query "ResourceTagMappingList[].ResourceARN" --profile learn-sandbox --region us-east-1Delete schema conversion migration projects, instance profiles and report buckets you no longer need, and any rehearsal snapshots.
Exercises
- 🟢 Guided: Run the validator against identical data (expect
VALIDATION PASSED), then change one amount, delete one row and add a trailing space to oneStatuson the target. Explain which check catches each change and why the trailing space does not show up. - 🟡 Challenge: Convert this T-SQL to PL/pgSQL and write a test that runs both against the same fixtures: a procedure that
MERGEs daily exchange rates from a staging table intoexchange_rates, usingOUTPUT $actionto count inserts and updates. - 🔴 Live problem: Two days before cut-over, DMS validation reports 312 mismatched records in
Transactions, all withCreatedAtvalues. The independent validator (which does not hashCreatedAt) passes. The business owner asks whether to proceed. What do you do?
Assignment
Produce a migration plan pack for a fictional 100 GB SQL Server database: path decision with the table from this lesson, assessment summary (DMS Schema Conversion or Babelfish Compass — Lab 6.5 is a good input), DMS task JSON, the validator adapted to two more tables, and the cut-over/rollback runbook with go/no-go criteria. Keep it in your portfolio repository.
Solutions
- Changed amount: daily totals differ for that day, and the hash of the chunk containing the row differs.
- Deleted row: row count differs, the day's count differs, and every chunk from that row onward hashes differently (rows shift). Comparing the first keys of chunks shows where the shift begins.
- Trailing space in
Status: not detected, because the canonical form trims strings. That is a deliberate choice —CHAR(n)padding differs between engines and would otherwise produce false alarms — but it means the validator cannot see whitespace changes. Document such normalisations in the validation report so reviewers know what "equal" means.
CREATE OR REPLACE FUNCTION ledger.merge_exchange_rates(p_date date)
RETURNS TABLE (inserted int, updated int)
LANGUAGE plpgsql AS $$
BEGIN
RETURN QUERY
WITH upserted AS (
INSERT INTO ledger.exchange_rates (date, base_currency, quote_currency, rate)
SELECT s.date, s.base_currency, s.quote_currency, s.rate
FROM staging.exchange_rates s
WHERE s.date = p_date
ON CONFLICT (date, base_currency, quote_currency)
DO UPDATE SET rate = EXCLUDED.rate
RETURNING (xmax = 0) AS was_insert
)
SELECT count(*) FILTER (WHERE was_insert)::int, count(*) FILTER (WHERE NOT was_insert)::int
FROM upserted;
END;
$$;Why: INSERT … ON CONFLICT DO UPDATE is PostgreSQL's idiomatic upsert; xmax = 0 is a well-known way to tell inserted from updated rows in RETURNING. PostgreSQL 15+ also supports MERGE, and PostgreSQL 17 added RETURNING with merge_action() for MERGE — closer to T-SQL's OUTPUT $action ⚠ VERIFY against your target version. The test should assert both the counts and the resulting rates for inserts, updates and unchanged rows, using decimal rate values with six decimals.
Do not proceed until the difference is explained.
- Inspect the DMS validation failures table on the target: compare the source and target
CreatedAtvalues for a sample. - Likely cause:
DATETIMEon SQL Server is stored with roughly 3 ms precision (values end in .000, .003, .007), while the targettimestampstores microseconds; or a time-zone conversion (DATETIMElocal time loaded intotimestamptz). Both are conversion issues, not data loss — but the second one is a real defect that would shift transactions across midnight and changebooking_datereports. - Fix the mapping (correct type and time-zone handling), reload the affected table, and add
CreatedAtto the independent validator's canonical form with an agreed precision. - Report to the business owner: what was found, why the independent validator missed it (column not covered), what changed, and the new evidence. Proceed only with both validations clean or with a documented, approved exception.
Knowledge check
Answer: It migrates data (full load and ongoing changes) and can create basic tables. It does not convert stored procedures, functions, triggers, views, users, jobs or most secondary indexes and constraints — use DMS Schema Conversion and your own scripts for those.
Answer: The source keeps running during the full load and testing. CDC applies changes made in the meantime, so cut-over needs only a short write freeze while the last changes are applied, instead of downtime for the whole copy.
Answer: A capability that lets Aurora PostgreSQL accept SQL Server's TDS protocol and much of T-SQL, so applications can connect with fewer changes. The risk is incomplete or different behaviour for some features — assess with Babelfish Compass and test thoroughly.
Answer: Independent evidence, produced by separate code and including business control totals (counts and sums per day), satisfies separation of duties and catches mapping decisions the tool considers "equal".
Answer: Advance each sequence past the maximum loaded value (setval), or the first new insert fails with a duplicate key.
Interview questions
- "How would you migrate a 2 TB SQL Server ledger to AWS with minimal downtime?" — Decide path (often RDS for SQL Server first), pre-create schema, DMS full load + CDC over private connectivity, rehearse at scale, validate with DMS and independent control totals, short write freeze, drain CDC, validate, switch, smoke test, with a tested rollback.
- "RDS for SQL Server, Babelfish or PostgreSQL?" — Trade licence cost against code change and risk: RDS SQL Server for speed and compatibility, Babelfish when Compass shows good coverage, full conversion for the strategic long-term platform.
- "What goes wrong in SQL Server → PostgreSQL conversions?" — Case-insensitive collations,
DATETIMEprecision and time zones,MONEYarithmetic, identity reseeding, procedures returning result sets,NOLOCKassumptions, isolation behaviour, and plan differences. - "What would you put in a cut-over runbook?" — Timed steps with owners, go/no-go evidence for each, freeze and drain procedure, validation gates, connection switch, smoke tests, rollback criteria and method, communication plan.
Certification practice
A company must migrate a 1 TB on-premises SQL Server database to Amazon RDS for SQL Server with less than 15 minutes of downtime. The database is updated continuously. What should a solutions architect recommend?
A. Take a native backup, upload it to S3, restore it into RDS, then switch the application.
B. Use AWS DMS with a full load and ongoing replication (CDC), then switch the application after replication catches up.
C. Export tables to CSV, load them with BULK INSERT, then switch.
D. Use AWS DataSync to copy the database files to RDS.
Answer: B. Full load plus CDC keeps the target current, so downtime is only the final drain and switch. A backup/restore alone misses changes made after the backup unless combined with change replication (A). CSV export requires long downtime (C). DataSync copies files, and RDS does not accept raw data files (D).
A data engineer is migrating a SQL Server database with hundreds of stored procedures to Amazon Aurora PostgreSQL. The team wants an assessment of conversion complexity and automatic conversion of as much code as possible, without installing desktop software. What should they use?
A. AWS DMS full-load task with table mappings. B. DMS Schema Conversion in the AWS DMS console. C. AWS Glue crawlers on the source database. D. Amazon Athena federated queries.
Answer: B. DMS Schema Conversion assesses and converts schemas and code as a managed feature. DMS tasks move data, not procedures (A). Glue crawlers infer table metadata, not code (C). Athena federation queries data in place (D).
A company wants to reduce SQL Server licensing costs by moving to a PostgreSQL-compatible database, but the application uses T-SQL and the TDS protocol, and the team wants to minimise application code changes in the first phase. What should the company use?
A. Amazon RDS for SQL Server Standard edition. B. Babelfish for Aurora PostgreSQL, after assessing compatibility. C. Amazon DynamoDB with PartiQL. D. Amazon Redshift.
Answer: B. Babelfish lets Aurora PostgreSQL understand TDS and much of T-SQL, reducing code changes while removing SQL Server licences. RDS for SQL Server keeps licence costs (A). DynamoDB and Redshift are different data models and engines (C, D).
Completion checklist
- I can choose between RDS for SQL Server, Babelfish and conversion for a given database and explain why
- I can describe DMS Schema Conversion's assessment, conversion, generative-AI and offline options and their limits
- I can write DMS table mappings and task settings for full load + CDC with validation
- I ran the independent validator and explained each kind of difference it catches
- I can present a cut-over and rollback runbook
- I removed local containers and any DMS resources
Key takeaways
- A migration is three problems — schema/code, data, application/operations — and each has its own tools and tests.
- Choose the path by deadline, licence cost and code risk: RDS for SQL Server, Babelfish, or full PostgreSQL conversion; it can be staged.
- DMS Schema Conversion assesses and converts (with optional, residency-sensitive generative AI); DMS moves data with full load + CDC and validates.
- Prove correctness independently with counts, business control totals and row hashes; Finance signs the totals.
- Rehearse at scale and agree rollback before cut-over — after go-live, rollback means reverse replication or forward-fix.
Further reading
Independent learning resource — not affiliated with, endorsed or sponsored by Amazon Web Services. AWS and related marks are trademarks of Amazon.com, Inc. or its affiliates. All banking data in examples is fictional. Sample architectures are for learning and are not automatically compliant with GDPR, PCI DSS or any banking regulation.
Performance optimisation
Diagnose and fix slow databases on RDS and Aurora: read DB load and wait events in CloudWatch Database Insights (the successor to Performance Insights), find top SQL with pg_stat_statements and Query Store, read execution plans, design indexes, and tame parameter sniffing on SQL Server and PostgreSQL.
Lab 6: Harbourline banking data store (RDS PostgreSQL, EF Core, IAM auth, restore drill)
Build and run the module project: an idempotent, concurrency-safe ledger API on EF Core and PostgreSQL, first locally, then on an encrypted private RDS instance with IAM authentication, ending in a measured, evidenced point-in-time restore drill and a written decision record.