Database Migration Tools Explained: AWS DMS, SCT, SSMA, DBConvert, and Azure DMS for SQL Server Professionals

---

Database migrations are one of the most stressful projects a DBA team takes on. The tools are unfamiliar, the documentation covers the happy path, and the gotchas only appear when you are already deep into the process. Most migration failures are not caused by data volume or complexity. They are caused by things nobody told you about: constraint naming rules that differ between engines, stored procedures that do not convert automatically, data types that map imperfectly, and replication instances left running after the migration that quietly bill you every hour.

This article covers the main migration tools used in AWS and Azure environments from the SQL Server professional’s perspective. What each tool does, how it fits with the others, when to use each one, and the real-world gotchas that documentation glosses over. Including the foreign key naming conflict that catches almost every team migrating from PostgreSQL to SQL Server.

1 The Two Problems Every Migration Has to Solve Beginner

Every database migration has two separate problems that require separate solutions. Understanding this distinction is the foundation for understanding why there are multiple tools rather than one tool that does everything.

Problem 1: Schema and object conversion. Tables, indexes, views, stored procedures, functions, triggers, constraints, and sequences all need to exist in the target database before any data can be loaded. When migrating between different database engines these objects often do not translate directly. T-SQL stored procedures do not run in PostgreSQL. PostgreSQL PL/pgSQL functions do not run in SQL Server. Data types are named differently and behave differently. Constraint scoping rules differ between engines. This conversion work requires a schema conversion tool.

Problem 2: Data movement. The actual rows in your tables need to move from the source to the target. For a one-time migration this is a bulk load. For a minimal-downtime migration it is continuous replication: bulk load the initial data and then keep the target synchronized with changes happening on the source until you are ready to cut over. This movement work requires a data migration and replication tool.

DMS, SCT, SSMA, and DBConvert all address these two problems in different combinations and with different strengths. Knowing which problem each tool solves tells you when to use it and when to reach for something else.

2 Migration Phases: Assessment, Schema, Data, Validation Beginner

Every successful migration follows the same four phases regardless of which tools you use. Skipping any phase is where migrations go wrong.

• Assessment. Connect the tool to the source database and generate a compatibility report. This tells you which objects will convert automatically, which need manual work, and what percentage of the overall migration you can automate. Most tools generate this as an assessment report before you commit to anything. Running this first prevents surprises mid-migration.
• Schema conversion. Convert and create all database objects in the target: tables, indexes, views, procedures, functions, triggers, constraints. Resolve any conversion issues flagged in the assessment before loading data. The schema must be complete and correct before data arrives.
• Data migration. Load the data. For online migrations this means initial full load followed by continuous change data capture to stay synchronized with the source. For offline migrations this is a one-time bulk load during a maintenance window.
• Validation. Verify that the migrated data matches the source. Row counts, checksums, sample data comparison, and application testing against the target. Never cut over without validation. The tools give you what looks like a successful migration. Validation confirms it actually is.

3 AWS DMS: The Data Replication Layer Beginner

-- DMS prerequisites on the SQL Server TARGET side
-- DMS needs these permissions to write to SQL Server

-- Create a dedicated DMS user on SQL Server
USE master;
CREATE LOGIN dms_user WITH PASSWORD = 'StrongPassword2026!';

USE YourTargetDatabase;
CREATE USER dms_user FOR LOGIN dms_user;

-- Grant required permissions
EXEC sp_addrolemember 'db_owner', 'dms_user';

-- For CDC on SQL SERVER AS SOURCE (reading changes)
-- SQL Server must have CDC enabled on source database and tables
USE YourSourceDatabase;
EXEC sys.sp_cdc_enable_db;  -- enable CDC on database

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'Orders',
    @role_name     = NULL;  -- enable CDC on specific table

-- Check CDC is enabled
SELECT name, is_cdc_enabled
FROM sys.databases
WHERE name = DB_NAME();

4 AWS SCT: The Schema Conversion Layer Beginner

5 SSMA: Microsoft’s Free Migration Assistant Intermediate

-- After SSMA migration: verify the extension pack was installed
-- SSMA installs its extension pack in a database named ssmatesterdb
-- or as a schema in the target database depending on version

-- Check for SSMA extension pack database
SELECT name FROM sys.databases WHERE name LIKE 'ssma%';

-- Check converted objects for extension pack dependencies
-- Converted procedures that call extension pack functions
-- will reference the ssma schema or a dedicated SSMA database
SELECT
    OBJECT_SCHEMA_NAME(object_id)       AS SchemaName,
    OBJECT_NAME(object_id)              AS ObjectName,
    type_desc
FROM sys.objects
WHERE OBJECT_DEFINITION(object_id) LIKE '%ssma%'
AND   type IN ('P', 'FN', 'TF', 'IF')
ORDER BY type_desc, OBJECT_NAME(object_id);

6 DBConvert: The All-in-One GUI Tool Beginner

7 Azure Database Migration Service Beginner

8 The FK Naming Conflict: PostgreSQL vs SQL Server Intermediate

This is the gotcha that causes the most confusion on PostgreSQL to SQL Server migrations and it catches teams using every tool in this article. Understanding why it happens and how to fix it before the migration saves significant debugging time.

Why It Happens

PostgreSQL allows constraint names (including foreign key constraint names) to be the same across different tables within the same schema. This is intentional PostgreSQL behavior and not considered a bug. You can have a constraint named FK_CustomerID on both dbo.Orders and dbo.OrderLines in the same PostgreSQL schema with no problem.

SQL Server requires constraint names to be unique within a schema. When DBConvert, SCT, or SSMA converts the PostgreSQL schema and brings over FK_CustomerID from both tables, SQL Server rejects the second one because the name already exists. The migration fails at the schema creation step with a constraint naming collision error.

-- This is valid in PostgreSQL -- same constraint name on two different tables
-- Orders table
ALTER TABLE orders
    ADD CONSTRAINT FK_CustomerID
    FOREIGN KEY (customer_id) REFERENCES customers(id);

-- OrderLines table (same constraint name, different table -- allowed in PostgreSQL)
ALTER TABLE order_lines
    ADD CONSTRAINT FK_CustomerID
    FOREIGN KEY (customer_id) REFERENCES customers(id);

-- SQL Server REJECTS this:
-- The second ALTER TABLE fails because FK_CustomerID already exists in the schema
-- Error: There is already an object named 'FK_CustomerID' in the database.

-- THE FIX: Rename constraints before migration
-- Query PostgreSQL to find duplicate constraint names:
SELECT
    tc.constraint_name,
    tc.table_name,
    tc.constraint_type,
    COUNT(*) OVER (PARTITION BY tc.constraint_name) AS NameCount
FROM information_schema.table_constraints tc
WHERE tc.constraint_schema = 'public'
ORDER BY NameCount DESC, tc.constraint_name;

-- Any constraint_name with NameCount > 1 will collide in SQL Server
-- Rename them before migration to include the table name:
-- FK_CustomerID on Orders -> FK_Orders_CustomerID
-- FK_CustomerID on OrderLines -> FK_OrderLines_CustomerID

How to Fix It Before Migration

The cleanest approach is to rename all constraint names in PostgreSQL before running the migration, adding the table name as a prefix to guarantee uniqueness. Run the duplicate detection query above, generate rename statements, execute them in PostgreSQL, then run the migration tool against the clean schema.

-- Generate rename statements for all duplicate constraint names in PostgreSQL
-- Run this against your PostgreSQL source before migration

SELECT
    'ALTER TABLE ' || tc.table_schema || '.' || tc.table_name ||
    ' RENAME CONSTRAINT ' || tc.constraint_name ||
    ' TO ' || tc.table_name || '_' || tc.constraint_name || ';' AS RenameStatement,
    tc.constraint_name,
    tc.table_name,
    COUNT(*) OVER (PARTITION BY tc.constraint_name, tc.constraint_schema) AS DuplicateCount
FROM information_schema.table_constraints tc
WHERE tc.constraint_schema = 'public'
AND   tc.constraint_type IN ('FOREIGN KEY', 'UNIQUE', 'CHECK')
ORDER BY DuplicateCount DESC, tc.constraint_name;

-- Copy the RenameStatement column for all rows where DuplicateCount > 1
-- Review and execute them in your PostgreSQL source database
-- Then re-run the migration tool against the renamed schema

9 Data Type Mapping Issues You Will Hit Intermediate

Every migration tool maps data types between engines automatically, but some mappings are imperfect and require review. These are the ones that cause problems most frequently.

PostgreSQL Type	SQL Server Mapping	Issue
SERIAL / BIGSERIAL	INT IDENTITY / BIGINT IDENTITY	Generally clean. Verify seed and increment match your needs.
BOOLEAN	BIT	PostgreSQL BOOLEAN accepts TRUE/FALSE/t/f/yes/no. SQL Server BIT only accepts 1/0. Application code that inserts string values needs updating.
TEXT	NVARCHAR(MAX)	Functional but check if indexes on TEXT columns existed. NVARCHAR(MAX) cannot be indexed directly in the same way.
JSONB / JSON	NVARCHAR(MAX)	PostgreSQL JSONB has native JSON operators and path queries. SQL Server stores JSON as a string and uses functions like JSON_VALUE and JSON_QUERY. All JSONB-specific queries must be rewritten.
ARRAY types	No native equivalent	PostgreSQL arrays have no direct SQL Server equivalent. Options: normalize to a child table, store as delimited string, or use JSON. Each has implications for application queries.
UUID	UNIQUEIDENTIFIER	Generally clean but verify application code using UUID functions works with SQL Server UNIQUEIDENTIFIER functions.
NUMERIC / DECIMAL	DECIMAL	Clean mapping. Verify precision and scale are explicitly specified in the migration.
TIMESTAMP WITH TIME ZONE	DATETIMEOFFSET	Functional but verify timezone handling. SQL Server DATETIMEOFFSET stores the offset, PostgreSQL TIMESTAMPTZ stores UTC internally and displays in session timezone.

10 Stored Procedures: What Converts and What Does Not Intermediate

Stored procedure conversion is where the 80 to 90 percent automatic conversion figure becomes important. The 10 to 20 percent that does not convert automatically is almost always stored procedures, functions, and triggers because procedural SQL differs significantly between engines.

When migrating from PostgreSQL to SQL Server, PL/pgSQL must be rewritten as T-SQL. The logic is equivalent but the syntax, error handling, cursor behavior, and many built-in functions are different. Migration tools will convert the simple cases but complex procedures with PostgreSQL-specific syntax require manual rewriting.

-- After schema migration: audit converted procedures for issues
-- Look for SSMA extension pack references and unconverted syntax

-- Find procedures that reference SSMA extension pack functions
SELECT
    OBJECT_SCHEMA_NAME(object_id)       AS SchemaName,
    OBJECT_NAME(object_id)              AS ProcedureName,
    LEN(OBJECT_DEFINITION(object_id))   AS DefinitionLength
FROM sys.objects
WHERE type = 'P'
AND   OBJECT_DEFINITION(object_id) LIKE '%ssma%'
ORDER BY OBJECT_NAME(object_id);

-- Find procedures with common PostgreSQL syntax remnants
-- that indicate incomplete conversion
SELECT
    OBJECT_SCHEMA_NAME(object_id)       AS SchemaName,
    OBJECT_NAME(object_id)              AS ProcedureName
FROM sys.objects
WHERE type = 'P'
AND (
    OBJECT_DEFINITION(object_id) LIKE '%RAISE NOTICE%'
    OR OBJECT_DEFINITION(object_id) LIKE '%RETURNING%'
    OR OBJECT_DEFINITION(object_id) LIKE '%PERFORM%'
    OR OBJECT_DEFINITION(object_id) LIKE '%->>%'  -- JSON operator
    OR OBJECT_DEFINITION(object_id) LIKE '%::text%'  -- PostgreSQL cast syntax
    OR OBJECT_DEFINITION(object_id) LIKE '%ILIKE%'  -- case-insensitive LIKE
)
ORDER BY OBJECT_NAME(object_id);

-- Review the unconverted procedures manually
-- Common rewrites needed:
-- RAISE NOTICE     -> PRINT or RAISERROR
-- RETURNING clause -> OUTPUT clause
-- ::type cast      -> CAST(x AS type)
-- ILIKE            -> LIKE (SQL Server LIKE is case-insensitive by default on CI collation)
-- String concat || -> +

11 Post-Migration: The Replication Instance Nobody Deleted Intermediate

This is the most expensive mistake in AWS DMS migrations and it happens on nearly every project at some point. The migration completes successfully. The cutover happens. The team moves on. The DMS replication instance keeps running and keeps billing.

A DMS replication instance is an EC2-based managed VM. It runs 24 hours a day, 7 days a week, at the instance type you selected. A t3.medium replication instance costs roughly $0.05 per hour. A larger instance used for a major migration can cost $0.15 to $0.50 per hour or more. Left running for a month after the migration completes, that is real money for a task that is done.

-- AWS CLI: check for running DMS replication instances
-- Run this after any migration to confirm cleanup

aws dms describe-replication-instances \
  --query 'ReplicationInstances[*].{Name:ReplicationInstanceIdentifier,
           Status:ReplicationInstanceStatus,
           Class:ReplicationInstanceClass,
           Created:InstanceCreateTime}' \
  --output table

-- Check for any running migration tasks
aws dms describe-replication-tasks \
  --query 'ReplicationTasks[*].{Task:ReplicationTaskIdentifier,
           Status:Status,
           Type:MigrationType}' \
  --output table

-- Stop and delete a completed task (replace task-arn with actual ARN)
aws dms stop-replication-task \
  --replication-task-arn arn:aws:dms:us-east-1:123456789:task:YOURTASKARN

aws dms delete-replication-task \
  --replication-task-arn arn:aws:dms:us-east-1:123456789:task:YOURTASKARN

-- Delete the replication instance after all tasks are deleted
aws dms delete-replication-instance \
  --replication-instance-arn arn:aws:dms:us-east-1:123456789:rep:YOURREPARN

12 Validation: How to Confirm the Migration Is Actually Correct Intermediate

Migration tools report success when the operation completes without errors. Success does not mean the data is correct. Validation is the step that confirms it.

-- Row count comparison: run against both source and target
-- Source (PostgreSQL):
SELECT
    schemaname                          AS SchemaName,
    tablename                           AS TableName,
    n_live_tup                          AS RowCount
FROM pg_stat_user_tables
ORDER BY schemaname, tablename;

-- Target (SQL Server):
SELECT
    OBJECT_SCHEMA_NAME(object_id)       AS SchemaName,
    OBJECT_NAME(object_id)              AS TableName,
    SUM(row_count)                      AS RowCount
FROM sys.dm_db_partition_stats
WHERE index_id IN (0, 1)
AND   OBJECTPROPERTY(object_id, 'IsUserTable') = 1
GROUP BY object_id
ORDER BY OBJECT_SCHEMA_NAME(object_id), OBJECT_NAME(object_id);

-- Compare row counts across both result sets
-- Any table where counts differ needs investigation

-- Spot-check data accuracy on high-value tables
-- Run equivalent queries on source and target and compare results:

-- Source (PostgreSQL):
SELECT SUM(total_amount) AS TotalRevenue,
       COUNT(*) AS OrderCount,
       MAX(order_date) AS LatestOrder
FROM orders
WHERE order_date >= '2026-01-01';

-- Target (SQL Server) -- same logic, SQL Server syntax:
SELECT SUM(total_amount) AS TotalRevenue,
       COUNT(*) AS OrderCount,
       MAX(order_date) AS LatestOrder
FROM dbo.Orders
WHERE order_date >= '2026-01-01';

-- Results must match before cutover

AWS DMS includes a data validation feature that can automatically compare source and target row by row and report discrepancies. Enable it on migration tasks for critical tables. It adds overhead to the migration but provides automated validation rather than requiring manual spot checks on every table.

13 Which Tool for Which Scenario Beginner

Migration Scenario	Schema Tool	Data Tool	Notes
PostgreSQL to SQL Server (one-time, any environment)	SSMA for PostgreSQL or DBConvert	SSMA (built-in) or DBConvert	Fix FK naming conflicts before running either tool
PostgreSQL to SQL Server (online, AWS environment)	AWS SCT	AWS DMS (Full load + CDC)	SCT first, then DMS for replication
Oracle to SQL Server	SSMA for Oracle	SSMA (built-in)	SSMA has the deepest Oracle to SQL Server support
MySQL to SQL Server	SSMA for MySQL	SSMA (built-in)	Free, well-supported path
SQL Server to Azure SQL DB or Managed Instance	Azure DMS (built-in assessment)	Azure DMS	Best supported path for this combination
SQL Server to Aurora PostgreSQL (AWS)	AWS SCT	AWS DMS	Same tools, reverse direction from PostgreSQL migrations
Any migration, non-technical team, one-time project	DBConvert	DBConvert	Single GUI tool, commercial cost, simpler workflow

14 Cross-Engine “Backup and Restore”: The pg_dump Workflow Beginner

One of the most common questions from SQL Server DBAs approaching a PostgreSQL migration is: can I just take a PostgreSQL backup and restore it to SQL Server? The answer is no, and understanding why makes the entire migration process clearer.

A SQL Server .bak file is a proprietary Microsoft binary format containing SQL Server internal page structures, transaction log records, and SQL Server-specific metadata. PostgreSQL has no ability to read or interpret it. In the other direction, PostgreSQL’s backup formats contain PostgreSQL-specific data structures, PL/pgSQL object definitions, and PostgreSQL type information that SQL Server has no ability to process. The two engines are completely different products with completely different storage architectures. Their backup files are no more interchangeable than a Word document and a Pages document.

What “backup and restore” actually means for a cross-engine migration is a three-step process: export, convert, and import. Each step has its own tools and its own failure points.

Step 1: Export from PostgreSQL Using pg_dump

pg_dump is PostgreSQL’s built-in export utility. It reads the database and produces a portable representation of its schema and data. For a cross-engine migration to SQL Server the most useful output format is either plain SQL (which produces readable CREATE TABLE and INSERT statements) or CSV per table (which produces raw data files that SQL Server can bulk load).

-- Export the full PostgreSQL database schema and data as plain SQL
-- This produces a .sql file containing CREATE TABLE, INSERT statements
-- The PostgreSQL syntax will NOT run directly on SQL Server
-- It is an intermediate step for the schema conversion tools

pg_dump   --host=your-rds-instance.us-east-1.rds.amazonaws.com   --port=5432   --username=postgres   --dbname=your_database   --schema-only \           -- schema only first (to review and convert)
  --no-owner \              -- skip ownership assignments
  --no-acl \                -- skip permission grants
  --file=schema_export.sql

-- Export data only as CSV per table (recommended for large tables)
-- CSV is engine-agnostic and can be loaded into SQL Server with BULK INSERT

psql   --host=your-rds-instance.us-east-1.rds.amazonaws.com   --username=postgres   --dbname=your_database   --command="\COPY orders TO 'orders.csv' WITH (FORMAT CSV, HEADER TRUE)"

psql   --host=your-rds-instance.us-east-1.rds.amazonaws.com   --username=postgres   --dbname=your_database   --command="\COPY customers TO 'customers.csv' WITH (FORMAT CSV, HEADER TRUE)"

-- Export all tables to CSV using a script loop
psql -h your-rds-instance.us-east-1.rds.amazonaws.com      -U postgres -d your_database -t      -c "SELECT tablename FROM pg_tables WHERE schemaname='public'" | while read table; do
    psql -h your-rds-instance.us-east-1.rds.amazonaws.com          -U postgres -d your_database          -c "\COPY $table TO '${table}.csv' WITH (FORMAT CSV, HEADER TRUE)"
  done

Step 2: Convert the Schema for SQL Server

The plain SQL schema export from pg_dump contains PostgreSQL DDL that SQL Server cannot run. This is the step where SSMA, SCT, or DBConvert does its work: reading the PostgreSQL schema (either by connecting directly to the source database or by reading the export file) and generating SQL Server-compatible CREATE TABLE scripts.

This step is also where you fix the FK naming conflicts described in Section 8. Before running the schema conversion tool, run the duplicate constraint name detection query against the PostgreSQL source and rename any duplicates. The conversion tool will then produce clean SQL Server DDL with no naming conflicts.

-- After schema conversion: run the generated SQL Server DDL
-- Example of what a converted CREATE TABLE looks like
-- Input (PostgreSQL):
-- CREATE TABLE orders (
--     order_id SERIAL PRIMARY KEY,
--     customer_id INTEGER NOT NULL,
--     order_date TIMESTAMPTZ NOT NULL DEFAULT NOW(),
--     total_amount NUMERIC(10,2),
--     is_shipped BOOLEAN DEFAULT FALSE
-- );

-- Output (SQL Server, after conversion):
CREATE TABLE dbo.orders (
    order_id      INT            IDENTITY(1,1) NOT NULL
                                 CONSTRAINT PK_orders PRIMARY KEY,
    customer_id   INT            NOT NULL,
    order_date    DATETIMEOFFSET NOT NULL DEFAULT SYSDATETIMEOFFSET(),
    total_amount  DECIMAL(10,2)  NULL,
    is_shipped    BIT            NOT NULL DEFAULT 0
);

-- Verify the schema created correctly
SELECT
    TABLE_NAME,
    COLUMN_NAME,
    DATA_TYPE,
    CHARACTER_MAXIMUM_LENGTH,
    IS_NULLABLE,
    COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'dbo'
ORDER BY TABLE_NAME, ORDINAL_POSITION;

Step 3: Load the Data into SQL Server

With the schema in place on SQL Server, load the CSV exports using BULK INSERT. This is the fastest method for large tables and is how DMS performs its full load phase internally.

-- BULK INSERT from CSV export files
-- Run after the schema is created on SQL Server

BULK INSERT dbo.customers
FROM 'C:\migration\customers.csv'
WITH (
    FORMAT           = 'CSV',
    FIRSTROW         = 2,          -- row 1 is the CSV header
    FIELDTERMINATOR  = ',',
    ROWTERMINATOR    = '
',
    TABLOCK,                       -- table lock for faster load
    BATCHSIZE        = 50000       -- commit every 50k rows
);

BULK INSERT dbo.orders
FROM 'C:\migration\orders.csv'
WITH (
    FORMAT           = 'CSV',
    FIRSTROW         = 2,
    FIELDTERMINATOR  = ',',
    ROWTERMINATOR    = '
',
    TABLOCK,
    BATCHSIZE        = 50000
);

-- Check row counts after load
SELECT
    OBJECT_SCHEMA_NAME(object_id)   AS SchemaName,
    OBJECT_NAME(object_id)          AS TableName,
    SUM(row_count)                  AS RowCount
FROM sys.dm_db_partition_stats
WHERE index_id IN (0, 1)
AND   OBJECTPROPERTY(object_id, 'IsUserTable') = 1
GROUP BY object_id
ORDER BY SUM(row_count) DESC;

The Complete Picture

The full cross-engine migration workflow from PostgreSQL on RDS to SQL Server is:

1. Fix FK naming conflicts in PostgreSQL before anything else
2. Export schema with pg_dump --schema-only
3. Export data per table as CSV files
4. Run SSMA, SCT, or DBConvert to convert the schema to SQL Server DDL
5. Create the schema on SQL Server
6. BULK INSERT the CSV files into SQL Server
7. Validate row counts and spot-check data accuracy
8. Update connection strings and cut over

AWS DMS automates steps 3 through 6 when you use it with SCT for the schema. DBConvert handles steps 3 through 6 in a single GUI workflow. SSMA handles the schema conversion and can also handle the data load. The underlying process is the same regardless of which tool you use.

References

• AWS Database Migration Service Overview
• AWS Schema Conversion Tool (SCT)
• Microsoft Docs: SQL Server Migration Assistant (SSMA) v10.5
• Microsoft Docs: Azure Database Migration Service Overview
• DBConvert: Database Migration and Sync Tools
• Redmond Magazine: Microsoft Releases SSMA v10.5 (February 2026)
• Microsoft Docs: sys.sp_cdc_enable_db (enabling CDC for DMS source)
• AWS: SQL Server to Aurora PostgreSQL Migration Playbook
• SQLYARD: Azure SQL Database Hyperscale Explained
• SQLYARD: Microsoft PostgreSQL Hub for Azure Developers
• SQLYARD: Azure Database for PostgreSQL Flexible Server Guide