AWS Schema Conversion Tool Guide

Introduction 

Moving between different database engines is a complex and time-consuming endeavor. Every organization is unique in terms of its infrastructure, processes and data needs, which is why there is no one-size-fits-all when it comes to migrations. In our experience translating the database schema is the most time-consuming task in any project. And that's where tools like AWS Schema Conversion Tool (SCT) can come in handy.

In this article, we will show you how to install, run and use the Schema Conversion Tool to translate the schema of a SQL Server database to PostgreSQL syntax. We'll also address the tool’s limitations, the challenges we encountered, and will look at potential alternatives to it.

What is AWS Schema Conversion Tool (SCT)

The AWS Schema Conversion Tool (SCT) is a service provided by Amazon Web Services to help convert your database schema from one database engine to another. AWS SCT provides a command line interface, a local user interface, and a managed service. All three options automatically assess and convert your source database schema, along with a majority of the database objects, into a format compatible with the selected target. SCT can also be used to scan application code and convert embedded SQL statements.

The AWS Schema Conversion Tool supports a variety of sources and targets. Source databases can include Oracle, Microsoft SQL Server, MySQL, MariaDB, SAP ASE, and PostgreSQL, while the target databases supported include Amazon Aurora, MySQL, MariaDB, PostgreSQL, and Amazon Redshift. While it is not clear from the user interface or the documentation, you can use the tool for local databases, as well.

AWS SCT works alongside AWS Database Migration Service (DMS), another tool provided by Amazon that helps in the actual migration of data. While the SCT tool adjusts your schema to make it compatible with the new database engine, DMS is responsible for migrating the data itself. 

For the purpose of this article, we will be using the free Schema Conversion Tool user interface locally on a Windows computer to convert the schema of a SQL Server database into a PostgreSQL compatible one. The two databases are created locally using Docker containers. We will only look into the schema conversion part of the process. Stay tuned for our AWS DMS review for information on the data migration part.

Installation

Installing AWS SCT as a stand-alone application is a straightforward process. You can find detailed instructions on their installation page. For the purpose of this article we used a Windows computer and had to follow these steps:

• Download for the tool for Windows 
• Download the JDBC driver for SQL Server: mssql-jdbc-10.2.jar
• Download thre JDBC driver for PostgreSQL: postgresql-42.2.19.jar
• Input source and target connection info and select the downloaded driver (.jar file).

AWS SCT currently only works on Windows and Linux (Fedora and Ubuntu), MacOS is not supported. 

Our source database 

The small sample SQL Server database we used for this demonstration contains the following objects:

• 4 schemas
• 10 tables
• 12 views
• 31 indexes
• 12 constraints
• 1 function
• 13 procedures

We have not included any logins, users and passwords.

How to use AWS Schema Conversion Tool

Once you have SCT up and running on your machine, you can create a new project or get started with the New Project Wizard option from the File menu. We recommend the latter, as we find it more intuitive to use. 

You will be prompted to set up your source system and choose the type of project you want to execute. You can choose between converting the source database to a new database engine, keeping the source as is and moving the database from on-premises to the cloud, or comparing the migration complexity of all available migration targets. For the purposes of this article we will select the third option in order to explore the complexity of different targets.

‍

Input the source connection information.

‍

Then choose the schema you want to analyze. It’s important to note here that If you want to analyze more than one schema, you need to select the checkbox next to the schema name, then select the containing database name, before you click Next. Otherwise SCT will analyze only one schema, even if there are multiple checkboxes selected.

‍

Now you are presented with the assessment report. Even if you already know your desired target system, it is still a good idea to compare its migration complexity to other options available to you.

‍

The report breaks down the necessary schema changes for each supported target into Auto/minimal changes and Complex Actions. Within these two categories you can see how many storage objects (e.g. tables, indexes, materialized views, etc.) and code objects(e.g. procedures, functions, triggers, etc.) need to be changed in order for the schema to work properly on the respective target. 

You also get a graph representation of the required action for each target that looks like this:

‍

‍

We recommend exporting the detailed report as a CSV file, as it will come in handy later on in the process. In the CSV you’ll find all required conversion actions along with the affected object, description of the issue, recommended action, estimated complexity, etc. Here is a summary of all required actions for our project with PostgreSQL as a target:

‍

In our case most of the detected issues are related to procedures, views and indexes. For instance we get “DMS SC can't convert the @@rowcount function in the current context” for a stored procedure with a recommended “simple” solution of “Convert your source code manually”. 

Let’s say you’re happy with the assessment report and want to move on with our schema conversion to PostgreSQL. Your next step is to set up the target. Make sure the database you are migrating to is already created in your target.

‍

As we mentioned in the beginning of the article, AWS advertises SCT as a tool that helps you migrate to the cloud. If you are setting up your project from scratch, you’ll notice that all target options are cloud-based. However, you can simply connect to a local database (in our case it is a database running in a docker container) and transfer your schema there.

The project setup is now complete and you are presented with the main view where most of the work is done.

‍

You’ll notice there are multiple targets in the right-hand panel that you have not set up yourself, such as Maria_DB (virtual). These are default, built-in examples provided by AWS SCT that you can use for learning and exploration. They are always there, regardless of your source or target connections, and do not affect your migration. Just make sure to uncheck them when transferring your DB objects.

Here you also have the option to create mapping rules by right-clicking on the source database and selecting Create mapping. Select the schema you want those rules to apply to and click New migration rule. Here you can set Transformation rules which define how objects will be named in the target DB.

‍

To run the actual schema conversion, with the target selected, right click on the database name and select Convert schema. Similarly you can convert one object at a time.

‍

Once SCT is done converting your source schema, it displays a proposed target schema in the right hand panel. Clicking on any of your source objects will display the original SQL Server definition in the middle top panel, along with the proposed PostgreSQL translation below.

Keep in mind that nothing has been applied to your target schema yet. At this point you can edit the proposed PostgreSQL definition based on the recommendation of the assessment report. This is where the exported CSV file comes in handy, because the actual tool does not point out the issues with each DB object.

‍

To apply the converted schema to your target system, all you need to do is choose the target schema in the right hand panel, right click on it and select Apply to database. It’s important to note that if a target schema already exists, this action will override it.

If the tool fails to create a certain object in the target database, the object will appear with a red exclamation mark in the right-hand panel.

‍

Limitations

Overall, the AWS Schema Conversion Tool does a decent job converting storage objects like tables. However, when it comes to code objects like procedures, its effectiveness is notably less reliable. If you are trying to convert a schema with a lot of tables and only a few stored procedures, then this may not be a deal breaker for you. However, there are additional limitations that you would need to consider before opting in for SCT.

Firstly, the tool is not inherently intuitive to use. To be fair, the task it is trying to accomplish is quite complex. However, navigation through the tool might be challenging, demanding a comprehensive tutorial to effectively learn its usage, which can also lead to a steep learning curve. Moreover, you would need to have your assessment report downloaded and open side by side with SCT while you do the manual changes, as the recommended actions are not listed in the tool itself.

Secondly, AWS Schema Conversion Tool does not provide the flexibility to specify conversion rules, such as data type mapping. And while it did a good job converting our sample tables from SQL Server to PostgreSQL, there were cases where we found that the proposed data type mappings did not match the best practices we’ve drawn from experience. Here are some examples:

‍

Thirdly, despite the presence of a "User guide" button within the tool, there is no actual user guide provided. This may not be a big deal for many, but it can increase the complexity and confusion in utilizing the tool.

‍

In SQL Server, the order of the input parameters in a stored procedure does not matter, while in PostgreSQL, parameters without default values must come first. Usually, in a manual migration project, the best practice is to rearrange the input param in the PostgreSQL procedure in order to preserve the business logic behind the specified default values. Schema conversion tool, on the other hand,  automatically assigns default values to input parameters in PostgreSQL, instead of rearranging them, so that it can preserve the original order. Again this may not be a deal breaker, but it is something to look out for to avoid unexpected, automatically assigned null values.

The biggest drawback of Schema Conversion Tool, and any tool that claims to do automatic schema translations, is that it does not understand business logic. It is one thing to map a data type from one system to another, and a completely different thing to understand why that choice of data type was made in the first place. Same goes for virtually any database object.

For instance, when using the tool we noticed that it automatically converted all SQL Server procedures to PostgreSQL procedures. Depending on the way your application interacts with your database, this might present an issue, because PostgreSQL functions return results, whereas procedures do not. Although procedures can return data via output parameters, they are designed to perform an action, finish and return control to the caller. It’s worth noting that procedures did not even exist before PostgreSQL version 11.

What we’ve learned from our experience with database migrations when it comes to functions vs procedures in PostgreSQL can be summarized as follows:

• If the original SQL Server stored procedure does not return anything (e.g. it only inserts into the database), it should be converted into a PostgreSQL procedure;
• If the procedure returns a single value/record (e.g. it returns the id for a newly created record), it can be a PostgreSQL procedure with a specified OUT parameter;
• If the procedure returns a set of records that corresponds to an entire table’s columns, convert it to PostgreSQL function using RETURNS SETOF;
• If a procedure returns a subset of columns, convert it to a PostgreSQL function using RETURNS TABLE.

Overall, AWS Schema Conversion Tool appears to mimic features of the source SQL Server as closely as possible, rather than leveraging native PostgreSQL capabilities that might offer more effective solutions (e.g. using bool type, or utilizing functions in PostgreSQL). While this approach may offer a sense of familiarity for users experienced in SQL Server, it may also prevent them from harnessing the full potential of PostgreSQL.

Alternatives to AWS Schema Conversion Tool

There are a number of alternative tools available on the market for database schema conversion. Some of these include Ispirer MnMTK, SQLines, SQL Workbench/J, and Oracle SQL Developer, and Full Convert.

Ispirer MnMTK is a paid migration tool which offers conversion solutions for all major databases, promising flexible customization, high automation, and efficient migration. Oracle SQL Developer is another such tool that provides schema and data conversion features, especially for Oracle databases. Full Convert, on the other hand, is a tool that specifically caters to the conversion between SQL Server and PostgreSQL. You can also find free conversion tools, such as SQLines and SQL Workbench/J, which support a lot of the major databases.

Each tool has its own set of strengths and limitations, and the selection often depends on the specific requirements of your database migration project. For instance, Ispirer MnMTK and SQLines may be suitable for large-scale and complex migrations, while SQL Workbench/J and Oracle SQL Developer are often preferred for smaller scale migrations or migrations centered around specific database technologies.

However, it's worth emphasizing that while automated conversion tools like AWS Schema Conversion Tool and its alternatives can be a good starting point, they cannot replace the expertise, judgment, and experience of a human dealing with database migrations. Automated tools can undoubtedly help in kick-starting the migration process, detecting common code conversion patterns, or automating the bulk of the code conversion that follows some standard patterns. But these tools often fall short in handling database specifics, ambiguous references and complex queries which often require human intervention.

Therefore, it is recommended to utilize these automated tools in conjunction with the knowledge and skill of an experienced database migration professional, who can handle the challenges that these tools may not be coded to address. Only then can you achieve a smooth and efficient database migration process.

Conclusion 

We hope that this comprehensive review of AWS Schema Conversion Tool has provided you with useful insights to help your decision-making process when assessing migration tool options. While automation can accelerate certain aspects of the process, the expertise and skill set of experienced professionals remain invaluable. If you require professional assistance during any stage of your database migration process, do not hesitate to reach out to us.