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:
- Set the environment
- Create SQL Server Express Edition Tables in Toad for SQL Server
- Create Model for SQL Server Express Edition
- Reverse Engineer SQL Server Express Edition Tables
- Create Connection to MySQL in Toad Edge
- Convert Model to MySQL
- Verify the MySQL Model
- Modify Default Settings for Generating DDL for MySQL
- Generate DDL for MySQL
- Run DDL to create MySQL Tables
- Describe MySQL Tables
- Create Connection to PostgreSQL in Toad Edge
- Convert Model to PostgreSQL 10
- Modify Default Settings for Generating DDL
- Generate DDL for PostgreSQL 10
- Run DDL to create PostgreSQL 10 Tables
- 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:
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:
Model: MySQL 5.7
Database: MySQL 5.7
— Create tables section ————————————————-
— Table Log
CREATE TABLE `Log`
`MSG_CODE` Varchar(20) NOT NULL,
ALTER TABLE `Log` ADD PRIMARY KEY (`MSG_CODE`)
— Table LogEntry
CREATE TABLE `LogEntry`
`MSG_CODE` Varchar(20) NOT NULL,
`CODE` Varchar(20) NOT NULL,
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.
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
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.
Need help managing data? Try Toad for free!
Quest Software® is here to help you simplify complexity, reduce cost and risk, and drive performance. Maybe that’s why we’re consistently voted #1 by DBTA readers and have 95%+ customer satisfaction rate.
Toad® database management toolsare cross-platform solutions from Quest® that can help new and veteran DBAs, and even “accidental” DBAs manage data in Oracle, SQL Server, DB2, SAP, MySQL, and Postgres environments.
More blogs on Toad World
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!