Part 1 - Use AWS SCT to Convert Schema

The first step of Database migration is to use the AWS Schema Conversion Tool (SCT) to convert the Microsoft SQL Server schemas to a PostgreSQL compatible schema.

Fetch Database Information

For this lab, the target database - a PostgreSQL compatible Aurora Database - was created for you. To connect, you will need the name and login credentials.

  1. In the AWS Management Console, navigate to RDS. Among the list of databases, there is a database named “inventorydb” with engine type “Aurora PostgreSQL”. Click on the “inventorydb” to access detailed info.

    Contents

  2. In the endpoints section, please note down the endpoint name of the instance of type “Writer” in your cheat sheet (You should paste it under “Aurora Database Writer Endpoint”). It will be used multiple times in subsequent steps.

    Contents

Configure AWS Schema Conversion Tool (SCT)

  1. In the developer desktop, start the AWS SCT application by opening the start menu and searching for AWS Schema Conversion Tool. Click on the application. Accept the terms of services in the pop up window during the process.

    Contents

  2. A new project wizard will show up when the app is opened. Select “Microsoft SQL Server” for the source engine field, and click “Next”.

    Contents

  3. Next, enter the info and credentials required to connect to the source database:

    • Server name: Enter db.unishop.local

    • Server port: Enter 1433

    • Instance name: Enter unishop

    • User name: Enter admin

    • Password: Enter dMdLgX6sZoXmOU2rnWTS

    Contents

  4. The Microsoft SQL Server driver path on the bottom is still blank. The driver is predownloaded for you in the path below. Copy and paste it into the input box:

    C:\jdbc\Microsoft JDBC Driver 6.0 for SQL Server\sqljdbc_6.0\enu\jre8\sqljdbc42.jar
    
  5. Cick the “Test connection” button in the bottom-left corner of the window, and you will see a pop up window saying the connection was successful.

    Contents

  6. Click OK and go to the next step. Dismiss the security warning window by selecting “Accept the risk and continue”. Then SCT will connect to our source database. Dismiss the warning message about metadata in the process.

    Contents

  7. On the next screen, select the “Unishop” database, and click “Next.”

    Contents

  8. An assessment report will be generated. Only a part of the report is related to our purpose, which you will have another chance to view in detail later in the process. Hence, for now, just skip it by clicking “Next.”

  1. On the next screen, enter the info and credentials to connect to the Aurora databse as shown in the screenshot below:

    • Target engine: Select “Amazon Aurora (PostgreSQL compatible)”

    • Server name: Enter the value of “Aurora Database Writer Endpoint” in your cheat sheet. (If you forget to copy this value to your cheat sheet, please refer to this section.)

    • Server port: Enter 5432

    • Database: Enter inventorydb

    • User name: Enter postgres

    • Password: Enter mYRyeT1IJamnWxAYowyR

    Contents

  2. Copy and paste the following string into the Amazon Aurora (PostgreSQL compatible) driver path field:

    C:\jdbc\postgresql-42.2.14.jar
    
  3. Click the “Test connection” button in the bottom-left corner of the window. As before, you will see a pop up window saying connection is successful.

  4. Click “Finish.” The same security warning will pop up again. As before, dismiss it by selecting “Accept the risk and continue.”

  5. Now you have access to the main interface of SCT, which is divided into three vertical sections. The leftmost section, in the red box, is the content of our source Microsoft SQL Server database. The middle section, in the yellow box, displays detailed information about a specific schema element. The rightmost section, in the green box, displays the content of the target database.

    Contents

  6. Next, let’s consider which tables in our source database should be migrated. Because the basket and user services were already extracted, these tables in the Microsoft SQL Server database are no longer necessary. Therefore, you only need to migrate the unishop inventory table to our target Aurora databse.

  7. To locate the unishop inventory table in the source database, navigate to “Unishop” > “Schemas” > “dbo” > “Tables” > “inventory”. Now you can see the columns of the inventory table displayed in the middle section. It stores various information about each unicorn item the Unishop has to offer.

    Contents

  8. In the leftmost section, right click on the “inventory” table, and select “Convert schema”. If a pop-up window shows up asking you whether you want to replace the existing schema in the target database, click “Yes”.

    Contents

  9. After the conversion is completed, the interface will display information as shown in the below screenshot. Note that new entries appear in the rightmost target database section. The selected inventory table by default corresponds to the inventory table that you just selected to convert (both circled in red). The middle section displays the inventory’s schema in source and target database. Pay attention to the attribute circled in blue: you will find that the computed column “year_model” in the source Microsoft SQL Server database becomes a normal integer attribute in the target database.

    Contents

  10. To understand what happened, in the leftmost section, right click on the “Unishop” entry, and select “Create report.”

    Contents

  11. A report is generated, the last section of which is a graph that shows the convertability of items from the source database to the target database. Note that everything except for one of the three tables in the source database are all automatically convertable to the target database. However, the inventory table you just converted does require some simple workaround to be converted, and the SCT has already automatically performed those actions for you during the conversion process.

    Contents

  12. To see what exactly the SCT did to convert the inventory table, click on the “Action items” tab in the top left corner of the window.

    Contents

  13. In the middle section, an issue is displayed, which says a computed column is replaced by a trigger. You can see more details about the issue by expanding it. You will see computed column “year_model” in the source database is replaced by a trigger during conversion, which explains why the “year_model” column became a normal integer attribute after conversion in step 13.

    Contents

  14. If you want to learn more about the auto generated trigger, you can expand the “Trigger functions” directory under the converted “unishop_dbo” entry in the rightmost section. In it, there’s a trigger function named “fn_tr_inventory_biu”. Clicking on the function name displays the definition of the function in the middle section. In short, it computes and assigns the value of the computed column before each insertion to the table.

    Contents

  15. After successfully converting the schema, you need to apply the converted schema to the target database. Select and right click on the “unishop_dbo” entry in the rightmost target database section. Select “Apply to database.” In the pop up windown asking for confirmation, click “yes”.

    Contents Contents

  16. Now the converted schema is in the target Aurora database, but there’s no data in it. In the next part of the lab, we will migrate the inventory data from the monolith database to it.