Heterogeneous Database Migration using AWS SCT

In this post we will see how we can migrate an On-premise Oracle database to Mysql RDS using AWS SCT and AWS DMS

Before we proceed with the migration, we need to know what is AWS SCT

For more posts on AWS RDS please check the INDEX page or visit our YouTube channel

What is AWS SCT?

You can use the AWS Schema Conversion Tool (AWS SCT) to convert your existing database schema from one database engine to another. Your converted schema is suitable for an Amazon Relational Database Service (Amazon RDS) MySQL, MariaDB, Oracle, SQL Server, PostgreSQL DB, an Amazon Aurora DB cluster, or an Amazon Redshift cluster. The converted schema can also be used with a database on an Amazon EC2 instance or stored as data on an Amazon S3 bucket.

AWS SCT provides a project-based user interface to automatically convert the database schema of your source database into a format compatible with your target Amazon RDS instance. If schema from your source database can’t be converted automatically, AWS SCT provides guidance on how you can create equivalent schema in your target Amazon RDS database.

AWS SCT is supported only in 64 bit OS Version

How to install the Schema Conversion Tool

The below link will route you to the download page and will guide you on how to install AWS SCT :

https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Installing.html

Install the Java Database Connectivity (JDBC) drivers for your source and target database engines. :

https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Installing.html#CHAP_Installing.JDBCDrivers

For the AWS SCT to work correctly, you must install the JDBC drivers for your source and target database engines.

To verify the AWS SCT MSI file on WIndows, unzip the downloaded file, right-click the MSI file and select Properties and navigate to Digital Signatures tab and verify that the digital signature is from Amazon Services LLC.

Install the SCT MSI file after verification and open it after installation.

Follow the below steps after successful installation of SCT Tool

1. Update the Driver Locations

Click on settings –> Global Settings

If you donot set the drivers locations globally, applications asks you for the location of the drivers whenever you connect

Choose Drivers

Choose the Driver (Oracle,Mysql in my example) which you need to select from the listed and browse and select the files from the path where you downloaded the JDBC Drivers

Click OK

2. Set the AWS Credentials with which your AWS SCT can access your AWS Console

From Global Settings go to AWS Service Profiles and provide the details as highlighted below

You need to specify your S3 bucket only if you are using a feature that connects to your S3 bucket
Test the connections and verify whether it is passing the test or not

Click OK

For converting your database from Oracle, the Oracle schemas needs to be granted the below privileges :

CONNECT
SELECT_CATALOG_ROLE
SELECT ANY DICTIONARY
SELECT on SYS.USER$ TO <sct_user>

Create Project for migration

To migrate data from a source to a target, AWS SCT loads all metadata from source and target databases into a tree structure. This structure appears in AWS SCT as the tree view in the main project window.

Some databases can have a large number of objects in the tree structure. You can use tree filters in AWS SCT to search for objects in the source and target tree structures. When you use a tree filter, you don’t change the objects that are converted when you convert your database. The filter only changes what you see in the tree.

Steps:

1. Click on File–>New Project

2. Provide the Project Name, Location of the project, Select OLTP,OLAP or NoSQL Database as per your requirement

3. Source Engine –> Oracle

4. Target ENgine –> Amazon RDS for MySQL

5. Once you click OK, you will find the highlighted options in your tab

6. Click on Connect to Oracle to configure the connectivity details of your Source Database and provide the details as per your requirements, store the password of your user and test the connection. Use SSL in your project environment for Secure connection between SCT and your Database

7. Once the connection test is successful, click OK

8. You can see the Tree Structure for your Source Database as mentioned above, once you connect to your Database

9. Similarly connect to your target database clicking on Connect to Amazon RDS for MySQL after which you would get the tree for your target database too

10. Now we need to filter the tree settings

11. From Global Settings traverse to Tree View, select the desired vendor, and then you can Hide empty Schemas, Hide System Schemas

12. If you are planning to filter out locally for just a single database, you can filter by clicking on the Funnel near your connection and provide the schema which you need

13. If there are changes in the source database after your successful connection, you can refresh the Schemas tab to make changes effective

Database Migration Assessment Report

The database migration assessment report summarizes all of the action items for schema that can’t be converted automatically to the engine of your target Amazon RDS DB instance. The report also includes estimates of the amount of effort that it will take to write the equivalent code for your target DB instance

Steps to create Database Migration Assessment Report:

1. To create the report you need to right click on the concerned schema and click on Create Report

2. The report would be something like below where it will show which all objects it can convert and which needs manual intervention for conversion.

In our example, being a demo environment,our checks passed for conversion of all objects

The colour ranges depict whether any actions need to be taken or not.

The report can be saved to pdf for further clarifications

Convert your Schema

Please follow the below steps to convert your schema :

1. In source, right click on the concerned schema and click on Convert Schema

2. Once the schemas have been converted, the same can be seen in the target database places in the right corner of your AWS SCT Tool

3. This doesnt mean that your schema has been migrated to your RDS instance. This has just been converted to be compatible with AWS RDS for MySQL

4. To make changes effective, you need to apply this to your database

5. This will just create the database and table structure in your RDS instance. Now you need to import the data using DMS. From the Mapping tab, you can check the status of your Apply to Database work

You can also check for any converted objects in the Related Converted objects , Creation date

6. To migrate using DMS, please refer to our blog for Full Load Migration

7. After successful DMS migration,you can check the datas to be present in your AWS RDS for MySQL. Also you can check the status of your DMS migration fro your SCT Tool