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.
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
- Database migration (Module 6) — the deep dive on DMS, CDC prerequisites and the validation tool. This lesson places that work inside a whole-estate migration and adds the application side.
- RDS for SQL Server and PostgreSQL and Amazon Aurora
- Migration assessment and portfolio discovery — which databases you are moving and in which wave
- Tools: Docker (SQL Server and PostgreSQL containers), .NET 10 SDK, AWS CLI v2
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:
- 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.
- 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
| Tool | What it does | Honest limits (checked 2026-11-18) |
|---|---|---|
| AWS DMS | Full load and CDC between many engines, premigration assessments, built-in validation, serverless option | Moves data and basic table structure, not procedures, jobs or users. CDC needs source prerequisites (recovery model, log retention). LOB columns need tuning |
| DMS Schema Conversion | Assesses 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 scripts | No 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 converter | Status could not be confirmed here ⚠ VERIFY before relying on it |
| Babelfish for Aurora PostgreSQL | TDS 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 Server | Managed SQL Server with backups, Multi-AZ, patching | Licence-included pricing is high; some server-level features are restricted; edition and BYOL options vary ⚠ VERIFY |
Microsoft / Azure comparison
| You know (Microsoft/Azure) | AWS equivalent | What's different |
|---|---|---|
| Azure SQL Managed Instance (near 100% compatible) | RDS for SQL Server | Closest to "no change", but some server-level permissions and features are restricted |
| Azure Database Migration Service | AWS DMS | Both do online migrations with CDC. DMS also validates data and supports many non-Microsoft targets |
| Data Migration Assistant, SSMA | DMS Schema Conversion, Babelfish Compass | AWS splits assessment (Compass) from conversion (DMS Schema Conversion) |
| Azure SQL Hyperscale | Aurora (PostgreSQL or MySQL-compatible) | Aurora separates compute and storage similarly; the engine is different |
| SQL Agent jobs | EventBridge Scheduler, Lambda, pg_cron on PostgreSQL | Jobs are not migrated by DMS; plan them as part of the application |
| Always On availability groups | RDS Multi-AZ, Aurora replicas | Failover 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
| Option | Choose when | Watch out for |
|---|---|---|
| RDS for SQL Server | Deadline-driven, large T-SQL estate, features like SQL Agent, CLR or linked servers matter | Licence cost; feature restrictions; edition limits |
| Aurora PostgreSQL with Babelfish | Wish to cut licence cost and keep much T-SQL and SqlClient code for now | Compatibility gaps, behavioural differences, migration mode fixed at creation |
| Full conversion to PostgreSQL | Long-term strategic move, simple schema, appetite to change code | Largest 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 tableTable mappings (table-mappings.json) include the dbo schema, rename it to a lower-case target schema and lower-case columns:
{
"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"
}
]
}{
"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
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.
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.
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:
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);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
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();
});
}
}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
- 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. - Each
Findingcarries advice. The summary counts per category and gives you a sizing input for the conversion estimate, not a verdict. Reconciler.Compareiterates 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).- The comparison uses
decimalequality, never a tolerance. A tolerance on money hides rounding or truncation bugs. - In
LedgerContext,HasPrecision(18, 2)matches the canonical money type; leaving precision off produces a provider default that may differ per engine. HasColumnType("timestamp with time zone")plus UTCDateTimevalues is the Npgsql rule: passDateTimeKind.Utcvalues only.xminas a concurrency token replacesrowversion. It is a system column, so it must not be in your migration's table creation; EF treats it as database-generated.- 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
datetimeprecision.datetimehas 3.33 ms granularity;timestamphas 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
| Symptom | Likely cause | Fix |
|---|---|---|
| DMS task fails at start with a CDC error | Source not in the right recovery model or CDC not enabled | Check the DMS source prerequisites for your edition and log-backup chain |
| Latency keeps growing | Target slow, a big transaction, or an undersized instance | Scale the instance class, check target indexes and constraints during load |
| Validation shows rows "suspended" or mismatched | LOB truncation, or timing of in-flight changes | Review LOB settings; revalidate after lag reaches zero |
Npgsql: Cannot write DateTime with Kind=Unspecified | Local or unspecified DateTime values | Use UTC (DateTime.UtcNow, TimeProvider) and timestamptz |
| EF Core queries return different ordering | Collation and null ordering differ | Add explicit ORDER BY, review collation |
| Babelfish rejects a statement | Unsupported T-SQL | Check 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-sandboxExercises
- 🟢 Guided: Run the scanner on a folder containing three
.sqlfiles you write (one withMERGE, one with a cursor andNOLOCK, one clean). Confirm the totals. Then write two xUnit tests forReconciler.Compare: identical inputs return no differences; a one-penny difference returnssum-mismatch. - 🟡 Challenge: Extend
DayTotalswith aHash(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
Tests: build two dictionaries with one date and identical DayTotals(10, 100.00m), and assert the result is empty. Change the target sum to 100.01m and assert one Difference with Kind == "sum-mismatch".
Why: the one-penny test proves the comparison is exact. A tolerance would make this test pass and the bank's ledger wrong.
Compute the hash in SQL on both engines with the same ordering and encoding (for example, sort by transaction ID and hash the concatenation), then compare in C#. A matching count and sum with a different hash means different rows with coincidentally the same total, for example a posting moved between two accounts, or a changed ID: a defect that count and sum alone would never find.
Why: control totals catch loss and rounding; hashes catch substitution. Banks need both because errors that cancel out are exactly the ones that survive reviews.
Knowledge check
Answer: It runs inside the tool that moved the data, so a systematic misconfiguration can affect both copy and check. An independent reconciliation in different code, by business date with exact decimal totals, is stronger evidence.
Answer: Stored procedures, functions, triggers, SQL Agent jobs, logins and most secondary objects. Schema conversion tools and manual work handle them.
Answer: When you want to reduce licensing cost soon, the code base is T-SQL heavy, and the Compass report shows few unsupported constructs. Full conversion fits a long-term strategic move with budget to change code.
Answer: It does not exist there; map a uint property to the xmin system column as a concurrency token.
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
A bank must migrate a 1 TB SQL Server database to Amazon Aurora PostgreSQL with under 30 minutes of downtime. The schema contains stored procedures. What is the best approach?
A. Use AWS DMS full load only, and migrate procedures automatically. B. Convert the schema and code with DMS Schema Conversion, then use AWS DMS full load plus CDC, validate, and cut over after the lag reaches zero. C. Export to CSV and import into PostgreSQL over a weekend. D. Use Application Migration Service to replicate the database server's disks.
Answer: B. Schema conversion handles code, DMS full load plus CDC keeps downtime small. A is wrong because DMS does not migrate procedures, C has a long outage, and D would produce a SQL Server instance, not an Aurora PostgreSQL one.
A company wants to cut SQL Server licensing costs while keeping most T-SQL code and applications unchanged. Which target may fit best?
A. Amazon DynamoDB. B. Amazon Aurora PostgreSQL with Babelfish, after a compatibility assessment. C. Amazon S3 with Athena. D. A larger RDS for SQL Server instance.
Answer: B. Babelfish lets Aurora PostgreSQL accept T-SQL and the SQL Server protocol for supported features, removing the SQL Server licence. A and C would need redesign; D increases cost.
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
- Database migration (Module 6) — full DMS and validation walkthrough
- Next: Network migration and hybrid architecture
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.
.NET Framework to modern .NET
Move a .NET Framework 4.8 banking application to .NET 10: find the blockers with a scanner, choose between AWS Transform for .NET, Microsoft tooling and a strangler approach, fix WCF, Web Forms and Windows-only code, containerise on Linux, and decide honestly when staying on Windows is right.
Network migration and hybrid architecture
Design the network that carries a long migration: plan non-overlapping address space, choose Site-to-Site VPN or Direct Connect, connect with Transit Gateway, share DNS in both directions with Route 53 Resolver, extend Active Directory, and handle latency and bandwidth while applications live in both worlds.