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, that can be modified and then executed using your favorite database tool to create new objects in the target database. The second option allows to modify the target database in real-time, using the GUI of the AWS Schema Conversion Tool.
NOTE: The AWS Schema Conversion Tool allows you to convert various database objects, but it cannot be used for actual data migration. 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.
First, and the most important thing about applying converted code directly using SCT to the target database is that the corresponding object in the target database is dropped 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:
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.
Also, you should remember that applying a database object means that all objects, that are included in it, are also applied to the target database.
Stay tuned to our blog on more tips and insights around migrating databases and applications to AWS.
Related AWS SCT Posts
- Migrating an Oracle to MySQL Database using the AWS Schema Conversion Tool
- AWS Schema Conversion Tool: Offline mode
- AWS Schema Conversion Tool: Connecting to Oracle Source Database
- Selecting target database type for migration using Assessment Report in AWS Schema Conversion Tool
- Troubleshooting database migration issues using AWS Schema Conversion Tool Assessment Report
- Converting Objects during Database Migration using AWS Schema Conversion Tool
- General Approach to Migration of Data Warehouses to Amazon Redshift
- Specific Features of Migrating Data Warehouses to Redshift
- First look at AWS Schema Conversion Tool with tighter DMS integration
To learn how the experts at DB Best can help you jumpstart your migration effort, contact us.