Minimal-downtime SQL Server migration to AWS with AWS DMS full load + CDC

#When to use DMS

• Downtime window too small for backup/restore of a large database.
• No native log chain possible (for example, partial table sets, or consolidating databases).
• Same pattern also feeds heterogeneous targets (Aurora PostgreSQL) — but for SQL → SQL, native backup/restore or AGs are often simpler.

#1. Schema first

DMS moves tables and data, not the full schema (secondary indexes, constraints, triggers, procedures, users). So:

1. Script the schema (SSMS Generate Scripts or SqlPackage DACPAC) and deploy tables without foreign keys and triggers (or disable them).
2. Run DMS full load + CDC.
3. Before cutover: create remaining indexes, add foreign keys, enable triggers.

#2. Source prerequisites (on-premises SQL Server)

• Database in FULL recovery with a full backup taken.
• Enable ongoing replication support, one of:
  • MS-Replication (tables with primary keys; DMS uses replication publications), or
  • MS-CDC (for tables without primary keys, or when you prefer CDC):

EXEC sys.sp_cdc_enable_db;
EXEC sys.sp_cdc_enable_table @source_schema = N'dbo', @source_name = N'Orders', @role_name = NULL;

• Retain logs long enough (DMS reads the transaction log / CDC tables; keep the log from being truncated before DMS reads it).
• A DMS user with the documented permissions (db_owner on the database is simplest during migration, plus VIEW SERVER STATE etc.).

#3. Networking

DMS replication instance in a private subnet that reaches on-premises SQL Server over Direct Connect or VPN on port 1433, and the target on 1433.

#4. Create the task

• Endpoints: source (SQL Server) and target (RDS), tested with Test connection.
• Migration type: Migrate existing data and replicate ongoing changes.
• Table mappings: include schemas/tables explicitly.
• Target table preparation: Do nothing (schema already created) or Truncate.
• LOB settings: Limited LOB mode with a max size that covers your data (much faster), or Full LOB mode for unknown sizes.
• Enable data validation.

aws dms create-replication-task --replication-task-identifier sales-full-cdc \
  --source-endpoint-arn $SRC --target-endpoint-arn $TGT --replication-instance-arn $RI \
  --migration-type full-load-and-cdc --table-mappings file://mappings.json \
  --replication-task-settings file://task-settings.json

(DMS Serverless is an option that scales capacity automatically.)

#5. Monitor

• CloudWatch: CDCLatencySource, CDCLatencyTarget, full load progress per table.
• Table statistics: inserts/updates/deletes applied, validation state (Validated, Mismatched records).

#6. Cutover

1. Stop the app; wait until CDC latency is ~0 and validation is clean.
2. Stop the DMS task.
3. Apply remaining schema (FKs, triggers), reseed identity columns:

DBCC CHECKIDENT ('dbo.Orders', RESEED);   -- sets seed to current max

4. Logins, jobs, permissions; switch connection strings; smoke tests.
5. Optional: a reverse DMS task (RDS → on-premises) as a rollback path.

Warning

Identity columns, sequences, computed columns and triggers are the classic DMS pitfalls. Disable triggers on the target during the load, and reseed identities before the app writes, or you'll get duplicate key errors on the first insert.