Database migration with the AWS Schema Conversion Tool

The AWS Schema Conversion Tool promises to take a lot of the pain out of migration so I’m giving it a try. So far, it has done what it says it will. Here’s how it works at a high level:

Create a new project
Connect to origin and destination servers
Select the database you would like to analyse
Drill down to make changes

Open the AWS SCT and create a new project

Prerequisites: Your new Amazon RDS or Aurora instance is deployed and the necessary drivers have been installed. This takes a few clicks from the AWS console.

Once you’ve got everything up and running pick your source and target database types. The tool currently supports a number of database sources and Aurora and Redshift as a destination.

Schema Conversion Tool
Image from AWS Documentation
  • You cannot use Redshift as a source if you are moving data into an Aurora database.

Connect to both the origin and destination servers

The origin will appear on the left and the destination will appear on the right with action items in the middle.

Schema Conversion Tool
Image from AWS Documentation

Select the database

Go to: Actions > Create Report

The drivers will then examine in detail all of the objects in that schema, including the schema itself, tables, views, procedures, functions, and packages.

It will convert as much as possible automatically and provide detailed information about items it couldn’t convert. The report can be saved as a .csv file or a .pdf file for review.

Schema Conversion Tool
Image from AWS Documentation
  • Green can be resolved right away.
  • Yellow need minor tweaks.
  • Red needs significant work to update.

We ran into trouble when converting tables with indexes.  SQL Server supports INCLUDE but as PostgreSQL does not it meant manually doing changes to make sure these columns were captured in the new index.

We found that the database engine that we were running on the Aurora side was out of sync with the Schema Conversion Tool and in some cases syntax that was not supported causes errors.

Drill down into each object to check where changes need to be made

Schema Conversion Tool
Image from AWS Documentation

When ready to commit, right click and select ‘apply to database’.

You can now go to your new Amazon RDS and refresh the database to pull over your work.

Migration of the data can also happen at this point if the necessary drivers have been installed or you can then use the AWS Database Migration Service.

And that’s it. The Schema Conversion Tool is incredibly easy to use and a massive time saver when it comes to migration, so we can spend more time upskilling users and less time tinkering with scripts and schemas.

Read more about the tool in the AWS documentation.

Photo by Mihis Alex from Pexels

Comments are closed, but trackbacks and pingbacks are open.