AWS Migration Tools Deep Dive

In the previous blog,”Motivations and Considerations for migration from MS SQL Server to AWS Aurora PostgreSQL”, we have mentioned that it is critical to have tools for a successful migration. Now let us do a deep dive on AWS tools for migrating MS SQL Server Databases to Aurora PostgreSQL.

AWS Schema Conversion Tool (AWS SCT)

This is a powerful tool for homogeneous and heterogeneous database migrations, and it will support migration from MS SQL Server to Aurora PostgreSQL. The tool will convert schemas and DB objects from source DB to Target DB. You can use the tool to identify and convert SQL queries in the application code also.

Let us start reviewing steps for converting schema and objects for the target Aurora PostgreSQL database.

1) Pre-conversion preparation

You will need the below preparation steps for successful conversion

• Specifying data type mappings (e.g., INT, INTEGER). This is required as data type conversions are not as expected from the AWS SCT tool. If you do not map, you may end up correcting the target schema script.
• Customizing assessment report settings based on how detailed you will need comments
• Conversion settings such as case sensitive option for source database object names

You will need to have a workaround for some of the limitations of AWS SCT.

For example, user-defined table types in the source schema are not appropriately recognized when used by procedures and functions in a different schema in the same database unless the Table Types are “fully qualified”. In this example, you can update the source SQL schema and then run the AWS SCT tool.

You will need to discover such specific limitations based on your source schema to create solutions for successful conversion.

2) Creating a Migration Assessment Report

Map source and target databases and generate a migration report. You will need to have access and connectivity to source and target databases for the tool to create an assessment report. You will need to deselect any items in the source database that will not need conversion.

3) Performing Schema Conversion

Apply converted schema on the target database. Ensure that all scripts were executed and are ready for validation of all converted schema and objects.

4) Compare source and target schema

We recommend comparing source and target schema using third-party tools such as DB Solo to resolve issues and recreate database schema by going through steps 2) and 3) until schemas match or differences are acceptable.

5) Post conversion activities

You will need to perform additional changes to converted objects manually to ensure database objects behave as expected. Let us review the below case to gain more insights.

Parameter lengths — Stored Procedure Parameters for CHAR and VARCHAR usually are not converted with their actual lengths filled in (e.g., SCT converts a SQL Server “CHAR(3)” Stored Procedure parameter to a PostgreSQL “CHAR” parameter) as the PostgreSQL ignores the length modifier on CHAR/VARCHAR parameters. If the length used to enforce a maximum length for the value is passed to the parameter, then this enforcement will now need to be done another way, either by adding an explicit length check to the stored procedure or by creating a DOMAIN TYPE of the appropriate type and length and using that type for the parameter instead. (Lengths _are_ enforced on DOMAIN type parameters.)

• For example: “CREATE DOMAIN languageid AS CHAR(3); “
• Then: “CREATE FUNCTION language_lookup(IN p_source_language languageid) …”

The number of issues you will need to fix manually will depend on your organizations’ coding patterns on source DB objects.

AWS Data Migration Service (DMS)

AWS DMS will help migrate data to AWS securely, and this tool will help you migrate data from MS SQL Server PostgreSQL. While the tool helps to migrate data, you will need to decide cut over strategy based on the volume of the data and possible downtime of the applications. This cutover strategy will help you to set the right migration settings in AWS DMS. Let me explain to you further.

If you have a small database and 8 to 12 hrs of possible downtime, then you can migrate and perform cut over within that time frame. Suppose the data size is relatively large and application availability requirements restrict your ability to get the required downtime. In that case, you will need to plan different strategies such as migrating historical data and then incremental data for cut over. Alternatively, you can plan for parallel update of databases for new transactions while you migrate historical data using a tool.

After you decide on the cut over and migration strategy, you will need to set up AWS DMS for migration from MS SQL Server to PostgreSQL. Assuming that you have deployed AWS DMS and connectivity is ensured to source and target databases, you will need to

a. Connect to source and target database

b. Create a migration task

This is where you will select a full load of existing data or the application of cached changes or ongoing replication

c. Monitor task

You will also need to follow certain practices to avoid issues, such as but not limited to:

• Disabling foreign key constraints
• Setting transformation rule in AWS DMS to force table and column names to lowercase as AWS SCT will convert table and column names to lower case in PostgreSQL DB
• Disabling/drop foreign keys and unique indexes

You will also need to follow best practices for best performance of AWS DMS, such as

• Adjusting the number of tables that can load in parallel
• Not allowing writes on target databases during migration
• Creating multiple tasks when you have sets of tables that don’t participate in common transaction
• Managing the number of tasks and parallel loading of numerous tables to reduce the burden on the target resource due to the database’s change logging

You will also need a plan for post-migration tasks such as creating the secondary index and non-primary key constraints.

We recommend you perform proof of concepts on the right candidates representing your application and database landscape and expose issues with the database objects in source databases.

This will allow you to effectively determine the right solution to prepare a run book for mass migration of databases.

Co-author: Lindsay Scarangello, Practice Lead, Technology Enablement