Heterogeneous Migration

Heterogeneous migration is a database migration in which the source and destination use different database engines. Moving an Oracle database to PostgreSQL or a Microsoft SQL Server database to MySQL are examples. By contrast, moving an on-premises PostgreSQL database to a managed PostgreSQL database is generally homogeneous migration because the database engine remains the same. Crossing a cloud, account, or network boundary does not by itself make a migration heterogeneous.

The distinction matters because database engines are not interchangeable implementations of SQL. They differ in data types, SQL syntax, stored procedure languages, indexing, identity generation, transaction behavior, and administrative features. Moving table rows is therefore only part of the work. Engineers must also determine how database objects and application assumptions will behave on the target engine.

A practical migration starts with discovery and compatibility assessment. Inventory tables, views, indexes, stored procedures, triggers, scheduled jobs, and database-dependent applications. Identify unsupported features and estimate the effort required to replace them. For example, an application using Oracle-specific SQL and PL/SQL packages will need more conversion work when moving to PostgreSQL than an application using mostly portable SQL and simple tables.

Schema conversion and data movement are separate responsibilities. Conversion tools can translate supported database objects and report items requiring manual changes, but automated conversion is not a guarantee of equivalent behavior. Application queries, database drivers, connection settings, and error-handling logic may also need modification. On AWS, AWS Database Migration Service supports data movement between supported source and target engines, while DMS Schema Conversion or the AWS Schema Conversion Tool can assist with supported schema conversions.

For a small database with an acceptable maintenance window, teams may stop application writes, export and transform the data, load the target, validate it, and redirect the application. For larger databases or tighter availability requirements, an initial full load can be followed by change data capture, where supported. This approach replicates ongoing source changes while preparation continues. It reduces the final interruption but does not automatically eliminate downtime or solve application compatibility problems.

Cutover requires an explicit plan. Teams typically pause writes, allow replication to catch up, validate the target, switch application connections, and monitor results. Validation should include row counts, representative values, business totals, and application workflows. Pay special attention to numeric precision, character encoding, time zones, null handling, and collation. Performance testing is also necessary because equivalent queries may produce different execution plans on the new engine.

The trade-off is potential long-term benefit against conversion effort and migration risk. Licensing or operational costs may improve, but temporary duplicate environments, testing, code changes, and specialist work add cost. Use encrypted connections, appropriately scoped permissions, and tested recovery procedures. A rollback plan must address writes accepted by the new database after cutover; pointing applications back to an outdated source can lose business transactions. For certification questions, recognize that different database engines indicate heterogeneous migration, and distinguish data migration from schema and application conversion.