Modify Procedural Code


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

  1. From the left panel, uncheck the items with the exclamation mark except for the generateTransferActivity procedure.

    \[SqlSct13\]

  2. Next, click on the generateTransferActivity procedure. Observe how the SCT highlights the issue, stating that MySQL does not support the PRINT procedure. To fix this, you simply need to replace the three highlighted PRINT statements with SELECT statement as demonstrated in the following example:

    MS SQL Server syntax:

    PRINT (concat('max t: ',@max_tik_id,' min t: ', @min_tik_id, 'max p: ',@max_p_id,' min p: ', @min_p_id));
    

    MySQL syntax:

    --first 2 use line below
    SELECT concat('max t: ',@max_tik_id,' min t: ', @min_tik_id, 'max p: ',@max_p_id,' min p: ', @min_p_id); 
    --third one use 
    SELECT ('Sorry, no tickets are available for transfer.');	
    
  3. After you make the modification, right-click on the dbo schema, and choose Create Report. Observe that the schema of the source database is now fully compatible with the target database.

    \[SqlSct14\]

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

    \[SqlSct15\]

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

    \[SqlSct16\]

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\]

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

    \[SqlSct18\]

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

    \[SqlSct19\]

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

This part demonstrated how easy it is to migrate the schema of a Microsoft SQL Server database into Amazon Aurora (MySQL) 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…