How to use Toad® developer tools to migrate to an open source database – 2

Open source databases are becoming more and more popular. MySQL and PostgreSQL databases are the top two open source relational databases and their use is on the increase.  Migrating to these databases from a commercial database requires data type conversions, and SQL syntax conversions.

In the first article in this three-part series, we discussed steps 1-4 (highlighted in grey below), creating tables in SQL Server 2017. Subsequently we created a model in the Quest® developer tool Toad® Data Modeler and reverse engineered the tables to the model.  The same SQL syntax and data types that SQL Server supports cannot be used with MySQL or PostgreSQL. 

In this continuation article we shall discuss seps 5-11: converting the model to open source database MySQL 5.7, generating DDL for the model, and running the DDL in the developer tool Toad Edge to create tables in MySQL 5.7 (highlighted in yellow below).

How do you migrate to an open source database?

The complete migration procedure involves the following sequence:

1. Set the environment
2. Create SQL Server Express Edition Tables in Toad for SQL Server
3. Create Model for SQL Server Express Edition
4. Reverse Engineer SQL Server Express Edition Tables
5. Create Connection to MySQL in Toad Edge
6. Convert Model to MySQL
7. Verify the MySQL Model
8. Modify Default Settings for Generating DDL for MySQL
9. Generate DDL for MySQL
10. Run DDL to create MySQL Tables
11. Describe MySQL Tables
12. Create Connection to PostgreSQL in Toad Edge
13. Convert Model to PostgreSQL 10
14. Modify Default Settings for Generating DDL
15. Generate DDL for PostgreSQL 10
16. Run DDL to create PostgreSQL 10 Tables
17. Describe PostgreSQL 10 Tables

As mentioned in the first blog, you must first set the environment and download and install the following developer tool software:

• Toad for SQL Server
• Toad Data Modeler
• Toad Edge
• MySQL 5.7 Database
• PostgreSQL 10 Database

 

Creating a connection to the open source database MySQL in Toad Edge

To be able to migrate SQL Server tables to MySQL we would need to connect to MySQL with the developer tool Toad Edge and run MySQL-specific DDL to create the tables. In this section we shall connect to a MySQL 5.7 database running on the local machine. Click on Connect>New Connection in Toad Edge as shown in Figure 1.

Figure 1. Connect>New Connection

 

In New Connection wizard select Database Platform as MySQL and click on Next as shown in Figure 2.

Figure 2. Selecting Database Platform

 

Next, configure connection settings as shown in Figure 3.  Set Hostname to localhost, Port to 3306 and Database to mysql. Set Username to root and specify the password for root. Click on Test Connection to test the connection. A  Connection is OK message shown in Figure 3 indicates that a connection gets established. Click on Next.

Figure 3. Connection is OK

 

In Connection Details a Connection Name field value is preset. Optionally shorten the connection name (mysql), select the Enable Autocommit checkbox and click on Next as shown in Figure 4. Click on Finish.

Figure 4. Enabling Autocommit

 

A new connection gets created and added to Connections view as shown in Figure 5. The Object Explorer displays the Databases, and Users. The mysql database is selected as the default or active open source database as indicated by the blue-green color coding.

Figure 5. New MySQL Connection

Converting model to MySQL

In this section we shall convert the model developed for SQL Server 2017 to MySQL 5.7 model. Select Run Convert in the toolbar in the developer tool Toad Data Modeler as shown in Figure 6.

Figure 6. Run Convert

 

In Model Conversion select target database platform as MySQL>MySQL 5.7 as shown in Figure 7.

Figure 7. Selecting Model to Convert to as MySQL 5.7

 

With the target database platform selected as MySQL as shown in Figure 8 click on Next.

Figure 8. Target>Next

 

In Conversion Settings  the default settings are listed. The only option selected is Comment Out Database Specific Items. Select all three options and click on Next as shown in Figure 9.

Figure 9. Conversion Settings

 

In Select Object Types select the default setting to Convert All as shown in Figure 10.

Click on Next.

Figure 10. Select Object Types

 

The Converter starts to convert the model. In Items Selection the model properties that shall get converted are displayed as shown in Figure 11. The checkbox in the margin is used to select the items to convert. All Users, Entities, Relations are shown to be selected. Click on Next.

Figure 11. Selecting Items to Convert

 

In Review the number of items for each type in the SQL Server 2017 model that are to be converted and committed are listed as shown in Figure 12. Specify a New Model Name and click on Finish.

Figure 12. Review

 

The Converter runs to completion to convert the model.

The new model MySQL 5.7 gets created as shown in Figure 13. The Entities in the converted model are Log and LogEntry, which are the same as in the SQL Server 2017 model.  The Relations are also the same as in the SQL Server 2017 model. The entity relation diagram for the new model also gets displayed. The data types in the MySQL model are the same as in the SQL Server model but if the same data types were not supported the data types would also have got converted.

Figure 13. New Model MySQL 5.7

 

Verifying the MySQL model

Having converted the model to MySQL 5.7 we shall verify the model next. Click on Model>Verify Model as shown in Figure 14.

Figure 14. Model>Verify Model

 

The Model Verification window displays the verification settings as shown in Figure 15. Click on Save & Verify.

Figure 15. Model Verification

An Information dialog indicates that verification has completed without any errors. Click on Close in Model Verification.

 

Modifying default settings for generating DDL for MySQL

The default settings for generating DDL may be modified if needed. We need to modify at least one setting. Select Model>Generate DDL Script>Edit Default Settings… as shown in Figure 16.

Figure 16. Model>Generate DDL Script>Edit Default Settings…

 

In What to Generate the Location of SQL File field has the default directory path and file name set for the generated DDL as shown in Figure 17.

Figure 17. Default Location of SQL File

 

We need to modify the suffix to lowercase .sql as shown in Figure 18 because the default uppercase suffix is not supported by Toad Edge when opening the script. The generated file name may be modified optionally. Click on OK.

Figure 18. Modifying Default Generated File Name

 

Generating DDL for MySQL

To generate DDL from the converted model select Model>Generate DDL Script>Run… as shown in Figure 19.

Figure 19. Model>Generate DDL Script>Run…

 

The DDL Script Generation of MySQL 5.7 window gets displayed as shown in Figure 20. Click on Generate to generate the script.

Figure 20. DDL Script Generation of MySQL 5.7>Generate

 

The DDL script for MySQL gets generated as indicated by log message in Figure 21.

 

Figure 21. SQL Generation finished successfully

 

The SQL Script generated is listed:

/*

Created: 11/10/2018

Modified: 11/10/2018

Model: MySQL 5.7

Database: MySQL 5.7

*/

 

 

— Create tables section ————————————————-

— Table Log

CREATE TABLE `Log`

(

  `MSG_CODE` Varchar(20) NOT NULL,

  `DESCRIPTION` Varchar(255)

)

;

ALTER TABLE `Log` ADD PRIMARY KEY (`MSG_CODE`)

;

— Table LogEntry

CREATE TABLE `LogEntry`

(

  `MSG_CODE` Varchar(20) NOT NULL,

  `CODE` Varchar(20) NOT NULL,

  `TIMESTAMP` Varchar(55),

  `MSG` Varchar(255)

)

;

ALTER TABLE `LogEntry` ADD PRIMARY KEY (`MSG_CODE`,`CODE`)

;

— Create foreign keys (relationships) section ————————————————- 

ALTER TABLE `LogEntry` ADD CONSTRAINT `Log-LogEntry` FOREIGN KEY (`MSG_CODE`) REFERENCES `Log` (`MSG_CODE`) ON DELETE NO ACTION ON UPDATE NO ACTION

;

ALTER TABLE `LogEntry` ADD CONSTRAINT `Log-LogEntry-2` FOREIGN KEY (`CODE`) REFERENCES `Log` (`MSG_CODE`) ON DELETE NO ACTION ON UPDATE NO ACTION

;

 

If compared with the DDL script used to create SQL Server 2017 tables in the first article (PLEASE ADD LINK) the script is markedly different.

Running DDL to create MySQL tables

 

Next, we shall run the generated DDL script in Toad Edge to create MySQL tables. Copy the generated DDL to a SQL Worksheet in Toad Edge. Select Execute All Statements as shown in Figure 22.

Figure 22. Execute All Statements

The 6 statements get processed.

 

Describing MySQL tables

The MySQL tables generated in the open source database may be described with DESC command. In a SQL Worksheet copy the following DESC statements.

DESC Log;

DESC LogEntry;

Position the cursor at the beginning of the DESC statement to run and click on Execute SQL Statement as shown for the DESC Log; statement in Figure 23.

Figure 23. Running DESC Statement

 

The table description for the Log table gets displayed as shown in Figure 24.

Figure 24. Table Description for Log Table

 

Similarly list the table description for the LogEntry table as shown in Figure 25.

Figure 25. Table Description for LogEntry Table

 

Summary

In this continuation article about migrating to an open source database we Toad developer tools and converted a model that we created in Toad Data Modeler for SQL Server 2017 to a MySQL 5.7 model. Subsequently we generated the DDL for the MySQL model. We ran the generated DDL in Toad Edge to create tables in MySQL 5.7. We have demonstrated migrating SQL Server 2017 tables to MySQL 5.7 database. In a subsequent article we shall discuss migrating the same SQL Server 2017 tables to PostgreSQL 10. 

 

More blogs on Toad World

Blog: How to use Toad® developer tools to migrate to an open source database – 1

Blogs about Toad for SQL Server

Blogs about Toad Data Modeler

Blogs about Toad Edge

More blogs about PostgreSQL

More blogs about MySQL

 

Help your colleagues

If you think your colleagues would benefit from this blog, share it now on social media with the buttons located at the top of this blog post. Thanks!