AWS Database Blog

AWS Schema Conversion Tool blog series: Introducing new features in build 617

We are excited to introduce a new version of the AWS Schema Conversion Tool (AWS SCT). This version includes support for table-valued function conversions, additional information in server-level assessment reports, 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. You can find all the supported sources and targets in the AWS SCT documentation.

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

  • Microsoft SQL Server to PostgreSQL – Table-valued functions conversion
  • SQL Server to PostgreSQL/MySQL – Table type variables conversion
  • SQL Server to PostgreSQL – MERGE statement implementation
  • Oracle to PostgreSQL 10 – Support for converting timestamp without time zone data types as partition by column
  • Oracle to Amazon RDS for Oracle – Additional server-level objects
  • SQL Server to Amazon RDS for SQL Server – Additional server-level objects

Let’s take a look at these features in detail.

SQL Server to PostgreSQL – Table-valued functions

In AWS SCT version 616, multi-statement table-valued function emulation used a temporary table to access the data. AWS SCT first created and populated a temporary table and then accessed the temporary table instead of accessing the table-valued function.

In version 617, a multi-statement table-valued function returns a table with a structure similar to the original table-valued function. A temporary table is used only inside a multi-statement table-valued function to emulate all the possible DML that can be used in SQL Server. A temporary table outside the multi-statement table-valued function is not used.

The following is an example that demonstrates this change:

SQL Server table type implementation

User-defined table types are created as database objects and are defined by a list of attribute names and data types. You can use table-valued parameters declared as a user-defined table type to send multiple rows of data to a Transact-SQL statement or a routine (such as a stored procedure or function), without creating a temporary table or many parameters.

Target: PostgreSQL and MySQL

PostgreSQL and MySQL don’t support user-defined table types, but you can simulate the feature by using temporary tables.

The following example procedure and table type use a SELECT statement that was created in a SQL Server source.

Source (SQL Server) table type and procedure:

CREATE TYPE Employee2Client AS TABLE(
    EmployeeID INT
  , EmployeeName VARCHAR(160)
  , ClientID INT
  , ClientName VARCHAR(160)
)
CREATE PROCEDURE [dbo].[PROC_CREATE_PROC_005]
  @p_E2C Employee2Client READONLY
AS
BEGIN
  SELECT *
    FROM @p_E2C
   WHERE UPPER(ClientName) 
     LIKE CONCAT('%', UPPER('bank'), '%');
END;

Target: PostgreSQL

The following objects are created in PostgreSQL to emulate table types:

  • Composite type
  • Domain: Declared as an array of composite type
  • Function: Creates a temporary table

AWS SCT converted code (PostgreSQL): 

CREATE TYPE ramya_dbo.employee2client$aws$t AS (
employeeid NUMERIC(10,0),
employeename VARCHAR(160),
clientid NUMERIC(10,0),
clientname VARCHAR(160)
);

CREATE DOMAIN ramya_dbo.employee2client AS 
    ramya_dbo.employee2client$aws$t [] NOT NULL;

CREATE OR REPLACE FUNCTION ramya_dbo.employee2client$aws$f
(IN variable_name VARCHAR)
RETURNS void
AS
$BODY$
BEGIN
    EXECUTE 'DROP TABLE IF EXISTS ' || variable_name;
    EXECUTE 'CREATE TEMPORARY TABLE ' 
    || variable_name || ' ramya_dbo.employee2client$aws$t WITH OIDS;';
END;
$BODY$
LANGUAGE  plpgsql;


CREATE OR REPLACE FUNCTION ramya_dbo.proc_create_proc_005
(IN par_p_e2c ramya_dbo.employee2client, OUT p_refcur refcursor)
AS
$BODY$
BEGIN
    OPEN p_refcur FOR
    SELECT
        *
        FROM par_p_e2c$aws$tmp
        WHERE LOWER(UPPER(clientname))
             LIKE LOWER(CONCAT('%', UPPER('bank'), '%'));
END;
$BODY$
LANGUAGE  plpgsql;

The following image shows the objects that were created by AWS SCT on the target database to mimic table types. Here you can see the SQL Server table type Employee2Client has been converted to PostgreSQL.

Target: MySQL

MySQL does not support parameters of table types, but you can simulate this feature through temporary tables. Temporary tables are deleted after you disconnect a session in MySQL, so you have to create them every time you connect.

AWS SCT converted code (MySQL):

CREATE PROCEDURE RAMYA_dbo.PROC_CREATE_PROC_005(in par_p_E2C VARCHAR(255))
BEGIN
    DROP TEMPORARY TABLE IF EXISTS par_p_E2C$PROC_CREATE_PROC_005;
    SET @aws$tbl$stmt := 
    CONCAT('CREATE TEMPORARY TABLE par_p_E2C$PROC_CREATE_PROC_005 
    SELECT * FROM ', par_p_E2C);
    PREPARE aws$tbl$stmt FROM @aws$tbl$stmt;
    EXECUTE aws$tbl$stmt;
    DEALLOCATE PREPARE aws$tbl$stmt;
    SELECT
        *
        FROM par_p_E2C$PROC_CREATE_PROC_005
        WHERE UPPER(ClientName) LIKE CONCAT('%', UPPER('bank'), '%');
END;

The following example shows how AWS SCT converted the SQL Server procedure (PROC_CREATE_PROC_005) to MySQL.

SQL Server to PostgreSQL – MERGE statement implementation

Merge in SQL Server performs insert, update, or delete operations on a target table based on the results of a join with a source table. PostgreSQL does not support the MERGE statement, but you can simulate it by using the INSERT ON CONFLICT clause.

Here is an example of a procedure using MERGE with only one WHEN NOT MATCHED clause.

AWS SCT also supports other kinds of merge statements, for example:

  • MERGE from Table
  • MERGE from View
  • MERGE from Subquery
  • MERGE without WHEN MATCHED clause
  • MERGE without WHEN NOT MATCHED clause
  • MERGE with INSERT and without field list

The following are some limitations:

  • Complex merge ON condition
  • Two WHEN MATCHED THEN clauses (currently not supported)
  • WHEN NOT MATCHED BY SOURCE clause (currently not supported)

Oracle to PostgreSQL 10 – Converting timestamp without time zone data types as partition by column

Starting with this version, AWS SCT supports migrating partition columns with the timestamp without time zone data type when migrating from Oracle to PostgreSQL 10.

The following example shows a partitioned table in Oracle whose partition key is Call_Date of data type timestamp. AWS SCT converts this table into a partitioned table on call_date and creates four partitions in the PostgreSQL target corresponding to the Oracle partitions.

Oracle to Amazon RDS for Oracle – Additional server-level objects

We introduced the server-level assessment report in the previous release of AWS SCT. In this release, we have added quite a few new objects to the assessment report.

The following are some of the server-level objects that can be helpful when you assess your target database. For the full list, see the AWS SCT release notes.

  • DB instance limitations
  • Character set
  • Data Guard and Active Data Guard
  • Automatic Storage Management
  • Replication
  • Enterprise User Security
  • Streams
  • XML DB
  • Oracle Application Express (APEX)
  • Redo log generation

The following are a couple of snippets from the server-level assessment report. In the first example, the report informs you that Amazon RDS for Oracle doesn’t support replication and lets you know what your alternatives are.

Replication:
Amazon RDS for Oracle doesn’t support replication
Assessment report: Amazon RDS for Oracle doesn’t support PL/SQL packages
 for change data capture. Please try Amazon RDS Multi-AZ and AWS DMS instead to
 replicate your data.

Or maybe you use the Oracle XML DB feature, and the report lets you know what is not supported and provides documentation related to it.

Oracle XML DB Protocol Server:
Assessment report: Oracle XML DB. Amazon RDS for Oracle supports XML DB feature
without the XML DB Protocol Server. Please read prerequisites and configuration
steps in the next article: Oracle XML DB option

The following example shows an actual server-level assessment report of an Oracle to RDS for Oracle conversion.

SQL Server to Amazon RDS – Additional SQL Server objects

Similar to the Oracle to RDS for Oracle report described in the previous section, we have introduced some additional objects for SQL Server to RDS for SQL Server migration.

The following are some of the new objects that are quite useful:

  • Mirroring
  • Log shipping
  • AlwaysON
  • Cluster nodes
  • Database mail
  • Reporting services
  • Analysis services
  • Integration services
  • Full-text search service
  • Data quality service

For example, your SQL Server DB instance might use database mirroring. The server-level assessment report suggests that you can use Multi-AZ deployments for the database instances in Amazon RDS. Maybe your SQL Server DB instance has SQL Server log shipping configured. The report lets you know that Amazon RDS doesn’t support log shipping.

Here is a snapshot of a SQL Server to SQL Server on Amazon RDS server-level assessment report:

Conclusion

In addition to the features we discussed in this post, build 617 delivers several other improvements. You can access the entire feature list on the AWS SCT release notes page.

We implemented these features based on customer feedback, and we appreciate all comments and suggestions. Stay tuned for the next post.

About the Author

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