AWS Schema Conversion Tool

The part of a migration that is not the data

Moving a database from one engine to another involves two quite different problems, and conflating them is why these programmes are so often misjudged.

Moving the data. Rows from the source arrive in the target. This is tractable, tooling exists, and the effort is largely proportional to volume.

Moving everything else. Table definitions, views, indexes, constraints, stored procedures, functions, triggers. These are written for one engine's dialect and do not run on another.

The second problem is where migrations spend their time, and it is the one this tool addresses.

What converts and what does not

Structural objects convert reasonably well. A table is a table, and while data types differ between engines, the correspondences are mostly established.

Procedural code is where difficulty concentrates.

Every database has its own procedural dialect, and they differ in more than syntax. Error handling models are different. Behaviour around transactions varies. Cursor semantics differ. Built in functions exist in one and not another, or exist with different behaviour at the edges.

Some of this translates mechanically. A loop is a loop. Some does not, because the source code relies on behaviour that has no equivalent, and reproducing it requires understanding what the code was trying to achieve rather than what it says.

That distinction, between what can be translated and what has to be understood, is the substance of the problem.

The assessment report

The most useful output is arguably not the converted code but the report.

It lists what converted automatically, what did not, and gives an indication of the effort involved in the remainder.

The value is in timing. Migrations between engines are routinely estimated on the assumption that the data volume determines the work. Then the procedural code is encountered, and the estimate is discovered to have been wrong by a large factor.

Running an assessment first replaces that discovery with information. If a database has four hundred stored procedures and the report says eighty need manual attention, the programme can be scoped. If it says three hundred and eighty do, that changes the decision, possibly to not proceeding at all.

Knowing that before committing is worth considerably more than the automatic conversion itself.

Where the assessment leads

Assessment findings inform more than the estimate. They frequently change the approach.

A database whose logic sits mostly in the application, with the database holding tables and simple views, converts readily. A database where two decades of business rules live in stored procedures does not, and for such a system the sensible question may be whether to convert the procedures at all or to reimplement that logic elsewhere.

That is a different programme with a different shape, and it is better identified during assessment than halfway through conversion.

Testing what came out

Automatic conversion produces code that is valid for the target engine. Valid is not the same as equivalent.

Differences in numeric precision, in how nulls are treated by particular functions, in sort order for text, and in transaction behaviour all produce code that runs correctly and returns something subtly different.

These are found by testing against real data and comparing results with the source, which is work that cannot be compressed away. It is also work that a migration approach keeping the source available makes far easier, which is why data migration with ongoing replication and schema conversion are usually used together.

Who uses it

The tool is used by database administrators, migration teams and architects planning or executing moves between database engines, most often as part of moving to AWS and frequently when moving from a commercial engine to an open source one.

Points to consider

This is an AWS tool and the official documentation is the reference for supported source and target combinations, which determine whether it applies to a given migration at all.

The assessment is an estimate. It indicates effort based on what it can analyse, and the actual work depends on how complex the code is and how thoroughly it must be verified.

Converted code is a starting point for review rather than a finished result. Treating its output as complete without testing is how subtle differences reach production.

Schema conversion addresses the database. Application code containing SQL written for the source dialect is a separate problem that this does not solve, and it is frequently as large.

Getting started

The official documentation covers supported conversions, running an assessment and working through the results. Running an assessment against an existing database, before any commitment to migrate, is the single most informative step available and requires no decision to have been made first.