Phase 1 — Assessment & Planning

  1. Choose target engine
    • Aurora PostgreSQL (recommended for PostgreSQL features & ecosystem).
    • Aurora MySQL if your app is already MySQL-based.
  2. Inventory & compatibility assessment
    • Catalog databases, tables, indexes, constraints, stored procedures, triggers, views, jobs, and linked servers.
    • Identify MSSQL-specific items: T-SQL procedures, CLR assemblies, SQL Server Agent jobs, IDENTITY, DATETIME2, MONEY, NVARCHAR(MAX), temp-table patterns, use of WITH (NOLOCK), etc.
  3. Select migration tools
    • AWS Schema Conversion Tool (SCT) — converts schema & flags manual work.
    • AWS Database Migration Service (DMS) — full load + change data capture (CDC) for minimal downtime migration.
    • Supplemental: custom scripts, logical replication, or third-party ETL tools for complex transformations.
  4. Define success criteria
    • Data correctness (row counts, checksums), application functional tests, latency/throughput targets, and acceptable cutover window.

Phase 2 — Schema Conversion

  1. Run AWS SCT
    • Point SCT at MSSQL source and Aurora PostgreSQL target. Export conversion report and generated DDL.
    • Review automated conversions (green) and manual items (yellow/red).
  2. Refactor database code
    • Rewrite stored procedures, functions and triggers in PL/pgSQL where SCT cannot convert automatically.
    • Replace T-SQL constructs: IIF → CASE, TOP → LIMIT, OUTPUT semantics → RETURNING, etc.
    • Convert identity/sequence logic: MSSQL IDENTITY → Postgres SERIAL / GENERATED / sequences.
  3. Create schema on Aurora
    • Apply cleaned SCT DDL to a staging Aurora cluster. Validate constraints, indexes and privileges.
  4. Plan datatype & timezone handling
    • Decide canonical types (e.g., MSSQL DATETIMEOFFSET → Postgres timestamptz).

Phase 3 — Data Migration (DMS)

  1. Initial full load
    • Use AWS DMS in full load + ongoing replication mode to seed data and keep source/target in sync.
  2. Incremental / CDC
    • Enable CDC so DMS continually replicates changes during cutover prep.
  3. Validation
    • Row counts, checksums (e.g., hashed checks per table), sample record comparison, and referential integrity checks.
    • Resolve encoding, numeric precision, or timezone mismatches encountered during validation.
  4. Performance & tuning during load
    • Consider temporarily disabling non-critical indexes during full load and re-creating them after to speed up load.
    • Monitor DMS task logs, CPU, memory, and replication lag.

Phase 4 — Cutover

  1. Prepare applications
    • Ensure connection strings can point to Aurora endpoints and that driver/ORM supports PostgreSQL dialect.
    • Deploy application query changes (T-SQL → Postgres SQL) to staging beforehand.
  2. Final sync & freeze
    • Schedule a brief write freeze on MSSQL. Allow DMS to apply remaining CDC events until lag is zero.
  3. Switch traffic
    • Update application connection endpoints to Aurora; perform smoke tests and critical-path transactions.
    • Monitor errors, latencies, and DB metrics closely.
  4. Fallback plan
    • Have a rollback checklist — how to point apps back to MSSQL and any data reconciliation steps.

Post-cutover & Decommission

  • Keep both systems read-only for a short verification window if feasible.
  • Run full application test suite and load tests to validate performance.
  • After stabilization, schedule decommission of MSSQL resources and archive backups as required by compliance.

Checklist / Validation Items

  • Data correctness: row counts, CRCs/checksums for key tables.
  • Application functional tests & business process validation.
  • Performance tests: latency, throughput, read/write patterns.
  • Monitoring & alerts configured on Aurora (CPU, connections, replication lag, storage).
  • Backups & PITR verified.
  • Security: users, roles, parameter groups, VPC/subnet groups, KMS encryption keys.

Aurora PostgreSQL vs RDS PostgreSQL — Side-by-side

Feature Aurora PostgreSQL RDS PostgreSQL
Architecture Decoupled compute & distributed storage. Six copies across 3 AZs, auto-healing storage. Traditional single-instance with EBS-backed storage; optional Multi-AZ standby for HA.
Replication & readers Up to 15 low-latency reader instances using shared storage (fast failover & scaling). Up to 5 replicas using physical/logical replication; typically more lag than Aurora readers.
Failover time Typically sub-30 seconds (fast automated failover). Usually 1–2+ minutes depending on Multi-AZ configuration.
Performance Optimized storage/engine — often 2–3× higher throughput vs vanilla Postgres for similar hardware. Standard PostgreSQL performance characteristics.
Storage scaling Auto-scales up to 128 TB without downtime. Pre-allocated EBS; resizing may require downtime or I/O changes.
Backups & PITR Continuous backup to S3-backed storage with minimal impact. Automated snapshots and PITR using WAL archives; can have higher I/O impact.
Feature parity & versions Aurora may lag behind upstream PostgreSQL for new major releases; Aurora adds proprietary enhancements. Closer to upstream PostgreSQL; often quicker to support newest Postgres versions.
Cost Typically higher (engine/IO/replica benefits). Cost-effective for high-scale workloads where performance offsets price. Generally lower; predictable for standard workloads.
Best fit High-scale, low-latency, read-heavy, enterprise apps needing fast failover and large auto-scaling storage. Conventional workloads, smaller DBs, or teams wanting tight upstream Postgres compatibility and lower cost.

Aurora tips

  • Use parameter groups to tune Aurora for your workload (connection limits, work_mem, maintenance_work_mem, etc.).
  • For heavy writes, benchmark commit behavior — Aurora’s storage engine handles commit differently than typical Postgres on EBS.
  • Test long-running queries and background jobs (cron/pg_cron) after migration; scheduling may change semantics.
  • Consider using logical replication or pglogical for some specialized patterns if DMS/SCT aren’t appropriate.