screen decoration screen decoration
Data at the heart of your organisation
Trial: £free
Standard: from £450

Merge multiple Oracle Databases into a single MySQL Database


The Basics

SQL-Hub allows you to merge data from multiple databases in to a single central view. SQL-Hub is ideal where you have more than one database containing the same types of data. SQL-Hub combines the data from the tables in each of the databases into corresponding merged tables in a central database. Each record is tagged so its source database can be easily identified.

What you need

  • SQL-Hub: you need to install the appropriate version of the SQL-Hub database merging tool for your databases on a Windows machine
  • Connectivity: SQL-Hub needs to be installed on a machine that can connect to your source databases and the host for the central database you create
  • Permissions: you need to have a login to your database server that allows you to create the merged databases (catalogs/schemas)
  • Client Drivers: In most instances no client tools or drivers will be required however in some cases you may need to install some client software or drivers to connect to your databases - see client drivers below

Once these are in place you are ready to start...



SQL-Hub setup consists of a few simple steps...

Setting up the Central Database

  • To hold configuration information and the merged data

Connecting to a Source

  • To gather information about the structure of the source databases

Building the Central Tables

  • To create the table structure for the central copies of the chosen tables. This only needs to be done once - but can be revisited at any point

Adding Further Sources

  • To connect to each of the source databases - only the connection parameters are required, no additional configuration


  • To merge the data from all of the source databases

The Result:

  • A Central Database with a copy of the chosen tables containing the data from all of the source databases

Client Drivers

In order to connect to the Oracle source databases, either Oracle or the Oracle Instant Client - with ODBC, need to be installed and the network configuration set up.

The Instant Client is available from Oracle - the "Basic and ODBC" packages will be required. Set the environment variable TNS_ADMIN to the path of the Instant Client Installation and install the ODBC driver by running odbc_install.exe in the Instant Client Folder

Be sure to install the correct version (32bit / 64bit) for the version of SQL-Hub you are using. There are known problems with the 64bit version - we recommend sticking to 32bit.

To set up connections to the source Oracle servers you need to configure the tnsnames.ora file with entries for each server. This is well documented by Oracle but for example, the contents of this file to connect to an Oracle server with service name XE on server would be as follows

# tnsnames.ora Network Configuration File: C:\Program Files\instantclient_11_1\tnsnames.ora
# Generated by Oracle configuration tools. MAN = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = XE) ) )

If you are using the 32bit versions on a 64bit machine remember that you need to use the 32bit ODBC Administator (C:\Windows\SysWOW64\odbcad32.exe) if you are testing the driver.


Setting up the Central Database

  • Open SQL-Hub
  • Configure the Central Database Connection:

Enter the paramaters as follows

Central Server Name: the name or IP Address of the MySQL Server the central database is to be hosted on
Central User Name: a MySQL Username with access to the server and permission to create databases
Central Password: the password for the MySQL user
SQL-Hub DB Name: the name for the main central view database
Temp DB Prefix: the prefix for central copies of the individual source databases
  • Click Connect:

SQL-Hub will connect to the Central Server

If the connection is not succesful any errors will be shown in the progress dialog

If the connection is succesful SQL-Hub will attempt to create the central database and configuration tables and display a message detailing the current status. Again any errors will be show in the progress dialog.

Once the above is complete you should be able to see this database in MySQL and you now need to connect a source database to complete the set up.

  • Click New Source

Now configure the first source


Connecting a Source

  • Click New Instance
  • Configure the Source Connection:

Enter the paramaters as follows

Source Server: the name of the Oracle ODBC driver to be used (e.g. "Oracle in instantclient_11_1" - this can be found in the appropriate ODBC administrator)
Source User Name: an Oracle Username with access to the source database
Source Password: the password for the Oracle user
Source DB Name: the name of the entry for the database in the tnsnames.ora file
Source Name: the name to use for this source database in the central view
  • Click Connect:

SQL-Hub will connect to the Source Server

If the connection is not succesful any errors will be shown in the progress dialog

If this is the first connection or the Table List has not yet been created SQL-Hub will display a message detailing the current status and you should then proceed to the next step - otherwise you can proceed directly to Synchronisation.

  • Click Create/Update Table List:

You can now select the tables you want to include in your merged central database

Check the box next to each table you would like to include if this is your first time configuring a merged central view you might want to just select one or two tables to try out. You can change the table selection later by clicking Tables on the main SQL-Hub dialog.

  • Click Ok

Creating the central tables

SQL-Hub will display a message detailing the current status

  • Click Create Master Data Tables

    to create empty copies of the data tables in the merged central database

If you have not used SQL-Hub before you will need to get a FREE licence to create the master tables. When you click on Create Master Data Tables, a prompt will appear and then the Licence dialog will open automatically.

Getting a Licence

  • The Licence dialog

    this opens automatically if you try to synchronise or Create Master Data Tables without a licence or can be accessed from the link on the main SQL-Hub Application

  • Click "get licence"

    and follow the instructions on the website

Remember to click Create Master Data Tables again once you have your new licence.

You are now ready to synchronise from this source

You can return to the configuration dialog for a source at any time by double clicking the source in the 'Sources' table in the SQL-Hub Dashboard

  • Click Synch


SQL-Hub allows you to synchronise in 3 ways...

On Demand Instance Synchronisation

Synchronise from a single source database from the user interface - requires a basic licence (which may well be free).

  • Select the required source from the 'Sources' list
  • Click Synch

On Demand Full Synchronisation

Synchronise from all source databases from the user interface - requires a standard licence (which may also be free).

  • Click Synch All on the SQL-Hub Dashboard

Scheduled Full Synchronisation

Synchronise from all source databases from the command line automatically - requires a standard licence (which may also be free). See Scheduling Below

The Synchronisation Process

The synchonisation process creates a database on the central server for each instance database and creates a table in the Central merged database for each table included.

Data is copied from each source database to a central copy and then inserted in the merged central database table by table. The progress and any errors are displayed in the progress dialog.

Once the process is complete you will find tables in the merged Central Database containing data from all of the synchronised databases and table



You can schedule full synchronisation using the Windows task scheduler

When running through the UI SQL-Hub stores connection settings in the current user profile - the same user must be used for the scheduler to allow these connection settings to be determined

  • Open Windows Task Scheduler
  • Click "Create Basic Task"
  • Parameterise the Task:

Set the name, description and schedule as required

The parameters for the action should be set as follows

Program/Script:The path to the SQLHub??.exe executable - e.g. "C:\Program Files\SQLHub\SQLHub.exe" (with quotes)
Start In:The path to the directory containing the SQLHub??.exe executable - e.g. C:\Program Files\SQLHub\ (without quotes)

Your SQL-Hub setup is now complete

You now have everything in place to maintain a central collated database containing all the data from merged from your source databases

As we say in Manchester, Job's a good 'un

All content © SQL-Hub / Burr IST 2018
Site by Burr IST, design by foliozine

Burr Information Systems Technology Ltd

28 Park Avenue, West Point, Manchester, M19 2EE

Tel: +44 (0) 161 327 2934