AWS DMS Premigration Assessment and unsupported data types

Question: Quite often , after completing an AWS Database Migration Service (DMS )database migration task - I discover columns or tables missing from the target database. There can be many reasons. One of the typical reasons could be that there are data types which are not supported by DMS outright or data types whoich are only partially supported and require a change in the data type. 

Is there a method within the DMS allowing a review of columns or data types BEFORE the conversion & migration process ?

Answer:  The premigration checkout  is a critical steps in assessing the complexity of a potential migration. Particuarly if it is replatforming of database engines such as SQL Server to PostgreSQL - where there can be incompatibilities causing data integrity and application consistency issues.

DMS offers a premigration assessment process - covering :

                                  - Unsupported data types

                                  - Source table with LOBs but without primary keys or unique constraints

                                  - Large objects (LOBs) are used but target LOB columns are not nullable

You can either use the AWS CLI or the Interface to create these reports . 

Enabling and working with premigration assessments for a task

I won't go into how to set these premigration assessments as AWS has excellent documentation . But these are some notes to consider

1) As part of the setup you have a number of report options. I usually select all 3 listed above , as  all of these options can cause considerable issues. There is also the option to go back to the application owner to review some of there columns & data types

2)The report is stored in an s3 bucket . The IAM role used to interact with the s3 bucket will need permissions

3) When the Premigration assessment completes - separate json files are available in the s3 bucket. You can download the json files and read the reports in a json cpompatible viewer. 

The output is useful - and offers enough information investigate further 

Sample  example 1 : here is an example of the summary report output 

{

•   "version": "0.0.1",
•   "overall-test-result": "failed",
•   "summary": {
  •     "passed": [
    •       {
      •         "test-name": "full-lob-not-nullable-at-target",
      •         "test-result": "passed",
      •         "table-results-summary": {
        •           "passed": 20,
        •           "failed": 0,
        •           "warning": 0
                }
            }
        ],
  •     "failed": [
    •       {
      •         "test-name": "table-with-lob-but-without-primary-key-or-unique-constraint",
      •         "test-result": "failed",
      •         "table-results-summary": {
        •           "passed": 18,
        •           "failed": 2,
        •           "warning": 0
                }
            }
        ],
  •     "error": [ ],
  •     "warning": [
    •       {
      •         "test-name": "unsupported-data-types-in-source",
      •         "test-result": "warning",
      •         "table-results-summary": {
        •           "passed": 17,
        •           "warning": 3,
        •           "failed": 0
                }
            }
        ],
  •     "cancelled": [ ]
    }

}

Sample output 2 : unsupported-data-types-in-source

{

•   "test-name": "unsupported-data-types-in-source",
•   "test-result": "warning",
•   "table-results-summary": {
  •     "passed": 0,
  •     "warning": 3,
  •     "failed": 0
    },
• • warning": [
    •       {
      •         "schema-name": "production",
      •         "table-name": "stock_pics_image",
      •         "table-result": "warning",
      •         "message": "1 columns have fully supported datatypes, 1 have partially supported datatypes, and 0 have unsupported datatypes",
      •         "column-data": {
        •           "partially-supported": {
          •             "image": [
            •               "image_data"
                        ]
                    }
                }
            },
    •       {
      •         "schema-name": "production",
      •         "table-name": "stock_pics_text",
      •         "table-result": "warning",
      •         "message": "1 columns have fully supported datatypes, 1 have partially supported datatypes, and 0 have unsupported datatypes",
      •         "column-data": {
        •           "partially-supported": {
          •             "text": [
            •               "text_data"
                        ]
                    }
                }
            },
    •       {
      •         "schema-name": "production",
      •         "table-name": "stock_pics_ntext",
      •         "table-result": "warning",
      •         "message": "1 columns have fully supported datatypes, 1 have partially supported datatypes, and 0 have unsupported datatypes",
      •         "column-data": {
        •           "partially-supported": {
          •             "ntext": [
            •               "ntext_data"
                        ]
                    }
                }
            }
        ],
  •     "failed": [ ]
    }