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:
Open the AWS Schema Conversion Tool 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.
- 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.
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.
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 SCT and in some cases syntax that was not supported causes errors.
Drill down into each object to check where changes need to be made
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. 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 Lee Imho on Pexels