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

The general databases trend is in favor of the open source database with two of the top 5 relational RDBMS being open source databases according todb-engines.com. The top two open source databases are MySQL and PostgreSQL.  As use of the open source database increases, DBAs and database developers may consider migrating. Migrating to open source databases incurs several requirements, the main ones being the following:

• Developer tools that connect with open source databases and run SQL scripts are different
• The data types supported by open source databases are different
• The SQL syntax used by open source databases is different

In a series of three articles, we shall discuss migrating from a commercial database (SQL Server) to open source databases MySQL and PostgreSQL. We shall make use of several Quest® Toad® developer tools:

• Toad for SQL Server for creating SQL Server tables to migrate,
• Toad Data Modeler for converting SQL Server model to open source models, and
• Toad Edge to run open source database specific DDL scripts

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

This tutorial consists of three articles; we shall start with the first article, that will cover the first four steps in the process and are highlighted above in grey.

Setting the environment

Download and install the following software:

–         Toad for SQL Server

–         Toad Data Modeler

–         Toad Edge

–         MySQL 5.7 Database

–         PostgreSQL 10 Database

 

We need to create database tables in SQL Server; tables that are to be migrated to open source databases MySQL and PostgreSQL. We have used a SQL Server Express Edition instance (Figure 1) on AWS RDS. Another SQL Server instance running on a different platform may be used as well. 

 

Figure 1. SQL Server Express Edition RDS Instance

 

Creating SQL Server tables in Toad for SQL Server

To create tables in SQL Server Express Edition obtain connection parameters from the RDS Dashboard. Click on the sqlserver instance link shown in Figure 1. Copy the Endpoint from the Connect section as shown in Figure 2. The Endpoint is the SQL Server hostname. Also copy the Port (1433 by default). As the Toad for SQL Server and Toad Data Modeler are installed on the local machine we need to modify the Inbound access rules to allow access from the local machine, or all source IPs if the IP of the local machine is not known. Click on the rds-launch-wizard-3 link shown in Figure 2 to modify  the Inbound rules settings.

Figure 2. Connect Endpoint

 

Next, create a connection in Toad for SQL Server. Select Connect>New Connection as shown in Figure 3.

Figure 3. Connect>New Connection

 

In Create New Connection specify Server name as the Endpoint URL as shown in Figure 8. Select Authentication as SQL Server Authentication. Specify Login and Password as the Master username and Master password respectively. Click on the selector button (…) for Database as shown in Figure 4 to select a database to connect to.

Figure 4. Create New Connection

 

A connection gets established with SQL Server to obtain available databases. In Browse Databases select tempdb database as shown in Figure 5 and click on OK.

Figure 5. Browse Database>tempdb

With all the connection parameters selected click on Connect as shown in Figure 6.

Figure 6. Connect

A new connection gets created and added to Connection Navigator as shown in Figure 7.

Figure 7. New Connection to SQL Server  

 

Next, we shall run a DDL script in Toad for SQL Server to create database tables in SQL Server. Select Edit SQL from the toolbar as shown in Figure 8.

Figure 8. Edit SQL

 

Copy the following SQL script to the SQL Worksheet.

/*

Model: Microsoft SQL Server Express Edition

Database: MS SQL Server Express Edition

*/

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

— Table Log

CREATE TABLE [Log]

(

 [MSG_CODE] Varchar(20) NOT NULL,

 [DESCRIPTION] Varchar(255) NULL

)

go

— Add keys for table Log

ALTER TABLE [Log] ADD CONSTRAINT [PK_Log] PRIMARY KEY ([MSG_CODE])

go

— Table LogEntry

CREATE TABLE [LogEntry]

(

 [MSG_CODE] Varchar(20) NOT NULL,

 [CODE] Varchar(20) NOT NULL,

 [TIMESTAMP] Varchar(55) NULL,

 [MSG] Varchar(255) NULL

)

go

— Add keys for table LogEntry

ALTER TABLE [LogEntry] ADD CONSTRAINT [PK_LogEntry] PRIMARY KEY ([MSG_CODE],[CODE])

go

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

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

go

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

go

 

The SQL script creates two tables Log and LogEntry for WebLogic server log with two foreign keys in LogEntry referencing the primary key in Log. The SQL script includes go commands for the connect utility Toad for SQL Server. After copying the script to a SQL Worksheet select Editor>Execute SQL as shown in Figure 9. 

Figure 9. Editor>Execute SQL

 

As the messages in Figure 10 indicate the SQL statements in the script get run successfully. Two tablesdbo.Log and dbo.LogEntry get created and get listed in Object Explorer. The dbo.Log table has two columns MSG_CODE and DESCRIPTION.

Figure 10. SQL Statements run successfully

 

The dbo.LogEntrytable has 4 columns MSG_CODE, CODE, TIMESTAMPand MSGas shown in Figure 11.

Figure 11. The dbo.LogEntry Table

 

Creating a model for SQL Server  

Next, create a model in Toad Data Modeler for SQL Server. Select File>New>Model as shown in Figure 12.

Figure 12. File>New>Model

 

In New Model select Physical Data Model as Microsoft SQL Server 2017 as shown in Figure 13. Click on OK.

Figure 13. Selecting SQL Server 2017 as Physical Data Model

 

A new model gets created and added to Physical Model Explorer as shown in Figure 14. A new model is empty and does not contain any data structures or objects.

Figure 14. New Model created

 

Reverse engineering SQL Server tables

Having created both tables and a model for SQL Server next we shall reverse engineer the tables to the model. Click on Update Model from Database as shown in Figure 15.

Figure 15. Update Model from Database

 

The Model Update Wizard gets launched as shown in Figure 16. Click on Next.

Figure 16. Model Update Wizard

 

Select data source as Database RE>Microsoft SQL Server 2017 as shown in Figure 17. Click on Next.

Figure 17. Selecting Data Source

 

Next, select Data Provider as Native Connection as shown in Figure 18.

Figure 18. Selecting Data Provider

 

Next, configure a connection to SQL Server as shown in Figure 19. Specify Host Name as the Endpoint URL for the SQL Server RDS instance (or other SQL Server instance). Specify Port as 1433. Select OLE DB Provider as Auto. Select User Authentication as Server. Specify User Name and Password as the Master Username and Maser Password respectively.  A connection gets established dynamically and the databases to select from get listed in the Database drop-down. Select the tempdb database.

Figure 19. Connecting

 

Click on Next as shown in Figure 20.

Figure 20. Connecting>Next

 

Next, select What to Reverse as shown in Figure 21. Select Entities, Relationships, Sequences and Synonyms and other properties as needed. Click on Next.

Figure 21. What to Reverse

 

Keep the default settings for Options for Reverse Engineering and Items to Load as shown in Figure 22. Click on Next.

Figure 22. Options

 

Next, click on Save to save connection as shown in Figure 23.

Figure 23. Save Connection>Save

 

In Save Connection dialog specify a Connection Name and click on OK as shown in Figure 24.

Figure 24. Save Connection

 

A new connection gets added as shown in Figure 25. Click on Next.

Figure 25. Save Connection>Next

 

Next, select tables from those listed.  Click on Select All to select all tables as shown in Figure 26.

Figure 26. Select All Tables

 

With the tables selected click on Execute as shown in Figure 27.

Figure 27. Tables>Execute

 

Next, select Comparison Rules as shown in Figure 28 and click on Next.

Figure 28. Comparison Rules

 

Select object types and properties. Select Compare All as shown in Figure 29 and click on Next.

Figure 29. Select Object Types

 

Select items to reverse engineer as shown in Figure 30. Click on Next.

Figure 30. Items Selection

 

In Review, the number of Commits for each item type gets listed as shown in Figure 31. Select the checkbox Merge to New Model and specify a Model Name. Click on Finish.

Figure 31. Review

 

The model gets loaded. The SQL Server tables get reverse engineered and an entity relation diagram gets generated as shown in Figure 32. Two entities Log and LogEntry and two relationships get created and added to the model. The model may not be well formatted initially. Right-click and select Optimal Style to All Lines.

Figure 32. Entity Relation Diagram

 

The entity relation diagram gets formatted as shown in Figure 33.

Figure 33. Formatted Entity Relation Diagram

 

Summary

In this article we introduced migrating to open source databases MySQL and PostgreSQL using Toad developer tools. We created an AWS RDS instance for SQL Server Express Edition. We created tables in the SQL Server  by running DDL script in Toad for SQL Server. We created a model for SQL Server in Toad Data Modeler and reverse engineered SQL Server to the model. In subsequent articles we shall convert the model to MySQL and PostgreSQL database models and subsequently migrate the database tables to the open source databases.

 

 

More blogs on Toad World

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!