Modify Procedural Code


In this part, you will modify/review the procedural code that Schema Conversion Tool could not automatically convert for the target database dialect.

  1. From the left panel, click on the Stored Procedures. Next, click on the generate_tickets procedure.

    \[SqlSct13-PG\]

  2. Observe how the SCT highlights the issue(s), stating that for PostgreSQL this procedure has 2 potential issues. First is PostgreSQL is case-senstitive and SCT converted the compares to include LOWER function. Please review these to verify.

    MS SQL Server syntax:

         SELECT ......
         ,(CASE 
             WHEN seat.seat_type = 'luxury' THEN 3*@standard_price
             WHEN seat.seat_type = 'premium' THEN 2*@standard_price
             WHEN seat.seat_type = 'standard' THEN @standard_price
             WHEN seat.seat_type = 'sub-standard' THEN 0.8*@standard_price
             WHEN seat.seat_type = 'obstructed' THEN 0.5*@standard_price
             WHEN seat.seat_type = 'standing' THEN 0.5*@standard_price
          END ) ticket_price
          .....
    

    PostgreSQL syntax leveraging LOWER to ensure compare operation works for most situations even with the difference in case-senstitive differences between the database engines:

         SELECT ......
         ,(CASE 
            WHEN LOWER(seat.seat_type) = LOWER('luxury') THEN 3 * var_standard_price
            WHEN LOWER(seat.seat_type) = LOWER('premium') THEN 2 * var_standard_price
            WHEN LOWER(seat.seat_type) = LOWER('standard') THEN var_standard_price
            WHEN LOWER(seat.seat_type) = LOWER('sub-standard') THEN 0.8 * var_standard_price
            WHEN LOWER(seat.seat_type) = LOWER('obstructed') THEN 0.5 * var_standard_price
            WHEN LOWER(seat.seat_type) = LOWER('standing') THEN 0.5 * var_standard_price
          END ) ticket_price
          .....
    

    The second issue has to do with the newid() function which needs the uuid-ossp extension in PostgreSQL which is included in RDS PostgreSQL.

    MS SQL Server syntax:

           -- randomly generated standard price between 30 and 50 dollars
            DECLARE @standard_price SMALLMONEY = ROUND(ABS(CHECKSUM(NewId())) % 20 + 30 + rand(),2);
    

    PostgreSQL syntax SCT added the newid() function in the aws_sqlserver_ext schema to ensure the function works:

             /* randomly generated standard price between 30 and 50 dollars */
                var_standard_price NUMERIC(10, 4) DEFAULT ROUND(ABS(aws_sqlserver_ext.checksum(aws_sqlserver_ext.newid()::TEXT)) % 20 + 30 + RANDOM(), 2);
    
  3. Thus you don’t have to make any modifications and you can take the changes SCT has made on those objects.

  4. Right click on the dms_sample_dbo database in the left panel and select Convert Schema to generate the data definition language (DDL) statements for the target database.

    \[SqlSct15-PG\]

  5. When warned that objects may already exist in database, click Yes.

    \[SqlSct16-PG\]

You may be presented with a different message stating “The operation will be partially performed” and only 3 of the 8 objects will be converted. Click OK.

  1. Right click on the dms_sample_dbo schema in the right-hand panel, and click Apply to database.

    \[SqlSct17-PG\]

  2. When prompted if you want to apply the schema to the database, click Yes.

    \[SqlSct18-PG\]

  3. At this point, the schema has been applied to the target database. Expand the dms_sample_dbo schema to see the tables.

    \[SqlSct19-PG\]

You have sucessfully converted the database schema and object from Microsoft SQL Server to the format compatible with Amazon Aurora (PostgreSQL).

This part demonstrated how easy it is to migrate the schema of a Microsoft SQL Server database into Amazon Aurora (PostgreSQL) using the AWS Schema Conversion Tool. Similarly, you learned how the Schema Conversion Tool highlights the differences between different database engine dialects, and provides you with tips on how you can successfully modify the code when needed to migrate procedure and other database objects.

The same steps can be followed to migrate SQL Server and Oracle workloads to other RDS engines including PostgreSQL and MySQL.

The next section describes the steps required to move the actual data using AWS DMS. Read on to learn more…