AWS Database Blog

AWS Schema Conversion Tool introduces new features in build 616

We are excited to introduce a new version of AWS Schema Conversion Tool (AWS SCT) that includes support for PostgreSQL 10 partitioning, a new server-level Assessment Report, support for table-valued functions, and more.

For those of you who are new to AWS SCT, this tool helps convert your existing database schema from one database engine to another. You can convert from a relational OLTP schema or any supported data-warehouse OLAP schema to Amazon RDS. For example, you can convert to Amazon Aurora with MySQL compatibility or PostgreSQL compatibility, among others. You can also convert from a relational OLTP schema or a supported data-warehouse OLAP schema to Amazon Redshift. Find all the supported sources and targets in our documentation.

Following is a quick overview of the topics we cover in this blog post:

  1. Changes to the SCT Assessment Report
    • Oracle to Amazon RDS for Oracle
    • Microsoft SQL Server to Amazon RDS for SQL Server
  2. Table-valued functions implementation
    • SQL Server to PostgreSQL
    • SQL Server to MySQL
  3. Support for PG10 partitioning for these sources:
    • Db2 LUW
    • Oracle
    • SQL Server

The new features listed are just a subset of the capabilities in build 616. For a full list, see this release notes section.

Changes to the SCT Assessment Report

Usage of certain server-level objects and features of Oracle and SQL Server can be different in Amazon RDS. To make it simpler to understand what features might or might not be supported, SCT now analyzes server-level objects configured on your source Oracle or SQL Server instance and creates a new server-level Assessment Report. It identifies server-level objects and features being used on the source instance and provides an estimate of how many can be converted automatically if you select Amazon RDS as your migration target.

Let’s look at the steps needed to create an Assessment Report on the server-level objects:

  1. Create a new project and connect to the source (Oracle or SQL Server) and target (RDS for Oracle or RDS for SQL Server).
  2. After you connect, SCT displays the server-level objects it finds on the left side.
  3. Open the context (right-click) menu for the server-level objects, and choose Create Report, as shown following.
  4. Review the server-level Assessment Report, take care of any issues, and apply the conversion to the target.

Oracle to RDS for Oracle

Let’s look at an Oracle to RDS for Oracle conversion. SCT now analyzes server-level objects such as directory objects, tablespaces, user roles and privileges, and virtual private database policies. It also looks at the hardware configuration and other features like licenses, RAC and RAC One Node, Data Guard and Active Data Guard, database vault, spatial and locator features, schedules, and auditing. It provides input on whether these features are supported in the target version or not.

For example, Oracle stores data logically in tablespaces and physically in data files associated with the corresponding tablespace. In Oracle, you can create a tablespace with data file names.

However, Amazon RDS supports Oracle Managed Files (OMF) for data files, log files, and control files. When you create data files and log files in RDS, you can’t specify the physical file names. File name parameters in DATAFILE and TEMPFILE clauses must be removed.

Oracle       
CREATE TEMPORARY TABLESPACE tbs_test_temp
    TEMPFILE 'tbs_test_temp.dat'
    SIZE 5M
    EXTENT MANAGEMENT LOCAL UNIFORM size 1M;       
    
 Oracle RDS  
CREATE TEMPORARY TABLESPACE tbs_test_temp
 TEMPFILE 
/*AI 1512 
RDS supports Oracle Managed Files only. Custom file name cannot be given in RDS. 
*/
/*'tbs_test_temp.dat'*/
    SIZE 5242880
    FLASHBACK ON 
    EXTENT MANAGEMENT LOCAL
    UNIFORM SIZE 1048576;

Following is a sample Assessment Report for an Oracle to RDS for Oracle migration.

SQL Server to RDS for SQL Server

With SQL Server as a source, SCT analyzes server-level objects. The analysis includes discovering the maximum number of databases per instance, maximum size of DB instance, replication setup, service broker, jobs, operator, linked server, backup device, alerts, and endpoints. Using this information, SCT generates a server-level Assessment Report. It also looks at features unavailable in the target version and reports them.

For example, you can create up to 30 databases on each of your DB instances running SQL Server on Amazon RDS. If the source DB instance contains more databases, then SCT displays information about this restriction in the Assessment Report.

Here is a sample Assessment Report for a SQL Server to RDS for SQL Server conversion.

Table-valued functions

Table-valued functions have been around since SQL Server 2005. A table-valued function is a function that returns a table. You can use this type of function in place of a table, or you can join it with another table in a SQL statement.

Conversion of table-valued functions was one of the top requests from our customers. From this version of SCT forward, we support this functionality for migrations from SQL Server to PostgreSQL and from SQL Server to MySQL.

SCT emulates the behavior of inline table-valued functions by creating temporary tables. Following are a couple of examples to help clarify the conversion.

The following shows SQL Server to PostgreSQL. The following shows SQL Server to MySQL.

Support for partitioning in PostgreSQL 10

A partitioned table uses a data organization scheme in which table data is divided across multiple storage objects, called data partitions or ranges. These are divided according to the values in one or more table-partitioning key columns of the table.

Starting with version 10, PostgreSQL offers a way to specify how to divide a table into partitions. The table that is divided is referred to as a partitioned table. The specification consists of the partitioning method and a list of columns or expressions to be used as the partition key. Currently supported partitioning methods include range and list, where each partition is assigned a range of keys and a list of keys, respectively.

PostgreSQL has several implementations for table partitioning:

  • Declarative partitioning (available with version 10 and later)
  • Implementation using inheritance

Starting in version 615, SCT supports the partitioning feature of PostgreSQL for both Oracle and SQL Server as source databases. We have also added support for Db2 LUW starting with version 616.

Db2 to PostgreSQL

Each database engine offers several flavors of partitioning. PostgreSQL manages partitioned tables differently from Oracle, SQL Server, and Db2. Knowing the differences in partitioning schemes between the databases can help you understand how AWS SCT converts partitioned tables from Oracle, SQL Server, and Db2 to PostgreSQL.

Following are some of the differences between Db2 and PostgreSQL:

  • PostgreSQL doesn’t support NULL values for RANGE partitioning.
  • Db2 has the special clauses INCLUSIVE and EXCLUSIVE for range boundary values, which set whether this value should be included in the data partition containing this boundary. PostgreSQL only supports INCLUSIVE for a starting boundary and EXCLUSIVE for an ending boundary.
  • Db2 enables you to create primary or unique keys for partitioned tables. For PostgreSQL, you create a primary or unique key for each partition directly.
  • Db2 enables you to create foreign key constraint from and to a partitioned table. For PostgreSQL, foreign keys referencing partitioned tables aren’t supported. Neither are foreign key references from a partitioned table to another table.
  • Db2 enables you to create indexes and row triggers for partitioned tables. For PostgreSQL, you create indexes and row triggers for each partition directly.

Following is an example of how SCT converts a partitioned table in Db2 to PostgreSQL.

The following shows single-column range partitioning.The following shows multicolumn range partitioning.

Oracle to PostgreSQL

Following are some differences in partitioning schemes between Oracle and PostgreSQL:

  • Oracle sets only the upper bound for range partitions and subpartitions. In PostgreSQL, both bounds must be set.
  • For a date range, PostgreSQL only supports literals for bound values. Thus, all bound values from Oracle must be converted to suitable text literals.
  • Oracle enables you to use a DEFAULT special value for partition bounds storing any unspecified values directly to that partition or subpartition. PostgreSQL doesn’t support this feature.
  • Oracle enables you to partition tables by column without a NOT NULL constraint. In that case, all NULL values go to the rightmost partition. PostgreSQL doesn’t support such a strategy.
  • Oracle enables you to create a foreign key constraint from and to a partitioned table. For PostgreSQL, foreign keys referencing partitioned tables aren’t supported. Neither are foreign key references from a partitioned table to another table.
  • Oracle can move data between partitions if a change to a partition column value causes a row to move from one partition to another. In this case, however, PostgreSQL can fail, because it’s possible that the new value of the row fails to satisfy the implicit partition constraint of the original partition.

Following are a couple of examples that demonstrate the automatic conversion of partitioned tables in Oracle to PostgreSQL.

The following shows list partitioning.

The following shows range partitioning.

SQL Server to PostgreSQL

For SQL Server, partitioning is built with partition functions. For each function, the number of partitions created is equal to n + 1. The values don’t have to be listed in order. If the values aren’t in order, the database engine sorts them.

PostgreSQL supports only literals as values for boundary values. Original datetime boundary values are converted to suitable text literals.

Following are some differences in partitioning schemes between SQL Server and PostgreSQL:

  • PostgreSQL doesn’t support inclusive logic for an upper bound.
  • SQL Server enables you to partition a table by column without NOT NULL constraint. In that case, all NULL values go to the leftmost partition. PostgreSQL doesn’t support NULL values for range partitioning.
  • SQL Server enables you to create primary or unique keys for partitioned tables. For PostgreSQL, you create a primary or unique key for each partition directly.
  • SQL Server enables you to create a foreign key constraint from and to a partitioned table. For PostgreSQL, foreign keys referencing partitioned tables aren’t supported. Neither are foreign key references from a partitioned table to another table.
  • SQL Server enables you to create indexes for partitioned tables. For PostgreSQL, index should be created for each partition directly. Indexes must be removed from the parent table.

Conclusion

In addition to the features we discuss earlier in this post, build 616 delivers other UI improvements. You can access the entire feature list on the release notes page.

We believe that the new server-level Assessment Report should make planning your schema conversion and migration to RDS much easier. Support for partitioning also helps reduce the amount of manual work needed to migrate partitioned tables to PostgreSQL.

As always, feel free to drop us questions or feedback in the comments section. Happy migrating!

About the Author

Ramya Kaushik is a database engineer with the Database Migration Service (DMS) & Schema Conversion Tool (SCT) team at Amazon Web Services.