AWS Migration & Transfer
Schema Conversion Tool
Automatically convert database schema when moving between different database engines
The AWS Schema Conversion Tool (SCT) is a desktop application that analyzes your source database schema and automatically converts it to be compatible with a different target database engine - for example converting an Oracle schema to Aurora PostgreSQL or a SQL Server schema to MySQL. It identifies objects that cannot be converted automatically and provides an assessment report showing migration complexity. SCT is essential for heterogeneous migrations (different source and target engines) and is always used alongside DMS in those scenarios.
SCT vs DMS: Complementary Tools
SCT and DMS solve different problems and are used together for heterogeneous migrations. Understanding the boundary between them is a common interview question.
| SCT | DMS | |
|---|---|---|
| Purpose | Convert schema and database code (DDL) | Migrate data (DML - rows) |
| What it converts | Tables, indexes, views, stored procedures, functions, triggers, sequences | Rows in tables (INSERT equivalent) |
| When it runs | Before the migration, one-time conversion | During migration, continuously for CDC |
| Deployment model | Desktop application on your laptop/workstation | Cloud service - replication instance in AWS |
| Cost | Free to download and use | Pay for replication instance + storage |
| Output | SQL DDL scripts for the target engine | Data in target tables |
The typical workflow is: run SCT to convert schema, apply the converted DDL to the empty target database, then use DMS to migrate the actual data. SCT must run first because DMS needs the target tables to exist before it can load data into them.
Supported Source and Target Engines
SCT supports a range of engine conversions. The conversion complexity (and manual remediation effort) depends heavily on how many proprietary features the source schema uses.
| Source Engine | Supported Targets |
|---|---|
| Oracle | Aurora PostgreSQL, Aurora MySQL, PostgreSQL, MySQL, MariaDB, Redshift |
| SQL Server | Aurora PostgreSQL, Aurora MySQL, PostgreSQL, MySQL, MariaDB, Redshift |
| MySQL | Aurora PostgreSQL, PostgreSQL, Redshift |
| PostgreSQL | Aurora MySQL, MySQL |
| Teradata | Redshift, Redshift Serverless |
| SAP ASE (Sybase) | Aurora PostgreSQL, Aurora MySQL, PostgreSQL, MySQL |
Teradata to Redshift conversions use a separate SCT component called the DW Extraction Agent. It works differently from the OLTP conversion flow and requires additional setup. Do not assume the standard SCT workflow applies to data warehouse migrations.
SCT Assessment Report and Action Items
Before converting anything, run an SCT assessment report against your source database. This is the most important output SCT produces - it tells you how much work the migration will require.
| Report Category | What It Means | Action Required |
|---|---|---|
| Automatically converted | SCT converts these without manual changes | Review and apply |
| Converted with issues (simple) | Minor syntax differences - SCT converts but flags for review | Review generated code for correctness |
| Converted with issues (complex) | Feature gaps between engines - SCT attempts conversion but result may be incorrect | Manual rewrite required |
| Cannot convert | Proprietary feature with no equivalent in target (e.g., Oracle Spatial, SQL Server CLR) | Redesign the feature or eliminate it |
SCT expresses complexity as a percentage of objects it can convert automatically. A score above 80% means a straightforward migration. Below 50% means significant manual effort - factor this into your migration timeline and resourcing.
Run the SCT assessment on a development copy of your schema first. You can connect SCT directly to the source database or point it at an exported schema script. Connecting directly gives the most accurate results because SCT can analyze stored procedure dependencies.
Common Conversion Challenges by Source Engine
Each source engine has common patterns that do not map cleanly to AWS-native databases. Knowing these is critical for realistic migration planning.
| Source | Common Challenge | Typical Resolution |
|---|---|---|
| Oracle | Sequences and NEXTVAL calls in triggers | PostgreSQL uses SERIAL or IDENTITY columns - SCT handles simple cases |
| Oracle | PL/SQL stored procedures using BULK COLLECT / FORALL | Rewrite as PL/pgSQL with arrays - SCT cannot fully automate |
| Oracle | Oracle-specific data types (RAW, NCLOB, BFILE) | Map to BYTEA, TEXT, or external S3 references manually |
| SQL Server | T-SQL specific functions (CHARINDEX, ISNULL, GETDATE) | SCT maps most to PostgreSQL equivalents (POSITION, COALESCE, NOW()) |
| SQL Server | CLR (Common Language Runtime) stored procedures | No equivalent - must rewrite in application code or PL/pgSQL |
| SQL Server | Linked servers | Use Federated Query (Aurora), dblink (PostgreSQL), or application-level joins |
Oracle packages (groups of stored procedures and functions) are a major source of conversion complexity. Oracle packages have no direct equivalent in PostgreSQL - SCT emulates them using schemas and function naming conventions, but complex packages with global state often require significant manual rewriting.
SCT End-to-End Workflow
The SCT workflow from download to schema applied on the target database follows these steps.
| Step | Action | Output |
|---|---|---|
| 1. Download SCT | Install SCT desktop app on your workstation (Mac, Windows, Linux) | SCT application ready |
| 2. Create project | Define source connection and target engine type | SCT project file |
| 3. Connect to source | SCT connects directly to source DB and loads schema | Schema tree in SCT UI |
| 4. Run assessment | File > Create Report > Database Migration Assessment Report | HTML/PDF report with action items |
| 5. Convert schema | Right-click schema > Convert Schema | Converted DDL in SCT project |
| 6. Review action items | SCT flags issues in red/yellow - review and fix each one | Corrected SQL scripts |
| 7. Apply to target | Connect to target DB, right-click > Apply to Database | Schema created on target |
| 8. Run DMS | Now run DMS full-load + CDC to populate data | Data migration begins |
SCT saves its work in a project file (.sct). Keep this file in version control - it contains all your manual schema edits and conversion decisions. If you need to re-run the conversion (e.g., after source schema changes), you can re-apply only the changed objects rather than redoing everything.
Interview Focus Points
- 1What is the difference between SCT and DMS and how do they work together in a heterogeneous migration?
- 2Walk me through the steps to migrate from Oracle to Aurora PostgreSQL using SCT and DMS.
- 3What does the SCT assessment report tell you and how do you interpret the conversion complexity score?
- 4What Oracle features commonly fail to convert automatically in SCT and how do you handle them?
- 5How does SCT handle Oracle packages when converting to PostgreSQL?
- 6What is the difference between a homogeneous migration and a heterogeneous migration and when do you need SCT?
- 7Can SCT be used for data warehouse migrations? What is different about the Teradata to Redshift workflow?
- 8How would you estimate the total migration effort for a 500-table Oracle database using SCT output?