Migrate SQL Server to Aurora PostgreSQL using AWS DMS best practices

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