AWS Schema Conversion Tool

(1)

AWS Schema Conversion Tool

User Guide

Version 1.0

(2)

AWS Schema Conversion Tool: User Guide

Amazon's trademarks and trade dress may not be used in connection with any product or service that is not Amazon's, in any manner that is likely to cause confusion among customers, or in any manner that disparages or discredits Amazon. All other trademarks not owned by Amazon are the property of their respective owners, who may or may not be aﬃliated with, connected to, or sponsored by Amazon.

(3)

What is the AWS Schema Conversion Tool?

You can use the AWS Schema Conversion Tool (AWS SCT) to convert your existing database schema from one database engine to another. You can convert relational OLTP schema, or data warehouse schema.

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 supports several industry standards, including Federal Information Processing Standards (FIPS), for connections to an Amazon S3 bucket or another AWS resource. AWS SCT is also compliant with Federal Risk and Authorization Management Program (FedRAMP). For details about AWS and compliance eﬀorts, see AWS services in scope by compliance program.

AWS SCT supports the following OLTP conversions.

Source database Target database

IBM Db2 LUW (versions 9.1, 9.5, 9.7, 10.5, 11.1,

and 11.5) Amazon Aurora MySQL-Compatible Edition

(Aurora MySQL), Amazon Aurora PostgreSQL- Compatible Edition (Aurora PostgreSQL), MariaDB 10.5, MySQL, PostgreSQL

For more information, see Using IBM Db2 LUW as a source for AWS SCT (p. 48).

Microsoft Azure SQL Database Aurora MySQL, Aurora PostgreSQL, MySQL, PostgreSQL

Microsoft SQL Server (version 2008 R2 and later) Aurora MySQL, Aurora PostgreSQL, Babelﬁsh for Aurora PostgreSQL (only for assessment reports), MariaDB 10.5, Microsoft SQL Server, MySQL, PostgreSQL

For more information, see Using Microsoft SQL Server as a source for AWS SCT (p. 68).

MySQL (version 5.5 and later) Aurora PostgreSQL, MySQL, PostgreSQL For more information, see Using MySQL as a source for AWS SCT (p. 52)

You can migrate schema and data from MySQL to an Aurora MySQL DB cluster without using AWS SCT. For more information, see Migrating data to an Amazon Aurora DB cluster.

Oracle (version 10.2 and later) Aurora MySQL, Aurora PostgreSQL, MariaDB 10.5, MySQL, Oracle, PostgreSQL

(9)

For more information, see Using Oracle Database as a source for AWS SCT (p. 54).

PostgreSQL (version 9.1 and later) Aurora MySQL, Aurora PostgreSQL, MySQL, PostgreSQL

For more information, see Using PostgreSQL as a source for AWS SCT (p. 66).

SAP ASE (12.5, 15.0, 15.5, 15.7, and 16.0) Aurora MySQL, Aurora PostgreSQL, MariaDB 10.5, MySQL, PostgreSQL

For more information, see Using SAP ASE (Sybase ASE) as a source for AWS SCT (p. 76).

AWS SCT supports the following data warehouse conversions.

Source data warehouse Target data warehouse

Amazon Redshift Amazon Redshift

For more information, see Using Amazon Redshift as a source for AWS SCT (p. 79).

Azure Synapse Analytics (version 10) Amazon Redshift Greenplum Database (version 4.3 and later) Amazon Redshift

For more information, see Using Greenplum Database as a source for AWS SCT (p. 82).

Microsoft SQL Server (version 2008 and later) Amazon Redshift

For more information, see Using Microsoft SQL Server Data Warehouse as a source for AWS SCT (p. 93).

Netezza (version 7.0.3 and later) Amazon Redshift

For more information, see Using Netezza as a source for AWS SCT (p. 84).

Oracle (version 10.2 and later) Amazon Redshift

For more information, see Using Oracle Data Warehouse as a source for AWS SCT (p. 87).

Teradata (version 13 and later) Amazon Redshift

For more information, see Using Teradata as a source for AWS SCT (p. 95).

Vertica (version 7.2 and later) Amazon Redshift

For more information, see Using Vertica as a source for AWS SCT (p. 98).

Snowﬂake (version 3) Amazon Redshift

(10)

Schema conversion overview

Source data warehouse Target data warehouse

For more information, see Using Snowﬂake as a source for AWS SCT (p. 89).

AWS SCT supports the following data NoSQL database conversions.

Apache Cassandra (versions 2.1.0, 2.1.20, and

3.11.4) Amazon DynamoDB

Schema conversion overview

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.

For information about how to install AWS SCT, see Installing, verifying, and updating AWS SCT (p. 5).

For an introduction to the AWS SCT user interface, see Using the AWS SCT user interface (p. 14).

For information on the conversion process, see Converting database schemas using the AWS SCT (p. 119).

In addition to converting your existing database schema from one database engine to another, AWS SCT has some additional features that help you move your data and applications to the AWS Cloud:

• You can use data extraction agents to extract data from your data warehouse to prepare to migrate it to Amazon Redshift. To manage the data extraction agents, you can use AWS SCT. For more information, see Using data extraction agents (p. 187).

• You can use AWS SCT to create AWS DMS endpoints and tasks. You can run and monitor these tasks from AWS SCT. For more information, see Using AWS SCT with AWS DMS (p. 185).

• In some cases, database features can't be converted to equivalent Amazon RDS or Amazon Redshift features. The AWS SCT extension pack wizard can help you install AWS Lambda functions and Python libraries to emulate the features that can't be converted. For more information, see Using the AWS SCT extension pack (p. 259).

• You can use AWS SCT to optimize your existing Amazon Redshift database. AWS SCT recommends sort keys and distribution keys to optimize your database. For more information, see Optimizing Amazon Redshift by using the AWS SCT (p. 156).

• You can use AWS SCT to copy your existing on-premises database schema to an Amazon RDS DB instance running the same engine. You can use this feature to analyze potential cost savings of moving to the cloud and of changing your license type.

• You can use AWS SCT to convert SQL in your C++, C#, Java, or other application code. You can view, analyze, edit, and save the converted SQL code. For more information, see Converting application SQL using the AWS SCT (p. 238).

• You can use AWS SCT to migrate extraction, transformation, and load (ETL) processes. For more information, see Converting extract, transform, and load (ETL) processes with AWS Schema Conversion Tool (p. 158).

(11)

Giving feedback

Providing feedback

You can provide feedback about the AWS SCT. You can ﬁle a bug report, submit a feature request, or provide general information.

To provide feedback about AWS SCT

1. Start the AWS Schema Conversion Tool.

2. Open the Help menu and then choose Leave Feedback. The Leave Feedback dialog box appears.

3. For Area, choose Information, Bug report, or Feature request.

4. For Source database, choose your source database. Choose Any if your feedback is not speciﬁc to a particular database.

5. For Target database, choose your target database. Choose Any if your feedback is not speciﬁc to a particular database.

6. For Title, type a title for your feedback.

7. For Message, type your feedback.

8. Choose Send to submit your feedback.

(12)

Installing AWS SCT

Installing, verifying, and updating AWS SCT

The AWS Schema Conversion Tool (AWS SCT) is a standalone application that provides a project-based user interface. AWS SCT is available for Fedora Linux, Microsoft Windows, and Ubuntu Linux version 15.04. AWS SCT is supported only on 64-bit operating systems.

To ensure that you get the correct version of the AWS SCT distribution file we provide verification steps after you download the compressed file. You can verify the file using the steps provided.

Topics

• Installing AWS SCT (p. 5)

• Verifying the AWS SCT ﬁle download (p. 6)

• Installing the required database drivers (p. 8)

• Updating the AWS SCT (p. 13)

Installing AWS SCT

To install the AWS SCT

1. Download the compressed file that contains the AWS SCT installer, using the link for your operating system. All compressed files have a .zip extension. When you extract the AWS SCT installer file, it will be in the appropriate format for your operating system.

• Microsoft Windows

• Ubuntu Linux (.deb)

• Fedora Linux (.rpm)

2. Extract the AWS SCT installer ﬁle for your operating system, shown following.

Operating system File name

Fedora Linux aws-schema-conversion-tool-1.0.build-number.x86_64.rpm Microsoft Windows AWS Schema Conversion Tool-1.0.build-number.msi

Ubuntu Linux aws-schema-conversion-tool-1.0.build-number.deb

3. Run the AWS SCT installer ﬁle extracted in the previous step. Use the instructions for your operating system, shown following.

Operating system Install instructions

Fedora Linux Run the following command in the folder that you downloaded the ﬁle to:

(13)

Installing previous versions

Operating system Install instructions

sudo yum install aws-schema-conversion-tool-1.0.build- number.x86_64.rpm

Microsoft Windows Double-click the ﬁle to run the installer.

Ubuntu Linux Run the following command in the folder that you downloaded the ﬁle to:

sudo dpkg -i aws-schema-conversion-tool-1.0.build- number.deb

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

For instructions and download links, see Installing the required database drivers (p. 8).

Now, you have completed the setup of the AWS SCT application. Double-click the application icon to run AWS SCT.

Installing previous versions of AWS SCT

You can download and install previous versions of the AWS SCT starting from 1.0.625. Use the following format to download a previous version. You must provide the version and OS information using this format.

https://d211wdu1froga6.cloudfront.net/builds/1.0/<version>/<OS>/aws-schema-conversion- tool-1.0.zip

For example, to download AWS SCT version 632, do the following:

• Windows - https://d211wdu1froga6.cloudfront.net/builds/1.0/632/Windows/aws-schema- conversion-tool-1.0.zip

• Ubuntu - https://d211wdu1froga6.cloudfront.net/builds/1.0/632/Ubuntu/aws-schema-conversion- tool-1.0.zip

• Fedora - https://d211wdu1froga6.cloudfront.net/builds/1.0/632/Fedora/aws-schema-conversion- tool-1.0.zip

Verifying the AWS SCT ﬁle download

There are several ways you can verify the distribution file of the AWS SCT. The simplest is to compare the checksum of the file with the published checksum from AWS. As an additional level of security, you can use the procedures following to verify the distribution file, based on the operating system where you installed the file.

This section includes the following topics.

Topics

• Verifying the checksum of the AWS SCT ﬁle (p. 7)

• Verifying the AWS SCT RPM ﬁles on Fedora (p. 7)

• Verifying the AWS SCT DEB ﬁles on Ubuntu (p. 8)

(14)

Verifying the checksum of the AWS SCT ﬁle

• Verifying the AWS SCT MSI ﬁle on Microsoft Windows (p. 8)

Verifying the checksum of the AWS SCT ﬁle

In order to detect any errors that could have been introduced when downloading or storing the AWS SCT compressed ﬁle, you can compare the ﬁle checksum with a value provided by AWS. AWS uses the SHA256 algorithm for the checksum.

To verify the AWS SCT distribution ﬁle using a checksum

1. Download the AWS SCT distribution ﬁle using the links in the Installing section.

2. Download the latest checksum ﬁle, called sha256Check.txt. For example, the ﬁle can appear like the following:

Fedora b4f5f66f91bfcc1b312e2827e960691c269a9002cd1371cf1841593f88cbb5e6 Ubuntu 4315eb666449d4fcd95932351f00399adb6c6cf64b9f30adda2eec903c54eca4

Windows 6e29679a3c53c5396a06d8d50f308981e4ec34bd0acd608874470700a0ae9a23

3. Run the SHA256 validation command for your operating system in the directory that contains the distribution ﬁle. For example, the command to run on the Linux operating system is the following:

shasum -a 256 aws-schema-conversion-tool-1.0.latest.zip

4. Compare the results of the command with the value shown in the sha256Check.txt ﬁle. The two values should match.

Verifying the AWS SCT RPM ﬁles on Fedora

AWS provides another level of validation in addition to the distribution file checksum. All RPM files in the distribution file are signed by an AWS private key. The public GPG key can be viewed at amazon.com.public.gpg-key.

To verify the AWS SCT RPM ﬁles on Fedora

2. Verify the checksum of the AWS SCT distribution ﬁle.

3. Extract the contents of the distribution ﬁle. Locate the RPM ﬁle you want to verify.

4. Download GPG public key from amazon.com.public.gpg-key

5. Import the public key to your RPM DB (make sure you have the appropriate permissions) by using the following command:

sudo rpm --import [email protected]

6. Check that the import was successful by running the following command:

rpm -q --qf "%{NAME}-%{VERSION}-%{RELEASE} \n %{SUMMARY} \n" gpg-pubkey- ea22abf4-5a21d30c

7. Check the RPM signature by running the following command:

rpm --checksig -v aws-schema-conversion-tool-1.0.build number-1.x86_64.rpm

(15)

Verifying the AWS SCT DEB ﬁles on Ubuntu

AWS provides another level of validation in addition to the distribution file checksum. All DEB files in the distribution file are signed by a GPG detached signature.

To verify the AWS SCT DEB ﬁles on Ubuntu

2. Verifying the checksum of the AWS SCT distribution ﬁle.

3. Extract the contents of the distribution ﬁle. Locate the DEB ﬁle you want to verify.

4. Download the detached signature from aws-schema-conversion-tool-1.0.latest.deb.asc.

5. Download the GPG public key from amazon.com.public.gpg-key.

6. Import the GPG public key by running the following command:

gpg --import [email protected] 7. Verify the signature by running the following command:

gpg --verify aws-schema-conversion-tool-1.0.latest.deb.asc aws-schema-conversion- tool-1.0.build number.deb

Verifying the AWS SCT MSI ﬁle on Microsoft Windows

AWS provides another level of validation in addition to the distribution ﬁle checksum. The MSI ﬁle has a digital signature you can check to ensure it was signed by AWS.

To verify the AWS SCT MSI ﬁle on Windows

2. Verifying the checksum of the AWS SCT distribution ﬁle.

3. Extract the contents of the distribution ﬁle. Locate the MSI ﬁle you want to verify.

4. In Windows Explorer, right-click the MSI ﬁle and select Properties.

5. Choose the Digital Signatures tab.

6. Verify that the digital signature is from Amazon Services LLC.

Installing the required database drivers

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

After you download the drivers, you give the location of the driver ﬁles. For more information, see Storing driver paths in the global settings (p. 11).

You can download the database drivers from the following locations.

Important

Install the latest version of the driver available. The following table includes the lowest version of database driver supported by AWS SCT.

(16)

Installing the required database drivers

Database

engine Drivers Download location

Amazon Aurora (MySQL compatible)

mysql-connector-java-5.1.6.jar https://www.mysql.com/products/connector/

Amazon Aurora (PostgreSQL compatible)

postgresql-42.2.19.jar https://jdbc.postgresql.org/download/

postgresql-42.2.19.jar

Amazon

Redshift redshift-jdbc42-2.0.0.1.jar https://s3.amazonaws.com/redshift-downloads/

drivers/jdbc/2.0.0.1/redshift-jdbc42-2.0.0.1.zip Azure SQL mssql-jdbc-7.2.2.jre11.jar https://docs.microsoft.com/en-us/sql/connect/

jdbc/release-notes-for-the-jdbc-driver?view=sql- server-ver15#72

Azure Synapse mssql-jdbc-7.2.2.jre11.jar https://docs.microsoft.com/en-us/sql/connect/

Greenplum

Database postgresql-42.2.19.jar https://jdbc.postgresql.org/download/

postgresql-42.2.19.jar

Maria DB mariadb-java-client-2.4.1.jar https://downloads.mariadb.com/Connectors/

java/connector-java-2.4.1/mariadb-java- client-2.4.1.jar

Microsoft SQL

Server mssql-jdbc-7.2.2.jre11.jar https://docs.microsoft.com/en-us/sql/connect/

MySQL mysql-connector-java-8.0.15.jar https://dev.mysql.com/downloads/connector/j/

Netezza nzjdbc.jar

Use the client tools software.

Install driver version 7.2.1, which is backwards compatible with data warehouse version 7.2.0.

http://www.ibm.com/support/knowledgecenter/

SSULQD_7.2.1/com.ibm.nz.datacon.doc/

c_datacon_plg_overview.html

Oracle ojdbc8.jar

Driver versions 8 and later are supported.

https://www.oracle.com/database/technologies/

jdbc-ucp-122-downloads.html

PostgreSQL postgresql-42.2.19.jar https://jdbc.postgresql.org/download/

postgresql-42.2.19.jar SAP ASE

(Sybase ASE) jconn4.jar Available as part of the SDK for SAP Adaptive Server Enterprise 16 provided with SAP ASE product. You can download the trial version of the SDK at https://www.sap.com/developer/trials- downloads/additional-downloads/sdk-for-sap- adaptive-server-enterprise-16-13351.html Teradata terajdbc4.jar https://downloads.teradata.com/download/

connectivity/jdbc-driver

(17)

Installing JDBC drivers on Linux

Database

engine Drivers Download location

tdgssconﬁg.jar

Vertica vertica-jdbc-9.1.1-0.jar Driver versions 7.2.0 and later are supported.

https://www.vertica.com/client_drivers/9.1.x/

9.1.1-0/vertica-jdbc-9.1.1-0.jar

IBM DB2 LUW db2jcc-db2jcc4.jar https://www.ibm.com/support/pages/node/

382667 Snowﬂake snowﬂake-jdbc-3.9.2.jar

For more information, see Downloading / Integrating the JDBC Driver

https://repo1.maven.org/maven2/net/snowﬂake/

snowﬂake-jdbc/3.9.2/snowﬂake-jdbc-3.9.2.jar

Installing JDBC drivers on Linux

You can use the following steps to install the JDBC drivers on your Linux system for use with AWS SCT.

To install JDBC drivers on your Linux system

1. Create a directory to store the JDBC drivers in.

PROMPT>sudo mkdir –p /usr/local/jdbc-drivers

2. Install the JDBC driver for your database engine using the commands shown following.

Database engine Installation commands Amazon Aurora

(MySQL compatible) PROMPT> cd /usr/local/jdbc-drivers

PROMPT> sudo tar xzvf /tmp/mysql-connector-java-X.X.X.tar.gz

Amazon Aurora (PostgreSQL compatible)

PROMPT> cd /usr/local/jdbc-drivers

PROMPT> sudo cp -a /tmp/postgresql-X.X.X.jre7.tar .

Microsoft SQL Server

PROMPT> sudo tar xzvf /tmp/sqljdbc_X.X.X_enu.tar.gz

MySQL

PROMPT> sudo tar xzvf /tmp/mysql-connector-java-X.X.X.tar.gz

Oracle PROMPT> cd /usr/local/jdbc-drivers PROMPT> sudo mkdir oracle-jdbc PROMPT> cd oracle-jdbc

PROMPT> sudo cp -a /tmp/ojdbc8.jar .

PostgreSQL

PROMPT> sudo cp -a /tmp/postgresql-X.X.X.jre7.tar .

(18)

Storing driver paths in the global settings

After you have downloaded and installed the required JDBC drivers, you can set the location of the drivers globally in the AWS SCT settings. If you don't set the location of the drivers globally, the application asks you for the location of the drivers when you connect to a database.

To update the driver ﬁle locations

1. In the AWS SCT, choose Settings, and then choose Global Settings.

2. For Global settings, choose Drivers. Add the ﬁle path to the JDBC driver for your source database engine and your target Amazon RDS DB instance database engine.

(19)

Storing driver paths in the global settings

(20)

Updating the AWS SCT

3. When you are ﬁnished adding the driver paths, choose OK.

Updating the AWS SCT

AWS periodically updates the AWS SCT with new features and functionality. If you are updating from a previous version, create a new AWS SCT project and reconvert any database objects you are using.

You can check to see if updates exist for the AWS SCT.

To check for updates to AWS SCT

1. When in the AWS SCT, choose Help and then choose Check for Updates.

2. In the Check for Updates dialog box, choose What's New. If the link does not appear, you have the latest version.

(21)

The project window

Using the AWS SCT user interface

Use the following topics to help you work with the AWS SCT user interface. For information on installing AWS SCT, see Installing, verifying, and updating AWS SCT (p. 5).

Topics

• The AWS SCT project window (p. 14)

• Starting the AWS SCT (p. 15)

• Creating an AWS SCT project (p. 16)

• Saving and opening an AWS SCT project (p. 17)

• Adding database servers to an AWS SCT project (p. 17)

• Running AWS SCT in an oﬄine mode (p. 18)

• Using AWS SCT tree ﬁlters (p. 18)

• Hiding schemas in the AWS SCT tree view (p. 21)

• Creating and reviewing the database migration assessment report (p. 23)

• Converting your schema (p. 30)

• Applying the converted schema to your target DB instance (p. 34)

• Storing AWS service proﬁles in the AWS SCT (p. 34)

• Using AWS Secrets Manager (p. 39)

• Storing database passwords (p. 39)

• Using the UNION ALL view for projects with partitioned tables (p. 39)

• Keyboard shortcuts for the AWS SCT (p. 40)

The AWS SCT project window

The illustration following is what you see in the AWS SCT when you create a schema migration project, and then convert a schema.

1. In the left panel, the schema from your source database is presented in a tree view. Your database schema is "lazy loaded." In other words, when you select an item from the tree view, AWS SCT gets and displays the current schema from your source database.

2. In the top middle panel, action items appear for schema elements from the source database engine that couldn't be converted automatically to the target database engine.

3. In the right panel, the schema from your target DB instance is presented in a tree view. Your database schema is "lazy loaded." That is, at the point when you select an item from the tree view, AWS SCT gets and displays the current schema from your target database.

(22)

Starting AWS SCT

4. In the lower left panel, when you choose a schema element, properties are displayed. These describe the source schema element and the SQL command to create that element in the source database.

5. In the lower right panel, when you choose a schema element, properties are displayed. These describe the target schema element and the SQL command to create that element in the target database. You can edit this SQL command and save the updated command with your project.

Starting the AWS SCT

To start the AWS Schema Conversion Tool, use the instructions for your operating system shown following.

Operating system Instructions

Fedora Linux Run the following command:

/opt/AWSSchemaConversionTool/AWSSchemaConversionTool

(23)

Creating a project

Operating system Instructions

Microsoft Windows Double-click the application icon.

Ubuntu Linux Double-click the application icon or run the following command:

sudo /opt/aws-schema-conversion-tool/bin/

AWSSchemaConversionTool

Creating an AWS SCT project

Use the following procedure to create an AWS Schema Conversion Tool project.

To create your project

2. On the File menu, choose New project. The New project dialog box appears.

3. Enter a name for your project, which is stored locally on your computer.

4. Enter the location for your local project ﬁle.

5. Choose OK to create your AWS SCT project.

6. Choose Add source to add a new source database to your AWS SCT project. You can add multiple source databases to your AWS SCT project.

7. Choose Add target to add a new target platform in your AWS SCT project. You can add multiple target platforms to your AWS SCT project.

8. Choose the source database schema in the left panel.

9. In the right panel, specify the target database platform for the selected source schema.

10. Choose Create mapping. This button becomes active after you choose the source database schema and the target database platform.

Now, your AWS SCT project is set up. You can save your project, create database migration assessment report, and convert your source database schemas.

(24)

Saving and opening a project

Saving and opening an AWS SCT project

Use the following procedure to save an AWS Schema Conversion Tool project.

To save your project

2. On the File menu, choose Save project.

AWS SCT saves the project in the folder, which you speciﬁed when you created the project.

Use the following procedure to open an existing AWS Schema Conversion Tool project.

To open your project

1. On the File menu, choose Open project. The Open dialog box appears.

2. Choose the project folder and then choose the Windows Script Component (*.sct) ﬁle.

If you open a project saved in AWS SCT version 1.0.655 or before, AWS SCT automatically creates mapping rules for all source database schemas to the target database platform. To add other target database platforms, delete existing mapping rules and then create new mapping rules. For more information on creating mapping rules, see Creating mapping rules (p. 101).

Adding database servers to an AWS SCT project

You can add multiple source and target database servers to an AWS Schema Conversion Tool project.

To add a server to your project

2. Create a new project or open an existing project.

3. Choose Add source from the menu to add a new source database.

4. Choose a database platform and specify database connection credentials. For more information on connecting to a source database, see Sources for AWS SCT (p. 43).

Use the following procedure to connect to your database.

To connect to your database

1. Open the context (right-click) menu for a database server, and then choose Establish connection.

You can also choose Connect to the server at the top of your database schema tree.

2. Enter the password to connect to your source database server.

3. Choose Test connection to verify that AWS SCT can connect to your source database.

4. Choose Connect to connect to your source database.

Use the following procedure to remove a database server from your AWS SCT project.

To remove a database server

1. Choose the database server to remove.

(25)

Using an oﬄine mode

2. Open the context (right-click) menu, and then choose Remove from project.

AWS SCT removes the selected database server, all mapping rules, conversion results, and other metadata related to this server.

Running AWS SCT in an oﬄine mode

You can run AWS Schema Conversion Tool in an oﬄine mode. Following, you can learn how to work with an existing AWS SCT project when disconnected from your source database.

AWS SCT doesn't require a connection to your source database to run the following operations:

• Add mapping rules.

• Create database migration assessment reports.

• Convert database schemas and code.

• Edit your source and converted code.

• Save your source and converted code as SQL scripts in a text ﬁle.

Before you use AWS SCT in an oﬄine mode, connect to your source database, load metadata, and save your project. Open this project or disconnect from the source database server to use AWS SCT in an oﬄine mode.

To run AWS SCT in an oﬄine mode

1. Start the AWS Schema Conversion Tool and create a new project. For more information, see Creating an AWS SCT project (p. 16).

2. Add a source database server and connect to your source database. For more information, see Adding database servers to an AWS SCT project (p. 17).

3. Add a target database server or use a virtual target database platform. For more information, see Using virtual targets (p. 102).

4. Create a mapping rule to deﬁne the target database platform for your source database. For more information, see Creating mapping rules in AWS SCT (p. 101).

5. Choose View, and then choose Main view.

6. In the left panel that displays the objects of your source database, choose your source database schemas. Open the context (right-click) menu for the object, and then choose Load schema. This operation loads all source schema metadata into your AWS SCT project.

The Create report and Convert schema operations also load all source schema metadata into your AWS SCT project. If you ran one of these operations from the context menu, skip the Load schema operation.

7. On the File menu, choose Save project to save the source database metadata in your project.

8. Choose Disconnect from the server to disconnect from your source database. Now you can use AWS SCT in the oﬄine mode.

Using AWS SCT tree ﬁlters

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 ﬁlters in AWS SCT to search for objects in the source and target tree structures. When you use a tree ﬁlter, you don't

(26)

Using tree ﬁlters

change the objects that are converted when you convert your database. The ﬁlter changes only what you see in the tree.

Tree ﬁlters work with objects that AWS SCT has preloaded. In other words, AWS SCT doesn't load objects from the database during searches. This approach means that the tree structure generally contains fewer objects than are present in the database.

For tree ﬁlters, keep the following in mind:

• The filter default is ANY, which means that the filter uses a name search to find objects.

• When you select one or more object types, you see only those types of objects in the tree.

• You can use the ﬁlter mask to show diﬀerent types of symbols, including Unicode, spaces, and special characters. The “%” character is the wildcard for any symbol.

• After you apply a ﬁlter, the count shows only the number of ﬁltered objects.

To create a tree ﬁlter

1. Open an existing AWS SCT project.

2. Connect to the database that you want to apply the tree ﬁlter to.

3. Choose the ﬁlter icon.

The undo ﬁlter icon is grayed out because no ﬁlter is currently applied.

4. Enter the following information in the Filter dialog box. Options in the dialog box are diﬀerent for each database engine.

AWS SCT ﬁlter option Action

Level Choose Categories to ﬁlter objects by categories.

Choose Statuses to ﬁlter objects by statuses.

Type For Categories in Level, choose the categories of ﬁltered objects. Choose Any loaded to display objects from all categories.

For Statuses in Level, choose the status of ﬁltered objects. You can choose one of the following options:

• Converted to display all converted objects

• Has actions to display all objects that have conversion issues

• Encrypted to display all encrypted objects

Condition For Categories in Level, choose the ﬁltering condition between Like and Not like.

For Statuses in Level, the ﬁltering condition option isn't available.

Value For Categories in Level, enter the Value to ﬁlter the tree by this value.

Use the percent (%) as a wildcard to display all objects.

For Statuses in Level, choose the Value between True and False.

And/Or Choose AND or OR logical operators to apply multiple ﬁlter clauses.

(27)

Using tree ﬁlters

5. Choose Add new clause to add an additional ﬁlter clause. AWS SCT can apply multiple ﬁlter clauses using AND or OR logical operators.

6. Choose Apply. After you choose Apply, the undo filter icon (next to the filter icon) is enabled. Use this icon if you want to remove the filters you applied.

7. Choose Close to close the dialog box.

(28)

Importing a ﬁle list for the tree ﬁlter

When you ﬁlter the schema that appears in the tree, you don't change the objects that are converted when you convert your schema. The ﬁlter only changes what you see in the tree.

Importing a ﬁle list for the tree ﬁlter

You can import a comma separated value (CSV) file or a JSON file that contains names or values that you want the tree filter to use. Open an existing AWS SCT project, connect to the database to apply the tree filter to, and then choose the filter icon.

To download an example of the ﬁle, choose Download template. Enter the ﬁle name and choose Save.

To download your existing ﬁlter settings, choose Export. Enter the ﬁle name and choose Save.

To import a file list for the tree filter, choose Import. Choose a file to import, and then choose Open.

Choose Apply, and then choose Close.

CSV ﬁles have the following format:

• object_type is the type of object that you want to ﬁnd.

• database_name is the name of database where this object exists.

• schema_name is the name of schema where this object exists.

• object_name is the object name.

• import_type speciﬁes to include or exclude this item from the ﬁlter.

Use JSON files to describe complex filtering cases, such as nested rules. JSON files have the following format:

• filterGroupType is the type of ﬁlter rule (AND or OR logical operators) that applies to multiple ﬁlter clauses.

• filterCategory is the level of the ﬁlter (Categories or Statuses).

• names is the list of object names that applies for the Categories ﬁlter.

• filterCondition is the ﬁltering condition (LIKE or NOT LIKE) that applies for the Categories ﬁlter.

• transformName is the status name that applies for the Status ﬁlter.

• value is the value to ﬁlter the tree by.

• transformValue is the value of the ﬁlter (TRUE or FALSE) that applies for the Status ﬁlter.

Hiding schemas in the AWS SCT tree view

By using tree view settings, you specify what schemas and databases you want to see in the AWS SCT tree view. You can hide empty schemas, empty databases, system databases, and user-deﬁned databases and schemas.

To hide databases and schemas in tree view

1. Open an AWS SCT project.

2. Connect to the data store that you want to show in tree view.

3. Choose Settings, Global settings, Tree view.

(29)

Hiding schemas

4. In the Tree view settings section, do the following:

• For Vendor, choose database platform.

• Choose Hide empty schemas to hide empty schemas for the selected database platform.

• Choose Hide empty databases to hide empty databases for the selected database platform.

(30)

Managing the database migration assessment report

• For Hide system databases/schemas, choose system databases and schemas by name to hide them.

• For Hide user-deﬁned databases/schemas, enter the names of user-deﬁned databases and schemas that you want to hide, and then choose Add. The names are case insensitive.

5. Choose OK.

Creating and reviewing the database migration assessment report

The database migration assessment report summarizes all of the action items for schemas 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 eﬀort that it will take to write the equivalent code for your target DB instance.

You can create a database migration assessment report after you add the source databases and target platforms to your project and specify mapping rules.

To create and view the database migration assessment report

1. In the left panel that displays the schema from your source database, choose a schema object to create an assessment report for. Open the context (right-click) menu for the object, and then choose Create Report.

(31)

The assessment report view opens.

2. Choose the Action items tab.

The Action items tab displays a list of items that describe the schema that can't be converted automatically. Choose one of the action items in the list. AWS SCT highlights the item from your schema that the action item applies to, as shown following.

(32)

3. Choose the Summary tab.

(33)

The Summary tab displays the summary information from the database migration assessment report. It shows the number of items that were converted automatically, and the number of items that were not converted automatically. The summary also includes an estimate of the time that it will take to create schema in your target DB instance that are equivalent to those in your source database.

The section License Evaluation and Cloud Support contains information about moving your existing on-premises database schema to an Amazon RDS DB instance running the same engine. For example, if you want to change license types, this section of the report tells you which features from your current database to remove.

An example of an assessment report summary is shown following.

(34)

(35)

4. Choose the Summary tab, and then choose Save to PDF. The database migration assessment report is saved as a PDF ﬁle. The PDF ﬁle contains both the summary and action item information.

You can also choose Save to CSV to save the report as a CSV file. When you choose this option, the AWS SCT creates three CSV files. These files contain the following information:

• A list of conversion action items with recommended actions.

• A summary of conversion action items with an estimate of the eﬀort required to convert an occurrence of the action item.

• An executive summary with a number of action items categorized by the estimated time to convert.

(36)

(37)

Converting your schema

After you added source and target databases to your project and created mapping rules, you can convert your source database schemas. Use the following procedure to convert schema.

To convert your schema

1. Choose View, and then choose Main view.

2. In the left panel that displays the schema from your source database, choose schemas to convert.

Open the context (right-click) menu for the object, and then choose Convert schema.

(38)

(39)

3. When AWS SCT ﬁnishes converting the schema, you can view the proposed schema in the panel on the right of your project.

At this point, no schema is applied to your target database instance. The planned schema is part of your project. If you choose a converted schema item, you can see the planned schema command in the panel at lower center for your target database instance.

You can edit the schema in this window. The edited schema is stored as part of your project and is written to the target database instance when you choose to apply your converted schema.

(40)

(41)

Applying the converted schema to your target DB instance

You can apply the converted database schema to your target DB instance. After the schema has been applied to your target DB instance, you can update the schema based on the action items in the database migration assessment report.

Warning

The following procedure overwrites the existing target schema. Be careful not to overwrite schemas unintentionally. Be careful not to overwrite schemas in your target DB instance that you have already modiﬁed, or you overwrite those changes.

To apply the converted database schema to your target database instance

1. Choose the schema element in the right panel of your project that displays the planned schema for your target DB instance.

2. Open the context (right-click) menu for the schema element, and then choose Apply to database.

The converted schema is applied to the target DB instance.

Storing AWS service proﬁles in the AWS SCT

You can store your AWS credentials in the AWS SCT. AWS SCT uses your credentials when you use features that integrate with AWS services. For example, AWS SCT integrates with Amazon S3, AWS Lambda, Amazon Relational Database Service (Amazon RDS), and AWS Database Migration Service (AWS DMS).

AWS SCT asks you for your AWS credentials when you access a feature that requires them. You can store your credentials in the global application settings. When AWS SCT asks for your credentials, you can select the stored credentials.

You can store different sets of AWS credentials in the global application settings. For example, you can store one set of credentials that you use in test scenarios, and a different set of credentials that you use in production scenarios. You can also store different credentials for different AWS Regions.

Storing AWS credentials

Use the following procedure to store AWS credentials globally.

(42)

Storing AWS credentials

To store AWS credentials

2. Open the Settings menu, and then choose Global settings. The Global settings dialog box appears.

Choose AWS service proﬁles, as shown following.

(43)

(44)

3. Choose Add a new AWS service proﬁle.

4. Enter your AWS information as follows.

AWS SCT option Action

Proﬁle name Enter a name for your proﬁle.

AWS access key Enter your AWS access key.

AWS secret key Enter your AWS secret key.

Region Choose the AWS Region for your proﬁle.

AWS S3 bucket folder Choose the Amazon S3 bucket for your proﬁle. You need to specify a bucket only if you are using a feature that connects to Amazon S3.

Choose Use FIPS endpoint for S3 if you need to comply with the security requirements for the Federal Information Processing Standard (FIPS). FIPS endpoints are available in the following AWS Regions:

• US East (N. Virginia) Region

• US East (Ohio) Region

• US West (N. California) Region

• US West (Oregon) Region

5. Choose Test connection to verify that your credentials are correct and active.

The Test connection dialog box appears. You can see the status for each of the services connected to your proﬁle. Pass indicates that the proﬁle can successfully access the service.

(45)

Setting the default proﬁle for a project

6. After you have configured your profile, choose Save to save your profile or Cancel to cancel your changes.

7. Choose OK to close the Global settings dialog box.

Setting the default proﬁle for a project

You can set the default profile for an AWS SCT project. Doing this associates the AWS credentials stored in the profile with the project. With your project open, use the following procedure to set the default profile.

To set the default proﬁle for a project

1. Start the AWS Schema Conversion Tool and create a new project.

2. On the Settings menu, choose Project settings. The Project settings dialog box appears.

3. Choose the Project environment tab.

4. Choose Add a new AWS service profile to add a new profile. Then for AWS service profile, choose the profile that you want to associate with the project.

5. Choose OK to close the Project settings dialog box. You can also choose Cancel to cancel your changes.

(46)

Using AWS Secrets Manager

AWS SCT can use database credentials that you store in AWS Secrets Manager. You can ﬁll in all values in the database connection dialog box from Secrets Manager. To use Secrets Manager, make sure that you store AWS proﬁles in the AWS Schema Conversion Tool.

For more information about using AWS Secrets Manager, see What is AWS Secrets Manager? in the AWS Secrets Manager User Guide. For more information about storing AWS proﬁles, see Storing AWS service proﬁles in the AWS SCT (p. 34).

To retrieve database credentials from Secrets Manager

1. Start the AWS Schema Conversion Tool and create a new project.

2. Choose Add source or Add target to add a new database to your project.

3. Choose a database platform and then choose Next.

4. For AWS Secret, choose the secret you want to use.

5. Choose Populate. Then AWS SCT ﬁlls in all values in the database connection dialog box.

6. Choose Test connection to verify that AWS SCT can connect to your database.

7. Choose Connect to connect to your database.

AWS SCT supports secrets that have the following structure.

{

"username": "secret_user", "password": "secret_password", "engine": "oracle",

"host": "secret_host.eu-west-1.compute.amazonaws.com", "port": "1521",

"dbname": "ora_db"

}

In this structure, the username and password values are required, and all other values are optional.

Make sure that the values that you store in Secrets Manager include all database credentials.

Storing database passwords

You can store a database password or SSL certiﬁcate in the AWS SCT cache. To store a password, choose Store Password when you create a connection.

The password is encrypted using the randomly generated token in the seed.dat file. The password is then stored with the user name in the cache file. If you lose the seed.dat file or it becomes corrupted, the database password might be unencrypted incorrectly. In this case, the connection fails.

Using the UNION ALL view for projects with partitioned tables

If a source table is partitioned, AWS SCT creates n target tables, where n is the number of partitions on the source table. AWS SCT creates a UNION ALL view on top of the target tables to represent the source table. If you use an AWS SCT data extractor to migrate your data, the source table partitions will be extracted and loaded in parallel by separate subtasks.

(47)

Keyboard shortcuts

To use Union All view for a project

1. Start AWS SCT. Create a new project or open an existing AWS SCT project.

2. On the Settings menu, choose Conversion settings.

3. Choose a pair of OLAP databases from the list at the top.

4. Turn on Use Union all view?

5. Choose OK to save the settings and close the Conversion settings dialog box.

Keyboard shortcuts for the AWS SCT

The following are the keyboard shortcuts that you can use with the AWS SCT.

Keyboard shortcut Description

Ctrl+N Create a new project.

Ctrl+O Open an existing project.

Ctrl+S Save an open project.

(48)

Keyboard shortcuts

Keyboard shortcut Description

Ctrl+W Create a new project by using the wizard.

Ctrl+M Create a new multiserver assessment.

Ctrl+L Add a new source database.

Ctrl+R Add a new target database.

Ctrl+F4 Close an open project.

F1 Open the AWS SCT User Guide.

(49)

Getting started with AWS SCT

You can use the AWS Schema Conversion Tool (AWS SCT) to convert the schema for a source database located either on-premises or hosted by Amazon Web Services (AWS). You can convert your source schema to a schema for any supported database that is hosted by AWS. The AWS SCT application provides a project-based user interface.

Almost all work you do with AWS SCT starts with the following steps:

1. Install AWS SCT. For more information, see Installing, verifying, and updating AWS SCT (p. 5).

2. Install an AWS SCT agent, if needed. AWS SCT agents are only required for certain migration scenarios, such as between heterogeneous sources and targets. For more information, see Using data extraction agents (p. 187).

3. Familiarize yourself with the user interface of AWS SCT. For more information, see Using the AWS SCT user interface (p. 14).

4. Create an AWS SCT project. Connect to your source and target databases. For more information about connecting to your source database, see Sources for AWS SCT (p. 43).

5. Create mapping rules. For more information about mapping rules, see Creating mapping rules in AWS SCT (p. 101).

6. Run and then review the Database Migration Assessment Report. For more information about the assessment report, see Creating and reviewing the database migration assessment report (p. 23).

7. Convert the source database schemas. There are several aspects of the conversion you need to keep in mind, such as what to do with items that don't convert, and how to map items that should be converted a particular way. For more information about converting a source schema, see Converting database schemas using the AWS SCT (p. 119).

If you are converting a data warehouse schema, there are also aspects you need to consider before doing the conversion. For more information, see Converting data warehouse schemas to Amazon Redshift using the AWS SCT (p. 134).

8. Applying the schema conversion to your target. For more information about applying a source schema conversion, see Using the AWS SCT user interface (p. 14).

9. You can also use the AWS SCT to convert SQL stored procedures and other application code. For more information, see Converting application SQL using the AWS SCT (p. 238)

You can also use AWS SCT to migrate your data from a source database to an Amazon-managed database. For examples, see Using data extraction agents (p. 187).

(50)

Encrypting Amazon RDS connections

Sources for AWS SCT

AWS Schema Conversion Tool (AWS SCT) can convert schemas from the following source databases and data warehouses to a target database or data warehouse. For information about permissions, connections, and what AWS SCT can convert for use with the target database or data warehouse, see details in the topics listed following.

Encryption information

Encrypting Amazon RDS connections (p. 43)

Database sources

• Using Apache Cassandra as a source (p. 45)

• Using Azure SQL Database as a source (p. 46)

• Using IBM Db2 LUW as a source (p. 48)

• Using MySQL as a source (p. 52)

• Using Oracle Database as a source (p. 54)

• Using PostgreSQL as a source (p. 66)

• Using SAP ASE (Sybase ASE) as a source (p. 76)

• Using SQL Server as a source (p. 68)

Data warehouse sources

• Using Amazon Redshift as a source (p. 79)

• Using Azure Synapse Analytics as a source (p. 81)

• Using Greenplum Database as a source (p. 82)

• Using Netezza as a source (p. 84)

• Using Oracle Data Warehouse as a source (p. 87)

• Using SQL Server Data Warehouse as a source (p. 93)

• Using Snowﬂake as a source (p. 89)

• Using Teradata as a source (p. 95)

• Using Vertica as a source (p. 98)

Encrypting Amazon RDS and Amazon Aurora connections in AWS SCT

To open encrypted connections to Amazon RDS or Amazon Aurora databases from an application, you need to import AWS root certiﬁcates into some form of key storage. You can download the root certiﬁcates from AWS at Using SSL/TLS to Encrypt a Connection to a DB Instance in the Amazon RDS User Guide.

Two options are available, a root certificate that works for all AWS Regions and a certificate bundle that contains both the old and new root certificates.

Depending on which you want to use, follow the steps in one of the two following procedures.

To import the certiﬁcate or certiﬁcates into the Windows system storage

1. Download a certiﬁcate or certiﬁcates from one of the following sources:

AWS Schema Conversion Tool

AWS Schema Conversion Tool

User Guide

Version 1.0

AWS Schema Conversion Tool: User Guide

Table of Contents

What is the AWS Schema Conversion Tool?

Schema conversion overview

Providing feedback

To provide feedback about AWS SCT

Installing, verifying, and updating AWS SCT

Installing AWS SCT

To install the AWS SCT

Installing previous versions of AWS SCT

Verifying the AWS SCT ﬁle download

Verifying the checksum of the AWS SCT ﬁle

To verify the AWS SCT distribution ﬁle using a checksum

Verifying the AWS SCT RPM ﬁles on Fedora

To verify the AWS SCT RPM ﬁles on Fedora

Verifying the AWS SCT DEB ﬁles on Ubuntu

To verify the AWS SCT DEB ﬁles on Ubuntu

Verifying the AWS SCT MSI ﬁle on Microsoft Windows

To verify the AWS SCT MSI ﬁle on Windows

Installing the required database drivers

Important

Installing JDBC drivers on Linux

To install JDBC drivers on your Linux system

Storing driver paths in the global settings

To update the driver ﬁle locations

Updating the AWS SCT

To check for updates to AWS SCT

Using the AWS SCT user interface

The AWS SCT project window

Starting the AWS SCT

Creating an AWS SCT project

To create your project

Saving and opening an AWS SCT project

To save your project

To open your project

Adding database servers to an AWS SCT project

To add a server to your project

To connect to your database

To remove a database server

Running AWS SCT in an oﬄine mode

To run AWS SCT in an oﬄine mode

Using AWS SCT tree ﬁlters

To create a tree ﬁlter

Importing a ﬁle list for the tree ﬁlter

Hiding schemas in the AWS SCT tree view

To hide databases and schemas in tree view

Creating and reviewing the database migration assessment report

To create and view the database migration assessment report

Converting your schema

To convert your schema

Applying the converted schema to your target DB instance

Warning

To apply the converted database schema to your target database instance

Storing AWS service proﬁles in the AWS SCT

Storing AWS credentials

To store AWS credentials

Setting the default proﬁle for a project

To set the default proﬁle for a project

Using AWS Secrets Manager

To retrieve database credentials from Secrets Manager

Storing database passwords

Using the UNION ALL view for projects with partitioned tables

To use Union All view for a project

Keyboard shortcuts for the AWS SCT

Getting started with AWS SCT

Sources for AWS SCT

Database sources

Data warehouse sources

Encrypting Amazon RDS and Amazon Aurora connections in AWS SCT

To import the certiﬁcate or certiﬁcates into the Windows system storage