Modify the Procedural Code

In this part, we support modifying the procedural code for SQL Server 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.

   

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 need to replace the three highlighted PRINT statements with SELECT statement as demonstrated in the following example:

   Note: suggest you use – in front of current PRINT line to comment it out then copy and paste the correct syntax for the first 2 occurences then copy the last correct syntax for the last one. You’ll need to do this all in 1 edit session so arrow down after going to the firt line

   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.

   Note: For non-SQL Server sources (e.g. Oracle), it says step 4. However, this is really your first step on this page. Thanks for your understanding!!

   

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.

   

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

   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.

   

6. Right click on the dms_sample > dbo (SQL Server source) or dms_sample (other sources) schema in the right-hand panel, and click Apply to database.

   

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

   

8. At this point, the schema has been applied to the target database. Expand the dms_sample_dbo or dms_sample schema on the right hand panel to see the tables, views, store procedures etc.

   

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

This part demonstrated how easy it is to migrate the schema of a source 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…