AWS Schema Conversion Tool: Applying Converted Code to Target Database

This continues our video blog series on the AWS Schema Conversion Tool (SCT). After connecting to the source database and troubleshooting the database migration issues it’s time to apply converted code to the target database. The AWS Schema Conversion Tool provides two ways of applying changes to the target database. The first option allows you to generate the SQL-script. You can modify this script if needed and then execute it using your favorite database tool. The second option allows to modify the target database in real-time, using the GUI of the AWS Schema Conversion Tool.

In addition, SCT drops and recreates existing objects on the target, so any updates to tables will result in data loss. For migrating the data, you will want to do it after you have completed your schema updates using the AWS Database Migration Service or Database Compare Suite from DB Best.

In the following video, we’ll show you how to use both these methods in the AWS Schema Conversion Tool, and talk about fixing issues which can occur during creation of new objects in the target database.

Need help getting started? Check out our Jumpstart for SCT offer!

Observations

First, and the most important thing about applying converted code directly to the target database is that SCT drops the corresponding object in the target database on the very first iteration. So, if you have some valuable data in the target database, you’ll have to save it before applying the newly generated code. You can use the CREATE TABLE AS SELECT command to create a copy of the table with data. Depending on the changes to your table definition, you can use the INSERT INTO table command to copy the data back.

The second observation is that the objects with syntax or dependency errors cannot be applied to the target database. As the video pointed out, you can use the newest SCT log file to determine the error and then fix it in SCT. Keep in mind that any of the selected objects after the error can fail if they were depended on the object with the error.

The video also pointed out that some errors can occur when you have objects that are dependent on each other. Consider the following example:

1
2
3

CREATE VIEW A AS SELECT * FROM MyTable;
CREATE VIEW B AS SELECT * FROM C;
CREATE VIEW C AS SELECT col1, col2 FROM A;

For this to commit, you would need to create view A first, view C second and view B last. The problem is that SCT creates object groups in alphabetical order. Rather than using the generate a SQL file approach and updating the order of the items, you can use SCT to run the apply command against the View node 2 times. On the first run, SCT creates view A and C. On the second run, SCT creates view B.

Remember that applying a database object means that SCT will apply all objects included in it to the target database.

Stay tuned to our blog on more tips and insights around migrating databases and applications to AWS.

Blog: AWS Schema Conversion Tool: Applying Converted Code to Target Database

Observations

Related AWS SCT Posts

Related posts

Getting started