Working with a different database type?
Central SQL Server Database
- SQL Server to a SQL Server
- MySQL databases to SQL Server
- Oracle to SQL Server
- File Maker Server to SQL Server
- dBase (.dbf) to SQL Server
Central MySQL Database
The Basics
SQL-Hub allows you to merge data from multiple databases into 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 integrating 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...
Workflow
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
Synchronisation
- 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
You will need to use the 32-bit version of SQL-Hub
In order to connect to the paradox files the Borland Database Engine needs to be installed.
In some cases you will need to run SQL-Hub as administrator for connections to paradox to work correctly
Setting up the Central Database
- Open SQL-Hub
- Configure the Central Database Connection:
Enter the paramaters as follows
Central Server Name: | the name of the SQL Server the central database is to be hosted on |
Central User Name: | a SQL Server Username with access to the server and permission to create databases - if you want to use Integrated Windows Authentication enter nothing here |
Central Password: | the password for the SQL Server user - if you want to use Integrated Windows Authentication enter nothing here |
SQL-Hub DB Name: | the name for the main central view database |
File Storage Path: | a file system location to store local copies of the .db files (the directory needs to be exist and be writable) |
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 SQL Server and you now need to connect a source database to complete the set up.
- Click New Source
Now configure the first source
File System Connectivity
In order to extract data from paradox databases SQL-Hub needs to be able to access the .db files on the file system.
If the files are located on a different machine the file location may need to be set up as a windows share - the SQL-Hub user given permission to access this.
If the remote share is on a different domain (and therefore windows cannot automatically authenticate the SQL-Hub user) you may need to open the share manually (in Windows Explorer), enter valid domain credentials, and check the box "remember my credentials". This will allow SQL-Hub to access the files when running under this user profile
Synchronising
SQL-Hub allows you to synchronise in 3 ways...
On Demand Instance Synchronisation
Synchronise from a single source database from the user interface.
- Click Synch on the panel relating to a specific source.
On Demand Full Synchronisation
Synchronise from all source databases from the user interface.
- Click the Synch All icon on the Master Connection panel.
Scheduled Full Synchronisation
Synchronise from all source databases from the command line automatically. 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 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.
Once the process is complete you will find tables in the Central Database containing data from all of the synchronised databases and tables.
Scheduling
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 the Schedule panel, below the Licence panel
- Click "Create Task"
SQL-Hub will automatically create a task to synchronise all sources daily.
You can edit the task using Windows Task Scheduler to suit your requirements. Just find the task in the Windows Task Scheduler Library and adjust the settings.
Your SQL-Hub setup is now complete
You now have everything in place to maintain a central database containing all the data from your source databases
As we say in Manchester, Job's a good 'un