By using AWS re:Post, you agree to the Terms of Use

AWS DMS Issues | Duplicate Tables and SCT Conversion Issue


I am using AWS DMS to perform migration from on premise SQL Server to Aurora PostgreSQL.

1/ After performing the data migration task step, I am seeing same table name twice in PostgreSQL database. One table was created during schema conversion tool and 2nd table was created during data migration task. Is there any option in data migration task to prevent creation of new table,if it exists.

2/ I am also facing issues when converting the schema using AWS Schema Conversion Tool.It seems SCT is not supporting auto conversion of computed fields, temp tables and trigger objects to targeted Aurora PostgreSQL database. Does SCT automate part of the process, and not all of the process.

1 Answer
Accepted Answer

1.) When creating a DMS task, the "Target table preparation mode" controls the behavior of table creation. If you have pre-created your tables either with SCT or some other way, then you should select "Do nothing". "Truncate" is a good option if you want to empty out any existing tables, and "Drop tables on target" does just that. You generally don't want DMS to create your target tables as it only creates the basics e.g.) Table columns and a primary key. You don't get the extras like triggers, secondary indexes, etc.. If you are getting two tables, then there is obviously some difference in the name/schema. Irrespective of the settings chosen above, if DMS can't "find" the table it's looking for, it will create it. I would therefore assume that you have a mapping rule that is a bit off, and/or are missing a mapping rule. As a guess, going from SQL Server to PG, I would assume you're missing a transformation rule to change the camel case SQL tables to lower case PG tables.

2.) SCT supports the conversion of most objects including temp tables and trigger objects. The detailed list is stored in the release notes here. It supports generated columns for Sybase to PG, so I am all but certain computer fields are there for SQL Server as well.

answered 2 years ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions