The AWS Schema Conversion Tool (AWS SCT) makes your database migrations more predictable. It does this by automatically converting the source database schema and most of the database code objects to a format compatible with the target database.

We’re excited to announce a new feature in AWS SCT to enable schema comparison and synchronization for homogenous database migrations (that is, like for like – for example, Oracle to Oracle). The schema compare feature makes it easier for you to compare and migrate changes to the database schema from the source database to the target database.

Schema compare works with Oracle, Microsoft SQL Server, PostgreSQL, and MySQL. Schema compare is compatible with all variants of these engines: on premises, on Amazon EC2, on Amazon RDS, and on Amazon Aurora.

Why do you need schema compare?

You will need schema compare for these scenarios:

• During your migration to AWS, the schema compare feature helps you keep your on-premises production database and target AWS database in sync.
• You can keep your database schema in sync in all your environments: development, testing, performance testing, and production.
• Schema compare helps when you upgrade application versions by propagating metadata changes.
• Schema compare helps team development in propagating local schema changes to shared databases.
• Schema compare enables you to extract schema changes as a script to help users create migration and rollback scripts. It also enables saving the scripts under a source control system.

Supported databases

You can use schema compare for these database conversions:

• Oracle to Oracle, versions 12.1.0.2.0, 11.1.0.7.0, 11.2.0.1.0
• SQL Server to SQL Server, versions 2016, 2014, 2012, 2008 R2, 2008
• PostgreSQL to PostgreSQL and Aurora with PostgreSQL compatibility, versions 9.6, 9.5.9, 9.5.4
• MySQL to MySQL, versions 5.6.36, 5.7.17, 5.5

The schema compare feature works only when your target database version is the same or higher than the source database version. For example, if your source is Oracle and its version is 11.2.0.4, this feature works only if the target Oracle database is version 11.2.0.4 or higher.

How to use this feature in SCT

Learn how to use this feature in the following sections.

Comparing schema settings

In AWS SCT, you can find the Compare Schema option on the Current Project Settings page. You specify settings for the schema comparison on the Compare Schema tab of the Project Settings page.

To compare two schemas

To compare two schemas, follow these steps:

1. Open an existing AWS SCT project, or create a project and connect to the source and target endpoints.
2. Choose the schema that you want to compare.
3. Open the context (right-click) menu and choose Compare Schema.

AWS SCT indicates the objects that are different between the two schemas by adding a black circle to the object’s icon.

Applying changes to the target

You can apply the results of the schema comparison to a single object, to a single category of objects, or to the entire schema. To do so, in AWS SCT, choose the box next to the category, object, or schema that you want to apply the results to. Select Apply to Target to apply your changes from the source database to the target database.

To indicate successful script creation on the database, the icons of the affected object turn black.

To indicate objects with errors, a red exclamation mark appears on the object items.

Limitations of the feature

Full schema replication support is only available for homogeneous migrations. The ordering of the data and schema isn’t strictly guaranteed, especially when the same object is modified multiple times. Also, schema migration doesn’t migrate users, roles, and so on.

Summary

AWS SCT can help you compare and migrate your schema changes from your source database to your target database while keeping your databases up and running. In this post, we described how you can take advantage of this feature to help with your data migrations to AWS.

If you want to see specific features included in AWS SCT or AWS Database Migration Service (AWS DMS), drop us a note in the comments. For more information about AWS SCT and AWS DMS, see AWS Database Migration Service.

About the Authors

Venu Reddy is a software development manager for Database Migration Service at Amazon Web Services. He works with our customers to provide guidance and technical assistance on database projects, helping them improve the value of their solutions when using AWS.