Migrating to a New Database Source, Made Simple

With the emergence of so-called free and open source database software solutions, more and more companies are looking to break away from the grip of high cost database software. Just as quickly as management sees the dollar signs in savings, the Data architects and DBAs see the headaches brought about from making this change/move a reality.

Data architects and DBAs are likely to respond by pointing to the complexity of the task at hand. How will the current database model and DDL be generated? Who will convert that model and DDL to the new database format? How long will it take to extract the data from the current database source and move it to the new database source? Not to mention that the extracted data will likely have to be moved from one server to another.

Sometimes even with those questions, management still does not see the complexity of the issue. Often we look at the enormity of a particular task without considering the tools we have on hand to help us.

Say we are building a new house and we just received a shipment of clay tile roofing material that was dropped off on the ground in front of the new house. We quickly call the shipping company and tell them the material was put in the wrong location. They agree to fix it, but can’t make it out again for quite some time. The first thought that comes to mind is “Great, now how am I going to get this to where it needs to go in an acceptable amount of time?” We could use a person to move the material up to the roof, but that will take a long time. We could use a pulley to help us lift the material to the roof, but the material is very heavy and will have to be broken down into groups of a more manageable size. A crane can do the job, but it can’t do it alone. It needs other elements like a person and a pulley to complete the task.

Similarly, the Data architect and DBA can accomplish the task efficiently by leverging tools like Toad Data Modeler and Toad Data Point to do all the heavy lifting for them.

Reverse Engineering and Generating DDL Script with Toad Data Modeler

Toad Data Modeler can easily create a data model of an existing database.

Create the database model of the current database source by opening the Reverse Engineering wizard and step through the wizard to get the connection to the database.

Select what you would like to reverse engineer.

Select the options you would like.

Finally, select the Tables, Functions, Views, etc. that you would like to reverse engineer, then click Execute. 

Now that you have your database model, you will be able to convert the current database model to your new source.

Select the Convert button. 

Go through the conversion wizard and select the type of database your target will be and select the conversion settings. 

Select the object types and items you would like to convert.

Review and change the name of the model.

NOTE: This is the name that will appear on the tab once the model is complete.  

Now that our model is converted, let’s generate our DDL script to create our new schema/database. 

Select what objects and details you would like the script to contain and make note of the location of the generated script once complete. 

Before generating the script, make note that you may verify the model to ensure that all relationships and entities do not have any issues.  

NOTE: The Generate button will verify whether the model has issues prior to generating the script, so why not be proactive and verify that everything is correct before hitting the Generate button? Doing so will allow us to modify any changes that need to occur or correct any issues. 

Creating Database/Schema and Moving Data across Database Platforms with Toad Data Point

Now that we have the DDL created we will be able to execute it to build our structure in Postgres. Let’s start with creating a connection to our Postgres server. 

After connecting, open the script in the editor and “Run” (execute) the script. 

We can verify that our tables were created using the Object Explorer.

Let’s now use Toad Data Point to move the data for us from Oracle to Postgres. Let’s open a second connection to our Oracle Database.

Once connected, let us use the Import Export Data wizard to do the heavy lifting and move the data for us. 

Once in the wizard, we will enter source and target databases/schemas.

We will then select the table in our source where we are getting our data from. 

We then select the target table we are moving the data to. 

Verify the Import Source. 

Finally on the last page of the wizard, we will be able to import the data immediately or input it at a later time.

NOTE: the wizard will be able to be sent to Automation where it will be stored as created so that the process will not have to be recreated each time.  

Once we finish we will see a report of the data that was moved. 

We can also use the “View Details” by right clicking on the table and verifying the data is there on the data tab. 

As we have seen, it is simple to move from one data source to another.

Users can create database models from 16 different data sources and extract data from over 30 unique data sources. With over 20 years in helping users manage their databases, it is no surprise Quest Software offers a powerful line of tools in its Toad Family of products to simplify the task at hand. Toad Data Modeler and Toad Data Point take the worry out of having to recreate a Data Model and Data Preparation needs. Utilizing these tools will take the headache out of migrating from an existing database platform to a new one, freeing a company from that database software grip to explore those free and open source database solutions.