Skip to Content
Cloud Engineering Academy
13. Microsoft-to-AWS Migration

SQL Server database migration

Run the data half of a Microsoft migration: choose the target (RDS for SQL Server, Aurora PostgreSQL with Babelfish, or a full conversion), triage T-SQL compatibility, replicate with AWS DMS full load plus change data capture, adapt EF Core, and prove the data matches with an independent reconciliation.

advanced⏱ 3 h 🟡 Low costSAP-C03SAA-C03DEA-C01

Assumes: .NET 10 LTS · EF Core 10 · Npgsql.EntityFrameworkCore.PostgreSQL 10.0.3 · Microsoft.Data.SqlClient · AWS DMS · DMS Schema Conversion · Babelfish for Aurora PostgreSQL 6.x · AWS CDK v2 (C#) · Terraform AWS provider v6 · Region us-east-1. Verified against official sources on 2026-11-18.

Learning objectives

By the end of this lesson you can:

  • Choose between RDS for SQL Server, Aurora PostgreSQL with Babelfish and full conversion to PostgreSQL, with licence, effort and risk trade-offs for a bank.
  • Triage a T-SQL code base with a scanner, then confirm with the official tools (DMS Schema Conversion and the Babelfish Compass assessment).
  • Define a DMS full load plus CDC task with table mappings, validation and sensible settings, and deploy the supporting resources with CDK (C#) or Terraform.
  • Change an EF Core application from SQL Server to PostgreSQL without breaking money, time or concurrency behaviour.
  • Reconcile source and target independently, by date, with decimal arithmetic, and explain the result to a change board.

Prerequisites

Cost label: credits. A short DMS rehearsal with the smallest replication instance and a small dataset costs well under USD 5 if destroyed the same day ⚠ VERIFY current prices. Free alternative: do the local parts (scanner, EF Core change, reconciliation) with Docker containers and treat the DMS section as design-only.

Why this matters in banking

A bank's database is the system of record. A defect in the application can usually be fixed and re-run; a defect in the data migration can silently change a balance, and the discovery may come at month end. Typical failure modes: a decimal column mapped to float, a datetime truncated by a different precision, a collation that sorts customer names differently, a trigger that does not exist on the target, or CDC that missed rows during a log truncation.

Regulators and auditors will ask for three things: a rehearsed procedure, independent evidence that every table and every money total matches, and a tested way back. This lesson makes the second piece concrete.

The idea in plain language

Moving a SQL Server database has two independent decisions:

  1. Which engine? Stay on SQL Server (managed as Amazon RDS for SQL Server), keep T-SQL but change engine (Babelfish for Aurora PostgreSQL, which accepts SQL Server's network protocol and much of T-SQL), or convert fully to PostgreSQL.
  2. How do the bytes move? A full load copies the existing rows, change data capture (CDC) replays changes made during the copy and afterwards, and a cut-over points the application at the target once a final check passes.

Analogy: Moving a ledger room. Choosing the engine is choosing whether the new room has the same shelving (RDS for SQL Server), has adjustable shelving that fits most of your boxes (Babelfish), or needs everything re-boxed (conversion). The copy is a van, CDC is the courier running new papers across, and the cut-over is the day you lock the old room after a joint count.

What problem the AWS services solve

ToolWhat it doesHonest limits (checked 2026-11-18)
AWS DMSFull load and CDC between many engines, premigration assessments, built-in validation, serverless optionMoves data and basic table structure, not procedures, jobs or users. CDC needs source prerequisites (recovery model, log retention). LOB columns need tuning
DMS Schema ConversionAssesses and converts schema and code inside DMS (for example SQL Server to PostgreSQL), with generative-AI assistance and an offline mode (July 2026) that works from DDL scriptsNo extra charge for the feature itself. Complex T-SQL needs manual work. AI-assisted conversion raises a data-residency question for banks ⚠ VERIFY
AWS SCT (desktop)Older converterStatus could not be confirmed here ⚠ VERIFY before relying on it
Babelfish for Aurora PostgreSQLTDS endpoint and T-SQL support on Aurora PostgreSQL (version 6.x current)Not full SQL Server. Assess with the Babelfish Compass tool; some features and system procedures are unsupported or behave differently
RDS for SQL ServerManaged SQL Server with backups, Multi-AZ, patchingLicence-included pricing is high; some server-level features are restricted; edition and BYOL options vary ⚠ VERIFY

Microsoft / Azure comparison

You know (Microsoft/Azure)AWS equivalentWhat's different
Azure SQL Managed Instance (near 100% compatible)RDS for SQL ServerClosest to "no change", but some server-level permissions and features are restricted
Azure Database Migration ServiceAWS DMSBoth do online migrations with CDC. DMS also validates data and supports many non-Microsoft targets
Data Migration Assistant, SSMADMS Schema Conversion, Babelfish CompassAWS splits assessment (Compass) from conversion (DMS Schema Conversion)
Azure SQL HyperscaleAurora (PostgreSQL or MySQL-compatible)Aurora separates compute and storage similarly; the engine is different
SQL Agent jobsEventBridge Scheduler, Lambda, pg_cron on PostgreSQLJobs are not migrated by DMS; plan them as part of the application
Always On availability groupsRDS Multi-AZ, Aurora replicasFailover mechanics differ; see Database failover

Architecture

Security-boundary view. Source and target never touch the internet: DMS reaches the source over the private link, and the target sits in private subnets with security groups that admit only DMS, the reconciliation job and the application. Credentials live in Secrets Manager, and DMS reads them through a role limited to those secrets. Encrypt the target with a customer-managed KMS key and enforce TLS to both endpoints. The DMS instance holds a copy of production data in transit, so treat it as a production system.

Step-by-step implementation

Choose the target

OptionChoose whenWatch out for
RDS for SQL ServerDeadline-driven, large T-SQL estate, features like SQL Agent, CLR or linked servers matterLicence cost; feature restrictions; edition limits
Aurora PostgreSQL with BabelfishWish to cut licence cost and keep much T-SQL and SqlClient code for nowCompatibility gaps, behavioural differences, migration mode fixed at creation
Full conversion to PostgreSQLLong-term strategic move, simple schema, appetite to change codeLargest effort: procedures, application SQL, EF Core provider, and every behaviour difference

Triage compatibility

Run the scanner (below) over exported DDL and application SQL for a first look, then run Babelfish Compass and DMS Schema Conversion assessments for the authoritative answer. Treat the scanner as a conversation starter: Compass is the reference for Babelfish support.

Prepare the source for CDC

Set the database to full recovery, ensure transaction-log backups are taken and retained long enough for DMS to read, and enable MS-CDC or MS-Replication according to the DMS source documentation for your edition ⚠ VERIFY prerequisites for your SQL Server version. Create a least-privilege DMS login.

Convert the schema (if the engine changes)

Create the target schema from the conversion output, then review by hand: data types (datetime to timestamp, money to numeric), identity columns, computed columns, defaults, constraints and indexes. Create secondary objects before the cut-over, but leave foreign keys and triggers off during full load if you load in parallel.

Run full load plus CDC with validation

Create endpoints, the replication instance and a task with migration-type: full-load-and-cdc and validation enabled. Watch latency and validation failures. Rehearse the whole thing at least twice on a recent copy of production data.

Reconcile independently

DMS validation is helpful but runs inside the same tool that copied the data. Add an independent reconciliation by business date: row counts and control totals of money columns, compared with decimal (code below).

Cut over and keep a way back

Stop writes on the source, let CDC drain to zero lag, run final reconciliation, switch the application, and keep reverse replication or the frozen source for the rollback window (see Cut-over and rollback planning).

AWS Console instructions

  • Console → AWS DMS → Subnet groups → Create (two private subnets in different AZs).
  • Replication instances → Create (smallest class for a rehearsal, Multi-AZ off, not publicly accessible, KMS key).
  • Endpoints → Create a source endpoint (SQL Server) and a target endpoint (Aurora PostgreSQL), using AWS Secrets Manager for credentials; run Test connection.
  • Database migration tasks → Create with Full load, ongoing replication, table mappings (JSON), and Enable validation on.
  • Schema conversion (in DMS) → create an instance profile, a data provider pair and a migration project; run the assessment report and convert.

AWS CLI

# Create the replication instance (smallest class, private, encrypted).
aws dms create-replication-instance \
  --replication-instance-identifier hl-m13-dms \
  --replication-instance-class dms.t3.medium \
  --allocated-storage 50 \
  --no-publicly-accessible \
  --replication-subnet-group-identifier hl-m13-dms-subnets \
  --tags Key=Project,Value=cea-lab Key=Lab,Value=m13-dms Key=Environment,Value=learn Key=Owner,Value=your-name \
  --profile learn-sandbox --region us-east-1

# Create the task: full load then CDC, with table mappings and settings from files.
aws dms create-replication-task \
  --replication-task-identifier hl-m13-ledger \
  --source-endpoint-arn arn:aws:dms:us-east-1:123456789012:endpoint:SRC \
  --target-endpoint-arn arn:aws:dms:us-east-1:123456789012:endpoint:TGT \
  --replication-instance-arn arn:aws:dms:us-east-1:123456789012:rep:INSTANCE \
  --migration-type full-load-and-cdc \
  --table-mappings file://table-mappings.json \
  --replication-task-settings file://task-settings.json \
  --profile learn-sandbox --region us-east-1

# Start it and watch progress and validation.
aws dms start-replication-task --replication-task-arn arn:aws:dms:us-east-1:123456789012:task:TASK \
  --start-replication-task-type start-replication --profile learn-sandbox --region us-east-1
aws dms describe-table-statistics --replication-task-arn arn:aws:dms:us-east-1:123456789012:task:TASK \
  --profile learn-sandbox --region us-east-1 \
  --query "TableStatistics[].{table:TableName,rows:FullLoadRows,inserts:Inserts,validation:ValidationState}" --output table

Table mappings (table-mappings.json) include the dbo schema, rename it to a lower-case target schema and lower-case columns:

table-mappings.json
{
  "rules": [
    {
      "rule-type": "selection", "rule-id": "1", "rule-name": "include-ledger",
      "object-locator": { "schema-name": "dbo", "table-name": "%" },
      "rule-action": "include"
    },
    {
      "rule-type": "transformation", "rule-id": "2", "rule-name": "schema-to-lower",
      "rule-target": "schema", "object-locator": { "schema-name": "dbo" },
      "rule-action": "rename", "value": "ledger"
    },
    {
      "rule-type": "transformation", "rule-id": "3", "rule-name": "tables-to-lower",
      "rule-target": "table", "object-locator": { "schema-name": "dbo", "table-name": "%" },
      "rule-action": "convert-lowercase"
    },
    {
      "rule-type": "transformation", "rule-id": "4", "rule-name": "columns-to-lower",
      "rule-target": "column", "object-locator": { "schema-name": "dbo", "table-name": "%", "column-name": "%" },
      "rule-action": "convert-lowercase"
    }
  ]
}
task-settings.json
{
  "TargetMetadata": { "SupportLobs": true, "LimitedSizeLobMode": true, "LobMaxSize": 32 },
  "FullLoadSettings": { "TargetTablePrepMode": "DO_NOTHING", "MaxFullLoadSubTasks": 4 },
  "ValidationSettings": { "EnableValidation": true, "ThreadCount": 5 },
  "Logging": { "EnableLogging": true }
}

DO_NOTHING tells DMS to keep the schema you created and reviewed. LobMaxSize is in kilobytes, so set it from a measured maximum, not a guess ⚠ VERIFY the setting semantics in the DMS task-settings reference.

Infrastructure as Code

DmsStack.cs
using Amazon.CDK;
using Amazon.CDK.AWS.DMS;
using Amazon.CDK.AWS.EC2;
using Constructs;

namespace Harbourline.Migration.Infra;

public sealed class DmsStack : Stack
{
    public DmsStack(Construct scope, string id, IVpc vpc, IStackProps? props = null)
        : base(scope, id, props)
    {
        var subnetIds = vpc.SelectSubnets(new SubnetSelection { SubnetType = SubnetType.PRIVATE_WITH_EGRESS })
                           .SubnetIds;

        var subnetGroup = new CfnReplicationSubnetGroup(this, "SubnetGroup", new CfnReplicationSubnetGroupProps
        {
            ReplicationSubnetGroupDescription = "hl-m13 DMS private subnets",
            SubnetIds = subnetIds
        });

        var instance = new CfnReplicationInstance(this, "Instance", new CfnReplicationInstanceProps
        {
            ReplicationInstanceClass = "dms.t3.medium",
            AllocatedStorage = 50,
            PubliclyAccessible = false,
            MultiAz = false, // learning shortcut: Multi-AZ for production cut-overs
            ReplicationSubnetGroupIdentifier = subnetGroup.Ref
        });
        instance.AddDependency(subnetGroup);

        Amazon.CDK.Tags.Of(this).Add("Project", "cea-lab");
        Amazon.CDK.Tags.Of(this).Add("Lab", "m13-dms");
        Amazon.CDK.Tags.Of(this).Add("Environment", "learn");
        Amazon.CDK.Tags.Of(this).Add("Owner", "your-name");
    }
}

Endpoints (CfnEndpoint with SecretsManagerSecretId and SecretsManagerAccessRoleArn inside MicrosoftSqlServerSettings and PostgreSqlSettings) and the task (CfnReplicationTask with TableMappings and ReplicationTaskSettings as JSON strings) follow the same pattern ⚠ VERIFY property names against the current CloudFormation DMS reference.

Complete code

T-SQL triage scanner

A first-pass scanner over .sql files that flags constructs that usually need work when the engine changes. It is not a compatibility oracle: Babelfish Compass and DMS Schema Conversion are.

Program.cs
using System.Text.RegularExpressions;

if (args.Length != 1 || !Directory.Exists(args[0]))
{
    Console.Error.WriteLine("usage: dotnet run -- <folder-with-sql-files>");
    return 1;
}

var counts = new SortedDictionary<string, int>(StringComparer.Ordinal);
var files = Directory.EnumerateFiles(args[0], "*.sql", SearchOption.AllDirectories).ToList();

foreach (var file in files)
{
    var sql = StripComments(File.ReadAllText(file));
    foreach (var f in Findings.All)
    {
        var n = f.Pattern.Matches(sql).Count;
        if (n == 0) continue;
        counts[f.Name] = counts.GetValueOrDefault(f.Name) + n;
        Console.WriteLine($"{Path.GetRelativePath(args[0], file)}: {f.Name} x{n} -> {f.Advice}");
    }
}

Console.WriteLine($"\n{files.Count} file(s) scanned. Totals:");
foreach (var (name, total) in counts) Console.WriteLine($"  {total,5}  {name}");
Console.WriteLine("\nNext: run Babelfish Compass and the DMS Schema Conversion assessment for the authoritative answer.");
return 0;

static string StripComments(string sql) =>
    Regex.Replace(sql, @"--.*?$|/\*.*?\*/", " ", RegexOptions.Singleline | RegexOptions.Multiline);

sealed record Finding(string Name, Regex Pattern, string Advice);

static class Findings
{
    private static Regex R(string p) => new(p, RegexOptions.IgnoreCase | RegexOptions.Compiled | RegexOptions.CultureInvariant);

    public static readonly Finding[] All =
    [
        new("xp_cmdshell / OS access", R(@"\bxp_cmdshell\b"), "Move to application or Lambda; not available on managed targets"),
        new("CLR assemblies", R(@"\bCREATE\s+ASSEMBLY\b"), "Rewrite in the application or as a service"),
        new("Linked servers / OPENQUERY", R(@"\bOPENQUERY\b|\bOPENROWSET\b|\bsp_addlinkedserver\b"), "Replace with application integration or DMS"),
        new("Service Broker", R(@"\bCREATE\s+(QUEUE|SERVICE|CONTRACT)\b|\bSEND\s+ON\s+CONVERSATION\b"), "Replace with SQS or EventBridge"),
        new("Spatial and hierarchy types", R(@"\b(geography|geometry|hierarchyid)\b"), "Check target support; PostGIS or redesign"),
        new("FOR XML / XML methods", R(@"\bFOR\s+XML\b|\.nodes\s*\(|\.value\s*\("), "Review: behaviour and support differ"),
        new("MERGE statements", R(@"\bMERGE\b"), "Review semantics; rewrite as INSERT ... ON CONFLICT if converting"),
        new("Temporal tables", R(@"\bSYSTEM_VERSIONING\b"), "No direct equivalent in PostgreSQL; use history tables and triggers"),
        new("NOLOCK hints", R(@"\bWITH\s*\(\s*NOLOCK\s*\)|\bNOLOCK\b"), "Dirty reads: reconsider isolation (see Module 6)"),
        new("Identity retrieval (@@IDENTITY)", R(@"@@IDENTITY\b"), "Use SCOPE_IDENTITY, OUTPUT or RETURNING"),
        new("Cursors", R(@"\bDECLARE\s+\w+\s+CURSOR\b"), "Rewrite as set-based SQL where possible"),
        new("Dynamic SQL", R(@"\bsp_executesql\b|\bEXEC\s*\(\s*@"), "Review for injection and for dialect differences"),
        new("Global temp tables", R(@"##\w+"), "Different semantics on the target"),
        new("TOP with TIES / PERCENT", R(@"\bTOP\s*\(?\s*\d+\s*\)?\s+(PERCENT|WITH\s+TIES)\b"), "Rewrite with FETCH FIRST or window functions")
    ];
}

Independent reconciliation by business date

The comparison logic is a pure function so it is easy to test. Each side supplies, per booking date, a row count and a control total.

Reconciliation.cs
namespace Harbourline.Migration.Reconciliation;

public readonly record struct DayTotals(long Count, decimal Sum);

public sealed record Difference(DateOnly Date, DayTotals? Source, DayTotals? Target, string Kind);

public static class Reconciler
{
    public static IReadOnlyList<Difference> Compare(
        IReadOnlyDictionary<DateOnly, DayTotals> source,
        IReadOnlyDictionary<DateOnly, DayTotals> target)
    {
        var diffs = new List<Difference>();

        foreach (var (date, s) in source.OrderBy(kv => kv.Key))
        {
            if (!target.TryGetValue(date, out var t))
            {
                diffs.Add(new Difference(date, s, null, "missing-on-target"));
                continue;
            }

            // Money is compared exactly. decimal equality, never a tolerance, for amounts.
            if (s.Count != t.Count) diffs.Add(new Difference(date, s, t, "count-mismatch"));
            else if (s.Sum != t.Sum) diffs.Add(new Difference(date, s, t, "sum-mismatch"));
        }

        foreach (var (date, t) in target.OrderBy(kv => kv.Key))
            if (!source.ContainsKey(date))
                diffs.Add(new Difference(date, null, t, "unexpected-on-target"));

        return diffs;
    }
}

The two SQL queries that fill the dictionaries (one per engine) return the same shape:

source (T-SQL)
SELECT CAST(booking_date AS date) AS d, COUNT_BIG(*) AS n, SUM(amount) AS total
FROM dbo.transactions
WHERE status IN ('booked','reversed')
GROUP BY CAST(booking_date AS date);
target (PostgreSQL)
SELECT booking_date::date AS d, COUNT(*) AS n, SUM(amount) AS total
FROM ledger.transactions
WHERE status IN ('booked','reversed')
GROUP BY booking_date::date;

EF Core: SQL Server to PostgreSQL

LedgerContext.cs
using Microsoft.EntityFrameworkCore;

namespace Harbourline.Ledger.Data;

public sealed class Account
{
    public required string AccountId { get; set; }
    public required string Currency { get; set; }
    public decimal Balance { get; set; }
    public DateTime OpenedAtUtc { get; set; }
    public uint Version { get; set; } // maps to PostgreSQL xmin, replacing SQL Server rowversion
}

public sealed class LedgerContext(DbContextOptions<LedgerContext> options) : DbContext(options)
{
    public DbSet<Account> Accounts => Set<Account>();

    protected override void OnModelCreating(ModelBuilder b)
    {
        b.HasDefaultSchema("ledger");

        b.Entity<Account>(e =>
        {
            e.ToTable("accounts");
            e.HasKey(x => x.AccountId);
            e.Property(x => x.AccountId).HasColumnName("account_id").HasMaxLength(14);
            e.Property(x => x.Currency).HasColumnName("currency").HasMaxLength(3);

            // Money: exact numeric with explicit precision, never float or the SQL Server money type.
            e.Property(x => x.Balance).HasColumnName("balance").HasPrecision(18, 2);

            // PostgreSQL timestamptz stores UTC; Npgsql rejects Kind=Unspecified for it.
            e.Property(x => x.OpenedAtUtc).HasColumnName("opened_at").HasColumnType("timestamp with time zone");

            // Optimistic concurrency: xmin is PostgreSQL's row version system column.
            e.Property(x => x.Version).HasColumnName("xmin").HasColumnType("xid")
                .ValueGeneratedOnAddOrUpdate().IsConcurrencyToken();
        });
    }
}
Program.cs (registration)
builder.Services.AddDbContext<LedgerContext>(o =>
    o.UseNpgsql(builder.Configuration.GetConnectionString("Ledger")));
// Was: o.UseSqlServer(...). With Babelfish you may keep UseSqlServer and point the connection
// string at the Babelfish TDS endpoint (port 1433), then test every query and migration.

Code explained line by line

  1. The scanner strips SQL comments first (StripComments), so commented-out code does not inflate counts. The regexes are case-insensitive because T-SQL keywords are.
  2. Each Finding carries advice. The summary counts per category and gives you a sizing input for the conversion estimate, not a verdict.
  3. Reconciler.Compare iterates the source dates in order and records three kinds of difference: missing on target, count mismatch and sum mismatch. A final loop catches rows that exist only on the target (an unexpected load or a replay).
  4. The comparison uses decimal equality, never a tolerance. A tolerance on money hides rounding or truncation bugs.
  5. In LedgerContext, HasPrecision(18, 2) matches the canonical money type; leaving precision off produces a provider default that may differ per engine.
  6. HasColumnType("timestamp with time zone") plus UTC DateTime values is the Npgsql rule: pass DateTimeKind.Utc values only.
  7. xmin as a concurrency token replaces rowversion. It is a system column, so it must not be in your migration's table creation; EF treats it as database-generated.
  8. The registration comment shows the Babelfish choice: the same SqlClient code, a different endpoint, a full regression test.

Security considerations

⚠ Learning shortcut — not for production

The lesson uses a single-AZ replication instance, a small class and a rehearsal copy of data. In production use Multi-AZ replication instances for cut-over runs, an isolated migration account, a customer-managed KMS key for the instance and target, TLS on both endpoints, Secrets Manager rotation for the migration credentials, and a data-masking step if rehearsal data leaves the production boundary.

  • Production data in a rehearsal environment. A rehearsal copy is personal data under UK GDPR. Use the production controls, or mask before copying. This needs data-protection and legal review; the lesson cannot sign it off.
  • Least-privilege DMS roles. The source login needs read plus CDC rights, not sysadmin. The target role needs create and write on the target schema only.
  • Audit trail. CloudTrail records DMS API calls; keep DMS task logs in CloudWatch Logs with retention set (never infinite). Record who started and who approved each run.
  • Generative-AI conversion and Region. AI-assisted schema conversion may process code in another Region ⚠ VERIFY; get approval before enabling it.

Banking use case

Harbourline's HarbourlineLedger (600 GB, 180 tables) is a Babelfish candidate for the reporting databases, but the core ledger moves to RDS for SQL Server first: replatform, no engine change, short deadline. Two rehearsals run on a masked copy. In rehearsal one, reconciliation shows a missing day, traced to a log backup gap that truncated the log before DMS read it. In rehearsal two, all 1,826 dates match to the penny, DMS validation reports zero failures, and the cut-over timeline is: stop writes at 23:00, CDC lag to zero at 23:06, reconciliation at 23:20, application switch at 23:30, with reverse replication prepared. The Babelfish candidate waits for its own wave after Compass shows eleven unsupported constructs.

Common mistakes

  • Assuming DMS migrates procedures and jobs. It does not; schema conversion or manual work does, and jobs become scheduled services.
  • Using validation alone as evidence. Use an independent reconciliation too, in different code.
  • Letting collation drift. A different collation changes sort order and equality for names. Set it deliberately and test.
  • Ignoring time zones and datetime precision. datetime has 3.33 ms granularity; timestamp has microseconds. Define the rule.
  • Forgetting identity and sequences. After the load, reset sequences or identity seeds so new inserts do not collide.
  • Testing only on small data. Performance and LOB behaviour only appear at realistic size.

Troubleshooting

SymptomLikely causeFix
DMS task fails at start with a CDC errorSource not in the right recovery model or CDC not enabledCheck the DMS source prerequisites for your edition and log-backup chain
Latency keeps growingTarget slow, a big transaction, or an undersized instanceScale the instance class, check target indexes and constraints during load
Validation shows rows "suspended" or mismatchedLOB truncation, or timing of in-flight changesReview LOB settings; revalidate after lag reaches zero
Npgsql: Cannot write DateTime with Kind=UnspecifiedLocal or unspecified DateTime valuesUse UTC (DateTime.UtcNow, TimeProvider) and timestamptz
EF Core queries return different orderingCollation and null ordering differAdd explicit ORDER BY, review collation
Babelfish rejects a statementUnsupported T-SQLCheck the Compass report, rewrite, or choose another target

Cost and cleanup

Cost: credits. The cost is the DMS replication instance per hour (small class), its storage, the target database per hour and any data transfer, for the hours it exists ⚠ VERIFY current prices. A same-day rehearsal is typically a few dollars. DMS Schema Conversion itself has no additional charge. Never leave a replication instance running overnight.

# Stop and delete the task, then the endpoints, then the instance.
aws dms stop-replication-task --replication-task-arn arn:aws:dms:us-east-1:123456789012:task:TASK --profile learn-sandbox
aws dms delete-replication-task --replication-task-arn arn:aws:dms:us-east-1:123456789012:task:TASK --profile learn-sandbox
aws dms delete-endpoint --endpoint-arn arn:aws:dms:us-east-1:123456789012:endpoint:SRC --profile learn-sandbox
aws dms delete-endpoint --endpoint-arn arn:aws:dms:us-east-1:123456789012:endpoint:TGT --profile learn-sandbox
aws dms delete-replication-instance --replication-instance-arn arn:aws:dms:us-east-1:123456789012:rep:INSTANCE --profile learn-sandbox

# Delete the target database (Aurora cluster or RDS instance) and its snapshots, or run: cdk destroy / terraform destroy.
# Confirm nothing tagged Project=cea-lab remains.
aws resourcegroupstaggingapi get-resources --tag-filters Key=Project,Values=cea-lab --profile learn-sandbox

Exercises

  • 🟢 Guided: Run the scanner on a folder containing three .sql files you write (one with MERGE, one with a cursor and NOLOCK, one clean). Confirm the totals. Then write two xUnit tests for Reconciler.Compare: identical inputs return no differences; a one-penny difference returns sum-mismatch.
  • 🟡 Challenge: Extend DayTotals with a Hash (for example a 64-bit hash of the concatenated transaction IDs for that day) and compare it too. Explain what a matching count and sum but different hash would mean.
  • 🔴 Live problem: Rehearsal two matches on all dates, but the finance team says the closing balance of one account differs by 0.01 GBP after cut-over rehearsal. Describe how you would find it (which queries, which tables, which engine behaviour) and what you would tell the change board.

Assignment

Write database-migration-plan.md in your portfolio repository for a fictional bank database: target choice with reasons, compatibility findings (scanner plus the tools you would run), DMS design, reconciliation design with the queries, and the rollback approach.

Solutions

Knowledge check

Interview questions

  • How do you migrate a database with near-zero downtime? Full load, then CDC until lag is near zero, a short write freeze, a final reconciliation and a connection switch, with a rehearsed rollback.
  • What would make you reject a Babelfish migration? Heavy use of unsupported features such as CLR, Service Broker or linked servers, or behaviour differences that cannot be tested before the deadline.
  • How do you prove nothing was lost? Counts and control totals by business date, row hashes on key columns, DMS validation, and a sampled business-level check by finance.

Certification practice

Completion checklist

  • I can justify a target choice for a SQL Server database
  • I ran the scanner and know what it does not prove
  • I wrote tests for the reconciler, including the one-penny case
  • I know what DMS does and does not migrate
  • I ran the cleanup steps and confirmed nothing is still billing

Key takeaways

  • Two decisions: which engine, and how the data moves.
  • DMS moves data; conversion tools and people move code.
  • Validate twice: DMS validation and an independent reconciliation with exact decimals.
  • Money, time zones, collation and concurrency are the bug magnets in an application port.
  • Destroy the replication instance the same day.

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