Heterogeneous database migration: what AWS SCT automates, and the 30% it can't

There are two kinds of database migration, and they are not the same size of problem. A homogeneous move keeps the engine, SQL Server to SQL Server on Amazon RDS, say, and changes only where it runs. A heterogeneous move changes the engine itself: Oracle or SQL Server to Amazon Aurora PostgreSQL or MySQL. People choose it for good reasons, usually to escape proprietary licensing, but it carries work a homogeneous move never does, because the schema, the code, and sometimes the application all have to speak a new dialect.

The tool that carries most of that load is the AWS Schema Conversion Tool (SCT). Understanding precisely what it does, and what it deliberately leaves for you, is the difference between a scoped project and a surprise.

Start with the assessment report, not the conversion

Before converting anything, point SCT at the source and run its Migration Assessment Report. This is the single most useful artefact in a heterogeneous migration. It inventories every database object, classifies each as automatically convertible or needing manual action, and gives a defensible estimate of the human effort involved.

The headline is usually a figure like “70% of objects convert automatically.” That number is reassuring and slightly misleading: the automatic 70% is the easy 70%, tables, indexes, most views. The manual 30% is the procedural logic, and it is where nearly all the real effort sits. Read the report for the action items, not the percentage.

What converts cleanly

SCT handles the structural layer well. Tables, columns, primary keys, most indexes, simple views and constraints convert with little fuss, and SCT maps source data types to sensible target equivalents:

If a database were only tables and views, heterogeneous migration would be nearly push-button. Almost none are.

The 30%: where the hand-work lives

The objects SCT flags for manual conversion are the ones that encode behaviour rather than structure: stored procedures, functions, triggers, and packages. Converting Oracle PL/SQL to PostgreSQL PL/pgSQL, or T-SQL to its PostgreSQL equivalent, is a translation between languages, and some source constructs have no direct target:

• Packages (Oracle) have no PostgreSQL equivalent; they become schemas with grouped functions.
• Autonomous transactions (PRAGMA AUTONOMOUS_TRANSACTION) aren’t native; they need a redesign or a dblink workaround.
• Hierarchical queries (CONNECT BY) become recursive CTEs (WITH RECURSIVE).
• Proprietary helpers, NVL, SYSDATE, ROWNUM, DUAL, map to COALESCE, now(), LIMIT/row_number(), and nothing.
• Optimiser hints don’t carry; PostgreSQL plans differently and they must be re-thought, not translated.

Even a trivial procedure shows the shape of it:

CREATE OR REPLACE PROCEDURE close_order(
  p_id NUMBER) IS
BEGIN
  UPDATE orders
     SET status = 'CLOSED',
         updated_at = SYSDATE
   WHERE id = p_id;
  COMMIT;
END;

CREATE OR REPLACE PROCEDURE close_order(
  p_id integer)
LANGUAGE plpgsql AS $$
BEGIN
  UPDATE orders
     SET status = 'CLOSED',
         updated_at = now()
   WHERE id = p_id;
END;
$$;

SCT does most of this automatically; the value is in catching the cases it can’t, transaction control, data types, proprietary functions, and testing that behaviour is identical.

SCT converts what it can and inserts a commented action item where it can’t, with a severity. Your migration plan is, in effect, that list of action items worked through and tested.

Babelfish: the shortcut for SQL Server to Aurora

If the source is SQL Server and the target is Aurora PostgreSQL, there is a meaningful shortcut. Babelfish for Aurora PostgreSQL gives Aurora a T-SQL-compatible endpoint that speaks SQL Server’s TDS wire protocol on port 1433. Your application keeps issuing T-SQL over its existing SQL Server driver, and Aurora understands it:

# app keeps its SQL Server-style connection — Babelfish answers on 1433 (TDS)
Server=my-aurora-babelfish.cluster-xxxx.ap-southeast-2.rds.amazonaws.com,1433;
Database=app;Encrypt=true;

For SQL Server estates with heavy T-SQL, Babelfish can collapse the application-rewrite effort dramatically. It is not total, T-SQL coverage is broad but not complete, so run the Babelfish Compass assessment to see what falls outside it. But where it fits, it changes the economics of the move.

Data-type traps that surface at runtime

The conversions that pass SCT cleanly but bite later are almost always type semantics. Oracle’s DATE carries a time; PostgreSQL’s date does not. Empty string equals NULL in Oracle but not in PostgreSQL. Implicit numeric-to-string conversions that Oracle tolerates, PostgreSQL rejects. None of these is hard to fix; all of them are easy to miss until a query returns the wrong answer rather than an error.

Test the converted code as what it is: application logic

The single biggest mistake in heterogeneous migration is treating converted stored procedures as “migrated” once they compile. A procedure that compiles can still behave differently. This code is business logic, and it deserves the same testing as application code: a harness that runs representative inputs against source and target and asserts the outputs match. Functional equivalence, proven, not assumed, is the bar.

Budget for the 30%, honestly

Strip it back and heterogeneous migration is one structural job that SCT largely automates, plus one software job, converting and testing procedural logic, that it cannot. The failure pattern is always the same: a plan sized off the “70% automatic” headline that meets the procedural 30% halfway through. Read the assessment report for the action items, scope the manual conversion as real software work, consider Babelfish if the source is SQL Server, and test the converted logic for behaviour, not just compilation.