Cloud concept · Updated

Heterogeneous Migration - Heterogeneous migration moves a database from one database engine to a different engine, requiring compatible data structures and application behavior on the destination.

In short

Heterogeneous migration changes the database technology as well as moving the data, such as migrating from Oracle Database to PostgreSQL. It can support licensing changes, modernization, and adoption of managed cloud databases, but requires assessment of schema compatibility, application changes, and operational risk.

Cloud Concepts Hub →

6 key aspects of Heterogeneous Migration

Heterogeneous migration combines an engine change with compatibility assessment, schema conversion, controlled data movement, validation, and a planned cutover.

01
Different source and target engines

The defining characteristic is a change in database engine, such as SQL Server to PostgreSQL. A change in hosting location alone does not establish heterogeneous migration.

02
Schema and code conversion

Tables, data types, stored procedures, and other database objects must be assessed for target compatibility. Automated tools can assist, but unsupported objects and engine-specific application code often require manual changes.

03
Controlled data transfer

Data can be moved during a maintenance window or through an initial load followed by change data capture where supported. The choice depends on database size, source capabilities, and acceptable interruption.

04
Behavioral and performance validation

Successful loading does not prove application correctness. Test business workflows, transaction behavior, data accuracy, and query performance on the destination.

05
Planned cutover and recovery

Define when writes stop, how replication catch-up is confirmed, and when applications switch endpoints. Recovery planning must account for new writes on the target rather than assuming the original source remains current.

06
Cost and operational trade-offs

A new engine may reduce licensing dependence or support managed operations, but conversion and testing introduce costs. Include temporary parallel infrastructure and post-migration tuning in the estimate.

Cloud services for Heterogeneous Migration

AWS DMS can transfer data from a supported source engine into a supported target, such as Amazon RDS for PostgreSQL or Amazon Aurora PostgreSQL-Compatible Edition. Schema conversion capabilities help prepare supported objects, while engineers resolve unsupported features and update application code. Where supported, ongoing replication keeps the target close to the source until cutover. RDS or Aurora then provides the destination database environment. These services support different parts of the migration; they do not remove the need for compatibility testing, data validation, performance tuning, or a recovery plan.

Amazon RDSAmazon RDS

Amazon RDS provides managed relational database engines that can serve as migration destinations. Migrating Oracle to RDS for PostgreSQL is heterogeneous, while migrating Oracle to RDS for Oracle is generally homogeneous.

Amazon AuroraAmazon Aurora

Amazon Aurora provides MySQL-compatible and PostgreSQL-compatible relational databases that can be targets for heterogeneous migrations. Moving from Oracle or SQL Server to Aurora requires assessment and conversion for the selected compatibility edition.

Heterogeneous Migration exam questions

Real practice exam questions on Heterogeneous Migration, with the correct answer and its explanation. Answer them with every option in the exam simulator.

CLF-C02

A company plans to move an on-premises Microsoft SQL Server database to Amazon RDS for PostgreSQL to reduce commercial database licensing costs. The application uses SQL Server-specific stored procedures, and the source database must remain available during most of the migration. Which approach meets these requirements?

Show answerCORRECT ANSWER

Use AWS SCT to assess and convert the schema, address incompatible code, and use AWS DMS to load data and replicate ongoing changes before cutover.

This migration changes database engines, so schema and stored procedure compatibility must be addressed. AWS Schema Conversion Tool (AWS SCT) assists with assessment and conversion, while AWS Database Migration Service (AWS DMS) can load data and replicate ongoing changes to reduce downtime. Converted code and application behavior should be tested before cutover.

DEA-C01

A financial services company is migrating a Microsoft SQL Server database to Amazon Aurora PostgreSQL-Compatible Edition. A data engineer has completed schema conversion and resolved all manual conversion items in a desktop conversion project, including changes to views and stored procedures.

Company policy prohibits the engineer's workstation from executing DDL against the destination database. All schema changes must instead be reviewed in version control and deployed by an existing release pipeline that executes PostgreSQL SQL scripts. The pipeline must deploy the converted tables, views, and stored procedures before data migration begins.

How should the engineer prepare the deployment artifact?

Show answerCORRECT ANSWER

Save the converted target schema from AWS SCT as SQL scripts, submit the scripts for review in version control, and have the release pipeline execute the approved scripts.

AWS SCT can save the converted target schema as SQL scripts without applying it directly to the destination. The scripts can be reviewed, versioned, and executed by the authorized release pipeline to create the converted database objects.

SAP-C03

A software company plans to modernize 40 on-premises Microsoft SQL Server databases that support different products. The databases contain T-SQL stored procedures, triggers, views, and user-defined functions. The company must eliminate SQL Server licensing and is evaluating Amazon Aurora PostgreSQL-Compatible Edition and Amazon Aurora MySQL-Compatible Edition.

Before funding implementation, the company needs a per-database comparison of the two target engines that identifies objects that can be converted automatically and objects that require developer intervention. The comparison must account for database-resident business logic, not just table structures. Security permits metadata access from a controlled engineering workstation but prohibits uploading source schemas or code to a managed assessment service. Production data must not be copied during this phase.

Which approach meets these requirements with the LEAST manual effort?

Show answerCORRECT ANSWER

Install AWS SCT on the controlled workstation. Create conversion projects for each database and candidate target engine, generate database migration assessment reports, and compare automatic conversion coverage and action items requiring manual changes. Use those findings to estimate remediation effort before selecting targets.

AWS SCT is a downloadable tool that assesses heterogeneous schema and database-code conversion. Its assessment reports identify supported conversions and action items for objects that cannot be converted automatically. Evaluating each candidate target provides the required comparison without copying production rows or submitting source code to a managed assessment service. Converted business logic must still be validated during implementation.

Practice Heterogeneous Migration in a full exam

Questions on Heterogeneous Migration in each practice exam, with every answer option and an explanation for each:

Start a free practice exam

What is Heterogeneous Migration

2 min read

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.

Learn Heterogeneous Migration in lessons

7 short lessons on Heterogeneous Migration in 4 certification courses.

and 1 more lesson

Open the lessons