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.
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.
Applying dependent objects
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.
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.
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
- Using AWS Schema Conversion Tool for Application Conversion
- Optimizing your Redshift Storage with AWS Schema Conversion Tool
- Extracting Data from Warehouses with Data Migration Agents in AWS Schema Conversion Tool
- Using AWS Schema Conversion Tool Extension Pack for OLTP databases
- Using AWS Schema Conversion Tool Extension Pack for Data Warehouses