Skip to Content
Cloud Engineering Academy
6. Databases & Data Architecture

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.

advanced⏱ 3 h 🟢 FreeSAA-C03DEA-C01

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

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:

  1. Schema and code: tables, indexes, constraints, views, stored procedures, functions, triggers, jobs. If the engine changes (SQL Server → PostgreSQL), these must be converted.
  2. Data: copy what exists (full load), then keep copying changes made while you test (CDC — change data capture), until the moment you switch.
  3. 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

ToolSolvesHonest 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 assessmentsMigrates data and basic table structure, not procedures, users, jobs or most secondary objects; LOB handling needs tuning; CDC needs source prerequisites
DMS Schema ConversionAssessment report and automatic conversion of schema and code (SQL Server → Aurora/RDS PostgreSQL, among others) inside the DMS console; no additional charge for the featureComplex 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 sourceYou must export complete, current DDL; no live metadata
AWS Schema Conversion Tool (SCT)The older desktop toolStill documented; AWS's investment is in DMS Schema Conversion ⚠ VERIFY current SCT status before relying on it
Babelfish for Aurora PostgreSQLAurora PostgreSQL accepts TDS connections (port 1433) and much of T-SQL, so many apps connect with minimal changesNot 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 AdvisorDatabase inventoryEnd 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 equivalentWhat's different
Data Migration Assistant (DMA) assessmentDMS Schema Conversion assessment report / premigration assessments; Babelfish CompassCloud-hosted; also targets PostgreSQL, not only SQL Server
SQL Server Migration Assistant (SSMA)DMS Schema ConversionManaged in the console; generative-AI assisted conversion
Azure Database Migration Service (online migration)AWS DMS full load + CDCSimilar concept; DMS supports many source/target pairs
Log shipping / backup-restore to Azure SQL MINative backup to S3 and restore into RDS for SQL ServerBest for like-for-like SQL Server moves; minimal downtime needs CDC or log shipping alternatives
Transactional replication / CDCDMS CDC reading MS-CDC or MS-Replication from the sourceDMS reads the transaction log via these features
Azure SQL MI (near 100% compatibility)RDS for SQL ServerLicence-included or bring-your-own options vary by edition ⚠ VERIFY; some features (e.g. certain server-level permissions) are restricted
Azure Database for PostgreSQLRDS for PostgreSQL / Aurora PostgreSQL / BabelfishBabelfish has no Azure equivalent

Choosing the path

PathLicence costCode changeRiskTypical Harbourline use
Rehost (SQL Server on EC2)Highest; you manage everythingNoneLow technical, high operationalOnly for vendor products that require OS access
Replatform (RDS for SQL Server)Licence-included (or bring-your-own where eligible ⚠ VERIFY); Enterprise edition is expensiveMinimalLowFirst move under deadline; ledger stays T-SQL
BabelfishPostgreSQL pricing (no SQL Server licence)Low to medium; unsupported features need rewriteMedium: subtle behaviour differencesInternal apps with mostly standard T-SQL
Convert to Aurora/RDS PostgreSQLPostgreSQL pricingHigh: procedures, app SQL, data types, testsMedium–high, best long-termStrategic 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

  1. Inventory objects and sizes: tables, row counts, LOB columns, procedures, functions, triggers, jobs, linked servers, CLR assemblies, cross-database queries.
  2. Run the DMS Schema Conversion assessment (online, or offline from DDL scripts) — it produces a report with conversion percentages and action items by complexity.
  3. For Babelfish, run Babelfish Compass on the exported DDL to list unsupported features.
  4. 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 ServerPostgreSQLWatch out for
NVARCHAR(n) / VARCHAR(n)varchar(n) or text (UTF-8)Length semantics (characters vs bytes); text is fine and common
DATETIME / DATETIME2timestamp (or timestamptz for UTC instants)DATETIME rounds to ~3 ms; decide time zones explicitly
MONEYnumeric(19,4)Never double precision; check rounding in calculations
BITbooleanApp code that treats it as 0/1
UNIQUEIDENTIFIERuuidSort order differs
IDENTITY(1,1)GENERATED ALWAYS AS IDENTITYReseed sequences after data load (setval)
Case-insensitive collation (…_CI_AS)citext or an ICU non-deterministic collationUnique 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…CATCHBEGIN … EXCEPTION WHEN … ENDException blocks create subtransactions: cost and semantics
@@ROWCOUNTGET DIAGNOSTICS n = ROW_COUNT—
Procedure returning result setsFunction RETURNS TABLE or procedure with refcursorChanges how the app calls it
#temp tablesCREATE TEMP TABLECatalogue bloat if created per call at high rate
WITH (NOLOCK)Remove — PostgreSQL MVCC readers do not blockBehaviour difference: no dirty reads (lesson 9)
SQL Agent jobsEventBridge Scheduler + Lambda/ECS, or pg_cron on RDS/AuroraOwnership, alerting and logging move too

A converted procedure, for illustration:

T-SQL (source)
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
END
PL/pgSQL (target, reviewed)
CREATE 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-1

Define table mappings

table-mappings.json
{
  "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

task-settings.json
{
  "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 }
}
  • LimitedSizeLobMode with LobMaxSize (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.
  • EnableValidation makes 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-1

With 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-1

In 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:

  1. Row counts.
  2. Control totals per business day: count and SUM(amount) of transactions per booking_date — the numbers Finance recognises.
  3. 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-1

Infrastructure as Code

DmsStack.cs
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:17

Create 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.

Harbourline.Migration.Validator.csproj
<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>
Program.cs
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#

DmsTaskMonitor.cs
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

  1. 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.
  2. Row counts first — cheap and catches gross failures (a table not loaded).
  3. Daily control totals with GROUP BY booking_date — the business-level check; a difference here is something Finance will understand and must sign off.
  4. srcTotals.Keys.Union(tgtTotals.Keys) — catches days that exist on one side only.
  5. Ordering by the text form of the GUID with a binary collation on both sides (Latin1_General_BIN2, "C") — the engines sort uniqueidentifier/uuid differently 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).
  6. CommandBehavior.SequentialAccess and streaming readers — the validator never holds a whole table in memory.
  7. 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.
  8. Reporting the first key of a mismatching chunk — narrows a difference to 10,000 rows, which you can then diff row by row.
  9. Exit code 1 on failure — run it in the cut-over pipeline as a gate.
  10. DescribeTableStatisticsAsync with Marker paging — 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):

TActionOwnerGo/no-go evidence
T−14 dFull rehearsal on a production-sized copy: full load + CDC + validation + app smoke testsMigration leadTimings, validation report, performance baseline
T−1 dFreeze schema changes on the sourceChange managerFreeze notice
T−2 hConfirm CDC latency under 5 s and DMS validation "Validated" for all tablesDBAScreenshot/CLI output
T−0Stop application writes (maintenance page; queue inbound payments)OpsZero active write sessions on source
T+5 minWait for CDC latency = 0; stop the DMS taskDBATask stopped, last LSN recorded
T+10 minRun the independent validator; Finance checks daily totalsDBA + FinanceVALIDATION PASSED; signed totals
T+20 minReset identity/sequence values on target (setval)DBANext IDs above source max
T+25 minSwitch application connection strings (Secrets Manager / parameter) and restartApp teamHealth checks green
T+40 minSmoke tests: post a test transfer between internal test accounts; read balancesQAResults logged
T+60 minGo/no-go: reopen trafficIncident commanderDecision recorded
RollbackIf 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-fixIncident commanderPre-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:

  1. 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, MERGE with OUTPUT, case-insensitive lookups).
  2. Convert: automatic conversion + manual rewrites; unit tests for each procedure comparing outputs on both engines with the same fictional fixtures.
  3. Load: DMS full load + CDC for two weeks while reports were tested in parallel on both databases.
  4. 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 (MONEY arithmetic vs numeric).
  5. 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 LobMaxSize is 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 → setval to max(id) per table in the runbook.
  • Case sensitivity surprises → WHERE email = 'A@example.com' stops matching → citext or 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

SymptomLikely causeFix
Endpoint test fails: timeoutNetwork path or security groupCheck VPN/DX routes, SGs and source firewall for the replication instance's private IP
Endpoint test fails: loginSecret contents or permissionsCheck the secret JSON keys DMS expects and the IAM role's secretsmanager:GetSecretValue
CDC task errors about MS-CDC / MS-ReplicationSource not prepared or recovery model not fullEnable CDC per the DMS SQL Server source prerequisites; full recovery with log backups
CDCLatencyTarget keeps risingTarget too slow (indexes, triggers, small instance) or large transactionsScale target or DMS; disable target triggers during load; batch apply settings
Validation Mismatched recordsData type conversion (e.g. DATETIME rounding, trailing spaces in CHAR) or real lossInspect the awsdms_validation_failures_v1 table on the target; fix mapping or data
LOB columns truncatedLobMaxSize too smallIncrease it, or use inline/full LOB mode for those tables
Duplicate key on first insert after cut-overSequences/identity not advancedSELECT setval(…, (SELECT max(id) FROM …))
App errors on stored procedure callsProcedures converted to functions/procedures with different calling conventionsUpdate 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.medium for 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-pg
aws 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-1

Delete 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 one Status on 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 into exchange_rates, using OUTPUT $action to count inserts and updates.
  • 🔴 Live problem: Two days before cut-over, DMS validation reports 312 mismatched records in Transactions, all with CreatedAt values. The independent validator (which does not hash CreatedAt) 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

Knowledge check

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, DATETIME precision and time zones, MONEY arithmetic, identity reseeding, procedures returning result sets, NOLOCK assumptions, 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

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.

On this page