AWS Database Blog

Migrate from Teradata to Amazon Aurora PostgreSQL with Ispirer’s SQLWays migration tool

Automated tools and services greatly reduce the costs and risks of migrating on-premises databases or data warehouses to the AWS cloud. The AWS Database Migration Service (AWS DMS) supports over a dozen database systems as sources or targets. If DMS supports your source and target databases, we recommend using AWS DMS for your migration.

There are limited cases where a source or target database may not be supported in AWS DMS. In these cases, we can look to our AWS Partner Network (APN) to provide specialized tools to support your migration. One migration path that fits this exception includes a migration from Teradata to Amazon Aurora PostgreSQL-Compatible Edition.

In this post, we demonstrate the capabilities of Ispirer’s SQLWays solution to automate a cross-database migration from Teradata to Amazon Aurora PostgreSQL.

About Ispirer

Ispirer is an AWS APN Independent Software Vendor that provides innovative solutions for highly automated cross-database migrations and human-written program code modernization. Ispirer’s SQLWays solution facilitates heterogeneous database migrations including database systems that are not supported in DMS, including the following:

Here’s a short summary of features of the Ispirer’s SQLWays solution from our review and analysis:

  • There’s a wide range of support for various source and target database systems and their languages, including legacy systems.
  • If the conversion path is not already supported, Ispirer can add an extension for it as part of a limited engagement.
  • Ispirer offers two types of licenses: basic and standard. The standard license includes customizations based on the customer’s code, which can be achieved in a few days. This optimization often delivers very high automatic conversion rates (>95%).
  • The Ispirer SQLWays tool can also convert database code to application code (ex. Oracle PL/SQL to Java).
  • Ispirer also provides professional services offering to perform the conversion for the customer, if desired.

Why Teradata to Amazon Aurora PostgreSQL?

A unique scenario may necessitate an unusual migration path that falls outside of the norm. For example, data from a Teradata database may be needed to support an operational database system running in Aurora PostgreSQL. Merging the data and consolidating systems may be the desired strategy.

The typical AWS migration target for Teradata is Amazon Redshift. Both Teradata and Amazon Redshift are massively parallel processing (MPP) database systems, however, Teradata is row-based and Amazon Redshift is columnar. Teradata is not a supported source or target in AWS DMS, but schema conversion from Teradata to Amazon Redshift is supported using AWS Schema Conversion Tool (AWS SCT) data extraction agents.

Solution overview

The solution includes Ispirer’s SQLWays tool installed on an Amazon Elastic Compute Cloud (Amazon EC2) instance. The Amazon EC2 instance connects to the on-premises Teradata appliance using an ODBC connection over AWS Direct Connect or AWS Site-to-Site VPN. The target database is an Amazon Aurora PostgreSQL database cluster running in a private subnet. The Amazon EC2 instance is hosted in a public subnet to allow a DBA to connect to it using the Remote Desktop Protocol (RDP) with a security group that limits access to the DBA’s IP address.

The following diagram illustrates the solution architecture.

The migration process from Teradata to Aurora PostgreSQL using Ispirer’s SQLWays Wizard is an example of a comprehensive and efficient solution for businesses looking to transform their database systems.

Next, we discuss the key features of the architecture.

Secure connection from on-premises to the AWS Cloud

If the Teradata database is hosted on-premises, the first consideration is to understand the options for securely connecting from the on-premises location to the AWS Cloud. If you pursue a hybrid strategy you may have an existing dedicated network connection to AWS via AWS Direct Connect. Alternatively, you can use a virtual private network (VPN) connection using AWS Site-to-Site VPN. Additionally, a TLS WebSocket feature was introduced in Teradata 17.10, which allows the ODBC driver to communicate over TLS.

SQLWays Wizard on Amazon EC2

You can install the Ispirer migration toolkit, including the SQLWays Wizard on an Amazon EC2 Windows instance. Ispirer recommends choosing an instance type with a minimum of 32GB of RAM. Sufficient RAM capacity helps optimize the performance of the SQLWays Wizard, enabling it to handle large volumes of code and data.

We recommend that you provision the instance with adequate Amazon Elastic Block Storage (Amazon EBS) storage space. During the migration process, all the extracted data from the source database will be saved in files on the local machine. You should make sure that you have enough storage capacity on the Amazon EC2 instance to store these files temporarily.

Connect SQLWays to source and target

To establish a connection with the Teradata source database, the SQLWays Wizard uses an ODBC driver. The ODBC connection provides access to the source data, procedure code, schemas, and other relevant information required for the migration process.

To establish a connection to the target Aurora PostgreSQL database, the SQLWays Wizard uses PostgreSQL-native utilities like the psql.exe command line interface (CLI), which enables the data transfer and execution of the import process.

The following diagram illustrates the database migration flow.

Image provided by Ispirer

Securing access to the Amazon EC2 instance

The Amazon EC2 instance where SQLWays is installed is hosted in a public subnet. This facilitates DBA access via RDP. The instance security group rules must be modified to allow inbound access from your IP address only to the RDP port (3389).

The following figure shows Inbound rules.

Securing access to the Aurora PostgreSQL cluster

The target Aurora PostgreSQL cluster is deployed in a private subnet. We must modify the database security group to add an inbound rule to allow traffic on port 5432 from the EC2 instance security group as a source.

Migration logging in Amazon S3

An Amazon Simple Storage Service (Amazon S3) bucket can serve as highly available and durable storage for migration logs generated by the SQLWays migration tool.

Prerequisites

You should have the following prerequisites:

  • A Teradata database populated with data that has an ODBC driver installed. Public datasets are available for Teradata including a port of Microsoft’s AdventureWorks sample database, AdventureWorksDW_Teradata.
  • An Amazon EC2 Windows instance deployed in a public subnet.
  • An Amazon Aurora PostgreSQL instance deployed in a private subnet.
  • An Amazon S3 bucket for migration logs.
  • An Ispirer SQLWays demo license. You can obtain a demo license for the SQLWays tool by contacting the Ispirer team via their website www.ispirer.com.

Set up the database migration testbench

Complete the following steps to set up your testbench.

  1. Connect to the EC2 instance and open Ispirer SQLWays tool
  2. Under Source Options, provide the ODBC connection to our source database, along with the user credentials
  3. Under Target Options, provide information about your Aurora PostgreSQL Cluster and test to ensure connectivity.
  4. Drag and drop the database objects you’d like to migrate.
  5. Specify DDL and data options by choosing data type mapping, schema name conversion and specify reserved words replacement, among other options.
  6. Review the details and confirm the selected migration options on the summary page

The sample migration project has 45 tables, 10 views, 24 stored procedures, 2 functions, and 2 triggers in scope.

Running the migration

Initiate the migration by choosing Start Conversion. The tool automatically initiates the conversion and import processes. When the migration process is finished, analyze the results. You can view the Migration Reports as shown in the following screen capture.

You can find information about the migrated objects, failed objects, and errors (if any) in the Migration Summary as shown in the following illustration.

Test the migrated DB on Aurora PostgreSQL

Now that we have a migrated version of our Teradata database in our target Aurora PostgreSQL database, we can review the results using a database tool like PGAdmin to make sure all of the objects were migrated properly.

The SQLWays Wizard can further fine-tune and even customize the migration with additional conversion rules. In addition, the Ispirer team can work with clients to iteratively optimize migration rules to maximize the rate of automated conversion.

The following diagram illustrates the migration model.

Review converted database objects

Let’s review some of the converted code from the migration.

The first example shows the view named vTimeSeries.

source
target

The following example shows a source versus target trigger.

source
target

The following example shows procedure code performing an upsert.

source
target

Clean up

To avoid unnecessary AWS charges, complete the following steps:

  1. Terminate the Amazon EC2 instance.
  2. Terminate the Aurora PostgreSQL cluster and instance.
  3. Delete the Amazon S3 bucket.

Conclusion

In this post, we demonstrated how AWS APN Partners like Ispirer can help you automate database migrations that fall outside of supported migration paths. We showed how SQLWays database migration tool performs in migrating a sample Teradata database to Aurora PostgreSQL.

Do you have a Teradata datawarehouse that you’d like to migrate to Aurora PostgreSQL? Give Ispirer’s SQLWays migration tool a try and let us know in the comments!

About the Authors

Justin Leto is a Sr. Solutions Architect at Amazon Web Services with specialization in databases, big data analytics, and machine learning. His passion is helping customers achieve better cloud adoption. In his spare time, he enjoys offshore sailing and playing jazz piano. He lives in New York City with his wife and daughter.

Aychin Gasimov is a Senior Partner Solutions Architect at AWS. He works with our customers and partners to provide guidance and technical assistance on various database migration and modernization projects.

Arpitha Vasudeva is a Technical Account Manager at Amazon Web Services with a specialization in Database services. She is enthusiastic about helping customers build resilient and cost-effective cloud solutions. In her free time, she enjoys exploring local cafes, traveling and painting.