Configuring the AWS Schema Conversion Tool

The AWS Schema Conversion Tool (AWS SCT) is a project-style Java application that can be installed on a local computer or an Amazon EC2 instance. The tool can be downloaded from AWS and is supported on MS Windows, Mac OSX, and Ubuntu and Fedora Linux.

You can use the AWS Schema Conversion Tool 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 RDS MySQL DB instance, an Amazon Aurora DB cluster, an Amazon RDS PostgreSQL database instance, or an Amazon Redshift cluster.

SCT is simple to use and takes only minutes to get started after you install the tool locally on your machine. It requires only connectivity to the source and target databases. SCT uses JDBC drivers to connect to the source and target databases. When you set up your first DB connection to a new source, the wizard interface will prompt you for a driver. All supported drivers are listed by database engine in the Installing and Updating section of the documentation.

When both the source and target database connections are configured, simply right-click on the schema you want to analyze and choose Create Report.

SCT will examine in detail all of the objects in that schema, including the schema itself, tables, constraints, types, sequences, synonyms, collection types, views, procedures, functions, and packages. It will convert as much as possible automatically and provide detailed information about items it couldn’t convert. The report can be saved as a .csv file or a .pdf file for review.

Navigate to the Action Items tab in the report to see the items that the tool couldn’t convert and understand how much work you have to do.

When you’ve looked at the report, choose Actions, Convert schema from the menu bar to generate the data definition language (DDL) statements for the target database.

DDL for the target database is displayed in the project console, and you can edit it before applying it with SCT or simply saving it as an .sql file for application later.

In development, it’s a good idea to apply the changes directly from within SCT to a sandbox version of your database to understand what objects will be automatically converted, how many objects are involved, and what level of development effort is ahead. Beyond the sandbox and early development phases, it’s best practice to adhere to normal software lifecycle procedures within your organization. Scripted conversions will facilitate key processes like code reviews, automated builds, and automated deployments.

Working with Global Settings
The AWS Schema Conversion Tool is very easy to use and lets a DBA or developer move very quickly to analysis and deployment. However, sometimes the default settings might need to be modified. For example, such a change might be necessary if the wrong driver was entered through the wizard for a database engine, a single schema includes thousands of objects and places the Java virtual machine (JVM) under memory pressure, or verbose logging must be enabled. AWS Schema Conversion Tool exposes fine control over all these key areas through the Global settings dialog box. The sections following offer some suggestions on how to use global settings for fine control over these areas.

All sections following are accessed through the SCT console by choosing Settings and then Global Settings from the top menu bar.

Driver settings
You must have the required JDBC drivers installed for the AWS Schema Conversion Tool to connect to your source or target database. When a driver is downloaded, you can upload the driver into the tool through the wizard or through the Global settings dialog box. The advantage of uploading the drivers through the Global settings dialog box is that you can enter or update all of the drivers at one time.

Choose Drivers from the side navigation panel in the Global settings dialog box, and set the target database driver by choosing Browse, navigating to the local machine, and choosing the .jar file to associate with SCT for a particular database engine.

All projects within SCT for that database engine will use the selected driver.

Performance and memory settings

SCT has menu options that control how aggressively the tool will consume JVM memory:

• Fast conversion, but large memory consumption – This option optimizes for a fast conversion but might require more memory for the object reference cache.
• Slower conversion, lower memory consumption – This option minimizes the amount of memory used but results in a slower conversion. Use this option if your computer has a limited amount of memory.
• Balance speed with memory consumption – This option optimizes provides a balance between memory use and conversion speed.

To set these options, choose Performance and Memory from the side navigation panel in the Global settings dialog box, and change Performance and Memory Management Policy to the desired setting.

JVM size settings
The default amount of memory allocated to SCT is just less than a gigabyte. This memory size is usually sufficient for small databases of less than 1000 objects. For many databases, it is necessary to reconfigure the SCT JVM to use more memory even when the JVM is configured in the low memory consumption configuration.

The screenshot that follows shows an SCT JVM under duress, with the tasks allocated hitting the maximum boundary of JVM memory. The setting was Slower conversion, lower memory consumption and failed due to a too-small JVM memory setting.

You can increase (or decrease) the maximum and minimum memory allocated to the SCT JVM through configuration files.

Configuration files for SCT are located in the AWS Schema Conversion Tool installation directory under a subdirectory called app. The name of the main configuration file is AWS Schema Conversion Tool.cfg. You can modify the file using any text editor—however, don’t use a word processing tool (like Word or WordPad) because it might change certain characters in the file and prevent the file from being read correctly by the JVM.

The image following shows an AWS Schema Conversion Tool installation directory under C:\Program Files as an example.

The default file that is installed with SCT has a few parameters for the tool itself and a single JVM option. None of the existing parameters in the file should be changed without consulting AWS Support.

You can modify the memory settings of the JVM by adding two parameters under the [JavaOptions] section. However, use caution when configuring the JVM size. If your maximum size exceeds your system’s available memory, it might crash your machine.

The first of the two JVM memory parameters is the maximum JVM memory size, Xmx, which is recorded in megabytes and should be in standard recognized memory values. To set a maximum JVM size of 8 GB, set this value to –Xmx8192M with no spaces on a single line.

The second parameter is the minimum JVM memory size Xms, which is recorded in megabytes and should be in standard recognized memory values. To set a minimum JVM size of 4 GB, set this value to –Xms4096M with no spaces on a single line.

When you’re done editing, save the file and restart AWS SCT. You should see the values in the status bar change to reflect your new settings.

The screenshot that follows shows an SCT JVM running smoothly because the tasks allocated are operating comfortably within the maximum boundary of the JVM. The setting was Slower conversion, lower memory consumption, and the tool succeeded in converting nearly 5000 objects in about 10 minutes.

Logging
In addition to managing the memory settings of the AWS Schema Conversion Tool, it can be a good idea to increase the logging information produced by the tool when converting new projects. Although increasing logging information might slow conversion slightly, this change will give robust information to AWS Support if any unforeseen errors arise.

To change the logging settings, choose Settings and then Global Settings. In the left navigation pane, choose Logging. See the section above Memory Management for help.

In the logging pane, change Debug mode to true and choose a few key items to increase the logging information for. Setting the Parser, Type Mapping, and User Interface to TRACE can help with key problem areas during conversion.

You can also configure the log location. If the information becomes too verbose for the current file system where the logs are streaming, change the value to a location with sufficient space to capture the logs.

To transmit logs to AWS Support, navigate to the directory where the logs are stored, zip up all the files into a manageable single file, and upload the .zip file with your support case.
When the initial analysis is completed and ongoing development is taking place, simply return Debug mode to false to eliminate the verbose logging and increase speed.

Tip: To manage the log size and streamline reporting issues, remove the logs or move them to another location after a successful conversion. Doing this ensures that only the relevant errors and information are transmitted to AWS Support and keeps the log file system from filling.

All references in this document refer to Version 1 of the AWS SCT and were tested on build 401 successfully.

About the Author

Wendy Neu has worked as a Data Architect with Amazon since January 2015. Prior to joining Amazon, she worked as a consultant in Cincinnati, OH helping customers integrate and manage their data from different unrelated data sources.