How to use Toad developer tools to migrate to open source databases – 3

Quest® Toad Edge® provides an integrated development environment to connect to open source databases MySQL and PostgreSQL and run SQL scripts. In three articles we will explore migrating a commercial database SQL Server 2017 to open source databases using Toad Edge and Toad® Data Modeler developer tools. 

In the first article (steps 1-4 highlighted in grey below) we created a SQL Server 2017 instance on AWS RDS. We also created tables that are to be migrated in the SQL Server 2017. We created a model in Toad Data Modeler and reverse engineered the SQL Server 2017 tables to the model.

In the second article (steps 5-11highlighted in yellow below) we migrated the SQL Server tables to MySQL 5.7 database.

In this last of the three articles in the series (steps 12-17 highlighted in green below) we shall migrate the same SQL Server tables to PostgreSQL 10 database. Data types and SQL syntax being different, the same DDL script cannot be run on the open source databases being migrated to.  

 

How do you migrate to an open source database?

The complete migration procedure involves the following sequence and developer tools:

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 and second 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 connection to PostgreSQL in Toad Edge

To create a connection to PostgreSQL in the Toad Edge select Connect>New Connection as shown in Figure 1.

Figure 1. Connect>New Connection

 

In New Connection select Database Platform as PostgreSQL as shown in Figure 2. Click on Next.

 

Figure 2. Database Platform>PostgreSQL

 

In Connection Settings set Hostname as localhost, Port as 5432, and Database as postgres as shown in Figure 3. Specify Username as postgres and the password (default is postgres). Click on Test Connection. Connection is OK message gets displayed as shown in Figure 3 indicating that a connection gets established. Click on Next.

Figure 3. Connection is OK

 

In Connection Details a connection name is specified by default. Optionally set a shorter connection name (postgres) and select the checkbox Enable Autocommit as shown in Figure 4. Click on Finish.

Figure 4. Connection Details

 

A new connection gets created and added to Connections view as shown in Figure 5. A new SQL Worksheet gets associated with the connection by default.

Figure 5. New Connection to PostgreSQL in Toad Edge

 

Converting model to PostgreSQL 10

In this section we shall convert the model in Toad Data Modeler developed for SQL Server 2017 to a PostgreSQL 10 model. Click on Run Convert in the toolbar as shown in Figure 6.

Figure 6. Run Convert

 

Select PostgreSQL 10 as the Target Database platform in the Model Conversion wizard as shown in Figure 7. The PostgreSQL version options could be grayed out which implies that these are not yet enabled and shall get enabled after being selected.

Figure 7. Selecting Target Database Platform as PostgreSQL

 

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

Figure 8. Model Conversion>Target>Next

 

A Confirm dialog prompts with a message that the PostgreSQL 10 database is not yet enabled and does the user want to enable the database. Click on Yes. Subsequently click on Next in the Target window. Next, select the Conversion Settings option as shown in Figure 9. Click on Next.

Figure 9. Conversion Settings

 

Select Object Types as Convert All as shown in Figure 10.

Click on Next.

Figure 10. Selecting Object Types to convert

 

The Converter starts to run. Initially the objects get paired as indicated by a Pairing Objects message. Next, select items to convert with the checkbox adjacent to an item as shown in Figure 11. Select the two Entities Log and LogEntry, and also select the two Relations. Click on Next.

Figure 11. Item Selection

 

In Review specify a new model name (PostgreSQL 10) in the New Model Name field and click on Finish as shown in Figure 12.

Figure 12. Review>Finish

 

The converter continues to run. The Before Convert message is displayed initially. Subsequent status becomes Convert. The model gets loaded as indicated by a Loading Model dialog. The PostgreSQL 10 model gets created.  The entity relation diagram also gets converted for PostgreSQL 10 database. The data types get converted; Varchar gets replaced with Character varying. The SQL for the entities also gets converted. Double-click on the header for the Log entity as shown in Figure 13 to display the SQL associated with the entity.

Figure 13. Log Entity>Double-Click

 

The SQL Preview tab in the Entity Properties displays the SQL as shown in Figure 14.

Figure 14. Log>Entity Properties>SQL Preview

 

Similarly, the SQL Preview for the LogEntry table may be displayed.

 

Modifying default settings for generating DDL

We need to modify the SQL File name that gets generated by default. Select Model>Generate DDL Script>Edit Default Settings… as shown in Figure 15.

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

 

In the DDL Script Generation wizard modify the SQL file name in the Location of SQL File field to a lowercase suffix .sql file as shown in Figure 16. Click on OK.

Figure 16. Modifying Generated File Name Suffix

 

Generating DDL for PostgreSQL 10

To generate DDL for the PostgreSQL 10 model select Model>Generate DDL Script>Run… as shown in Figure 17.

Figure 17. Model>Generate DDL Script>Run…

 

Click on Verify to verify the model as shown in Figure 18.

Figure 18. DDL Script Generation of PostgreSQL 10>Verify

 

An Information dialog gets displayed indicating that the model verification has completed.

Click on Generate to generate the DDL as shown in Figure 19.

Figure 19. DDL Script Generation of PostgreSQL 10>Generate

 

A DDL Script gets generated at the directory location specified in the Location of SQL File field.  The DDL script may need to be slightly modified if the target PostgreSQL database already has some of the database objects reserved. Comment out the section to create a role publicas our target database has the publicrole reserved.

 

/*CREATE ROLE "public"

;*/

Add a statement to create schema dbo if it does not already exist in the target database.

— Add Schema section ————————————————-

CREATE SCHEMA "dbo"

;

Comment out the section to grant dbo_owner permission to dbo.

— Grant permissions section —

/*GRANT "db_owner" TO "dbo"

;

*/

The modified DDL script generated is listed:    

/*

Created: 11/10/2018

Modified: 11/10/2018

Model: PostgreSQL10

Database: PostgreSQL 10

*/

— Create roles section ————————————————-

CREATE ROLE "dvohra"

;

CREATE ROLE "db_accessadmin"

;

 

CREATE ROLE "db_backupoperator"

;

CREATE ROLE "db_datareader"

;

CREATE ROLE "db_datawriter"

;

CREATE ROLE "db_ddladmin"

;

CREATE ROLE "db_denydatareader"

;

CREATE ROLE "db_denydatawriter"

;

CREATE ROLE "db_owner"

;

 

CREATE ROLE "db_securityadmin"

;

/*CREATE ROLE "public"

;*/

— Create schemas section ————————————————-

CREATE SCHEMA "db_owner"

;

CREATE SCHEMA "db_accessadmin"

;

CREATE SCHEMA "db_securityadmin"

;

CREATE SCHEMA "db_ddladmin"

;

CREATE SCHEMA "db_backupoperator"

;

CREATE SCHEMA "db_datareader"

;

CREATE SCHEMA "db_datawriter"

;

CREATE SCHEMA "db_denydatareader"

;

CREATE SCHEMA "db_denydatawriter"

;

— Add Schema section ————————————————-

CREATE SCHEMA "dbo"

;

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

— Table dbo.Log

CREATE TABLE "dbo"."Log"(

 "MSG_CODE" Character varying(20) NOT NULL,

 "DESCRIPTION" Character varying(255)

)

WITH (

 autovacuum_enabled=true)

;

— Add keys for table dbo.Log

ALTER TABLE "dbo"."Log" ADD CONSTRAINT "PK_Log" PRIMARY KEY ("MSG_CODE")

;

 

— Table dbo.LogEntry

CREATE TABLE "dbo"."LogEntry"(

 "MSG_CODE" Character varying(20) NOT NULL,

 "CODE" Character varying(20) NOT NULL,

 "TIMESTAMP" Character varying(55),

 "MSG" Character varying(255)

)

WITH (

 autovacuum_enabled=true)

;

— Add keys for table dbo.LogEntry

 

ALTER TABLE "dbo"."LogEntry" ADD CONSTRAINT "PK_LogEntry" PRIMARY KEY ("MSG_CODE","CODE")

;

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

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

;

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

;

— Grant permissions section ————————————————-

 

/*GRANT "db_owner" TO "dbo"

;

*/

 

Running DDL to create PostgreSQL 10 Tables

Next, run the DDL script generated in Toad Edge. Copy and paste the DDL script to a SQL Worksheet associated with the PostgreSQL 10 connection in Toad Edge. Click on Execute All Statements as shown in Figure 20.

 

Figure 20. Execute All Statements

 

The DDL script statements get processed as indicated by a message. The Schemas generated includes the dbo schema as listed in Object Explorer in Figure 21.

Figure 21. New Schemas Created

 

The dbo schema lists the Tables and Constraints created as shown in Figure 22.

Figure 22. Tables and Constraints added to PostgreSQL

 

The Object Explorer objects may need to be refreshed if not updated automatically.  New group roles were added but if the Group Roles in Object Explorer don’t seem to have updated, click on Refresh, which is the button with two counter-clockwise circular arrows.   

The number of Group Roles gets updated from 5 (Figure 21) to 15 (Figure 23).

Figure 23. Group Roles Updated

 

Describing PostgreSQL 10 tables

PostgreSQL does not support the DESC command to describe database objects that the MySQL database supports. Instead run a SELECTstatement to list column names for the dbo.Logtable as shown in Figure 24.

Figure 24. Using SELECT Statement to list Columns for Log Table

 

Similarly run another SELECTstatement to list column headers for the dbo.LogEntrytable as shown in Figure 25.

Figure 25. Listing Column Headers for dbo.LogEntry Table

 

Conclusion

In three articles we discussed migrating SQL Server 2017 tables to open source databases MySQL and PostgreSQL using Toad developer tools. We used Toad for SQL Server to create the tables to migrate in SQL Server 2017. We used Toad for Data Modeler to reverse engineer SQL Server 2017 tables to a model. We used Toad for Data Modeler to convert the SQL Server 2017 model to MySQL 5.7 model and a PostgreSQL 10 model. We used Toad Data Modeler to generate DDL scripts for the converted models. We used Toad Edge to connect to MySQL 5.7 and PostgreSQL 10 databases and run the generated DDL scripts to create tables in MySQL 5.7 and PostgreSQL 10 databases.

 

 

More blogs on Toad World

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

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

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!