Subscribe to RSS feed  Follow @jackvamvas - Twitter

*Use the Comments section for questions Links



Migrate SQL Server to Aurora PostgreSQL using AWS DMS best practices

01 September,2022 by Rambler

Migrating on-premises SQL Server to Aurora PostgreSQL using AWS Database Migration Services (DMS) can be complex. This is a list of best practices which I've  learnt over a number of migrations


--> SQL Server Agent Jobs . PostgreSQL doesn't offer the SQL Server Agent equivalent. If a task or process is dependent on SQL Server Agent it will need to be replaced. AWS Lambda supports executing code. 

-->SQL Server Security 

                a) Linked Servers - SQL Server uses a linked servers . The equivalent in PostrgreSQL is Foreign Data Wrappers

                 b) DB Schemas - By default PostgreSQL creates objects in the public schema. SQL Server by default grants permissions to the principal creating the schema. If migrating to PostgreSQL , you'll need to adjust the public schema permissions. An alternative is to use the PostgreSQL search_path  and change the default schema of the user.

--> Focus on differences between SQL Server & PostgreSQL including case sensitivity , index differences, replication differences, HA,stored procedures ,Query Lanaguage,Regular Expressions,Table Statements,Data Types,Security

The truth about SQL Server to PostgreSQL migration

--> PostgreSQL performance

           a)Triggers - Review Triggers & Nested Triggers in SQL Server. There  are benefits to triggers but also extensive complications when migrating to PostgreSQL. At a minimum you will need to disable triggers prior to migration - during a DMS task

            b)SQL Server TempDB - Aurora PostgreSQL temporary tables are 

-->Indexes,Triggers & Referential Integrity Constraints

All these database objects can cause functional & performance issues during a DMS task.   The type of problem is dependant on the type of task : Full Load or Full Load + CDC.

Full Load : Always turn off referential integrity constraints & Triggers 

                Recommend to drop Primary Key Indexes,Secondary Indexes . This will depend on the data sizes 

Full Load + CDC : Always turn off referential integrity constraints & Triggers for the Full Load section

                 Add the secondary indexes prior to the CDC stage

Author: Rambler (


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment on Migrate SQL Server to Aurora PostgreSQL using AWS DMS best practices