FAQ: Database migration

General Section

Synchronization issues

Database/ Products specific questions

Microsoft Access and MySQL conversion and synchronization issues

Microsoft SQL Server and MySQL conversion and synchronization

Questions about MS Excel converters

Sales questions

Converting and Synchronizing tools: what are the basic differences between them?

Converters perform data conversion in the direction from source to destination. At the destination stage there is an option to choose Dump or PHP script, whereupon a converting tool creates files containing database information for further indirect data transferring to server. This detour is quite necessary for those who have server access restrictions for some reason.

Bulk insert option available for converter tools increases the speed of data migration. Tests show that the speed reaches 1 Million records per 5-10 minutes.

Views/queries conversion is available in Converters only and there is no such option for sync tools.

Read more information about database conversion .

Synchronizers mostly specialize in cross database synchronization. Software compares values of records between source and target databases. Combining of Insert, Update, and Drop sync options helps to make all table data identical for both source and destination.

Database Sync software requires having direct access to a both source and target databases.

Trigger based instant synchronization feature should be considered if your task is to replicate changed data to target database immediately after source database altered.

Comparative overview of conversion and synchronization tools.

  • Saving data to local dump file
  • Saving data to PHP script file
  • Built-in scheduler
  • SSH / SSL support
  • Forms conversion
  • Trigger-based instant synchronization method
  • Queries conversion
  • Bulk insert support

Conversion

  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  

Synchronization

  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  

Web forms

  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  

What Database types your applications support?

The list of supported server types is below:

The list of supported non-server types:

  • Access 97/2000/2003/2007/2010
  • FoxPro 9.x, 2.6 and later
  • Excel 97/2000/2003/2007/2010
  • SQLite 3.0, later and earlier

Is there any volume of data limit for data migration? How many tables and rows can be converted with your tools?

Every database management system has its own limits on the size of a database that can differ from one db to another. Also there are some restrictions related to the data types imposed by Database engine. Thus, the speed of conversion process depends on database complexity, its size and your PC hardware configuration.

What about conversion/ synchronization of large tables?

Bulk Insert and Quantization features help to perform big database migration efficiently and quickly

Generally, to convert or sync big-sized tables there are 3 scenarios depending on what you need:

  • 'Quantization' option is intended for transferring big tables with millions of entries and not to extend memory usage at the same time. The whole record set is divided into several subsets. However, it requires more time than the regular conversion / syncing process.
  • 'Bulk insert' database option significantly accelerates conversion / synchronization speed for big tables if you are pressed for time. But this feature requires the size of memory similar to the standard conversion settings.
  • Use the 'Bulk insert' option together with the Data Filtering feature . It is the most recommended solution for a speedy conversion of large tables despite the necessity of creating a few sessions to realize it. Set a range of data by means of filtering (e.g., from 1 to 20 000 records) and then check 'Bulk insert'. It will make the size of distributed memory smaller in comparison with the size described in the 2nd scenario.

Read more information about Quantization and Bulk Insert features

bulk insert and quantization options

Is there queries / views conversion support in your software?

Yes. Find more information about How to translate Views and Queries between databases automatically

Why am I unable to get an access to my database to a remote server via SSH Tunnel using PuTTY?

The following manual about SSH Tunnel connections covers 2 possible ways of establishing connection to database servers. It will clarify how to pre-configure SSH options using PuTTY or how to specify “SSH forwarding” options directly from our software excluding PuTTY configuration as a connection method.

I've got The Error: "out of memory for query result". How to overcome this?

Please check 'Quantization' option at the customization stage. The synchronization speed will be increased for processing large databases. This is achieved by splitting the whole record set into portions. Try to set the slider value to 100 or lower than 100 until the error disappears.

Is there queries / views conversion support in your software?

Yes. Find more information about How to translate Views and Queries between databases automatically

Why am I unable to get an access to my database to a remote server via SSH Tunnel using PuTTY?

The following manual about SSH Tunnel connections covers 2 possible ways of establishing connection to database servers. It will clarify how to pre-configure SSH options using PuTTY or how to specify “SSH forwarding” options directly from our software excluding PuTTY configuration as a connection method.

Can I convert primary keys, foreign keys and indexes?

Our programs enable complete conversion / synchronization of Foreign keys (relations), Primary keys, and Indexes.

Is it possible to choose only certain database elements for migration, not the whole database?

Our tools perform conversion of such database objects as tables, views, fields, foreign keys and indexes. If you don’t need to migrate the entire database, just select only required db elements to get converted. As all database elements are checked at the Customization stage by default, all you have to do is to uncheck tables, views, fields or indexes that you’re not going to convert.

Is it possible to migrate user-specified data types?

Only standard data types are supported by our tools. Complex data types such as data arrays and data structures or user defined data types are not supported.

Can your application be launched in command line mode?

Our data migration solutions can be launched from a command line with session name as a parameter, which keeps source/ target db connection settings and other predefined options. Passwords for accessing servers can be saved in a session file as well.

Example:C:\Program Files\DBConvert\MSSQL2MySQLPro\MSSQL2MySQLPro5_Cons.exe /Session:"Session Name" 

In /Session:"Session name" you should type the session name created before. Initially you should run the software in GUI mode to create a session file with parameters.

What does Batch file mean and can I use it for your converters?

A batch file (.BAT) is an executable text file containing a list of commands to perform. It is used to replace manual commands inputting from the keyboard. Command processor or an application reads that scenario and executes programs and/or other command files consecutively. Batch files allow automating routine and frequently executable operations.

Read more information about batch files at http://en.wikipedia.org/wiki/Batch_file. Our tools can use standard .bat files created either automatically with help of our built-in scheduler or manually with any text editor.

Here is a batch file sample:
                rem File:MSSQL2MySQLPro_Cons.exe
                    rem Session:New session [26.02.2013 15.16.29]
                    rem -------------------------------------------------------
                    "C:\Program Files\DBConvert\MSSQL2MySQLPro\MSSQL2MySQLPro_Cons.exe" /Session:"New session [26.02.2013 15.16.29]"
                

Can the conversion/ synchronization process be run automatically?

At the Scheduling stage in GUI mode you can arrange tasks to automatically accomplish actions at a particular time. Scheduling process can be left unattended running in the background automatically. Thus, your subsequent involvement is not required.

Read more information about scheduling tasks for your database migration jobs

Where can logs and session files be found?

Find “Settings” button in the bottom left corner. In ‘Work folder’ area you can setup a folder for storing sessions and logs.

By default, log files are located at:

C:\Program Files\DBConvert\Program name in LOG folder.

Example: "c:\Program Files\DBConvert\Oracle2MySQLPro\LOG\Oracle2MySQLPro.log"
            

Sessions are stored to C:\Program Files\DBConvert\Program name in Program name Sessions folder.

Example: "c:\Program Files\DBConvert\Oracle2MySQLPro\Oracle2MySQLPro Sessions"

If you can't find sessions and logs in default directory, probably UAC settings enabled in your Windows Vista/7/8

In this case iles can be located at:

C:\Users\%username%\AppData\Local\VirtualStore\Program Files\DBConvert\'program name' 

Or, if you run 64-bit system they are at:

C:\Users\%username%\AppData\Local\VirtualStore\Program Files (x86)\DBConvert\'program name'

Synchronization log can be found at: C:\Program Files\DBConvert\Program name\Program name Diffs
Example: "c:\Program Files\DBConvert\Oracle2MySQLSync\Oracle2MySQLSync Diffs"

You should check the "Create Log" option to keep tracking of events occurring to the data during the synchronization process. If the "Capturing the changes between source & destination" option is on, the log will be transformed to Dump file where you can find all generated database queries. When the "Store dump for target database back-up" option is checked, it allows producing a dump that includes the information before changing, i.e. back-up. Please see the screenshot below:

log creating options


If you need to change machine you should copy the "sessions" folder and .BAT files from the locations mentioned above depending on your OS version and UAC settings. If you need to redefine scheduled tasks, you can copy/export tasks in Windows Scheduler and then paste/import them to Windows Scheduler on the new machine.

Why my session cannot be stored?

Most probably you have no write permissions to the folder where your session is stored. Acquire rights or choose another folder with the ‘Write’ permission given for storing your session files.

A scheduled task doesn't run at all, though it's shown in your embedded Scheduler. I see the following message "Task has not been run".

The scheduling function of our tools is entirely based on the Standard Windows scheduler.

1. Make sure you've entered correctly your user login and password for Windows system in our Scheduler settings. Otherwise, the task will not be run as expected.

So, push "Edit" button, in the appeared "Edit task" window press "Settings" tab where you should input your user account name, password, and password confirmation. This user account information must be the same as you use for Windows OS.

As the standard Windows scheduler is used to add tasks for the converting / synchronizing process, you will need to create a .bat file. How to do it, see the batch file sample.

If sessions have been already created, they can be just specified in .bat file.

Note: Please read the information about the standard Windows scheduler at http://technet.microsoft.com/en-us/library/cc786711(v=ws.10).aspx

2 In addition to login and password, please choose the end date of your task.

In the "Edit task" window, press the "Triggers" tab and assign a timetable for starting a task. Press the "Advanced" button and activate the "End date" option.

3. One more reason of such scheduler malfunction is the non-activated option in the Windows Scheduler. Please see the screenshot below.

windows scheduler

NOTE: When using Windows Vista / Windows 7, User Account Control (UAC) should be disabled as shown below:

User Account Control settings

Do your synchronizing tools function in two directions?

Yes. There are two types of bidirectional synchronization for the moment: non-simultaneous and simultaneous.

  • Non-simultaneous both-way synchronization enables to replicate data firstly from source to destination (for example, from MS SQL to MySQL), then in the reverse direction. The synching process can be performed in one-way direction with the addition of missing records in destination database (Insert type of sync) or with the replacement of records changed in source database (Update type of sync). In this case for bidirectional synchronization you should create 2 sessions performed in sequence from A to B and backwards.
  • Simultaneous bi-directional synchronization allows updating both databases (source and destination) at the same time with no need for creating 2 sessions.

NOTE: It is available only in tools with trigger-based synchronization method with the “Bidirectional sync” option checked at the Customization stage.

Read more about how Bi-Directional Synchronization works

I noticed that the speed of synchronization is slow. How to accelerate it?

There are many reasons why the synchronization speed may slow down. The principal one is the presence of primary keys in synchronizing tables. One more cause of deceleration is a quantity of records and fields. And finally, the third decisive reason that matters is data type. For example, if a record includes several empty fields, the speed of operation will be considerably faster in comparison with records containing binary data.

Three synchronization types (Insert, Update, Drop) are available. They can be used separately or together.

Update sync type estimates the similarity and the difference between records and then replaces modified records in a destination database with ones from source DB.

Drop and/or Insert types are intended for comparison of Missing and Additional records but these synchronization options make a comparison of Primary Keys' array, not every record

Read more information about Insert, Update, Drop and Mixed synchronization types

Choose the one of the following ways of solving the synchronization speed issue:

1. Activate triggers checking "Use history table for synchronization" option at the Customization stage.
Trigger based synchronization works for the following databases:

More information on how triggers work.

2. Use filters to specify only needed set of data for synchronization.

3. Select the tables that require more time to be synchronized and move them to separate session for synching.

4. If there are no Primary keys in database, please add them. Note that presence of Primary keys can be checked out at the Customization stage.

5. Please check synchronizing options in your session before starting scheduling.

NOTE: Take into account that keeping your databases in conformity, you should activate "Overwrite database" and "Overwrite table" options at the Customization step.

Why synchronization doesn’t work properly?

Structures of databases you’re going to synchronize should be exactly the same for accurate and error-free synchronization process.

The modified data is not being updated during synchronization. Why?

In general, the basis of the correct synching process is the availability of primary keys in the structure of the tables you’re going to synchronize. Please make sure whether primary keys are existed in a database and check they are included in the synchronization process.

Why synchronization doesn't work for new tables, rows, columns added after saving a session?

You should activate "Automatic structure update in the session" option at the execution step. When this option is on, database structure in the session will be updated automatically.

Note: Save the current session before enabling this feature. When starting that session next time all new tables, fields, rows will be transferred without data loss.

When Drop sync option is activated, data filtering doesn’t work. Why?

Drop synchronization type is used for deleting records from a destination on condition that those records have been removed from a source. So, it’s impossible to create filters for non-existent records, as they are available to be applied only for source.

If I don’t need triggers, how can I remove them from synchronization process?

If an option "Use history table for synchronization" at the Customization stage is checked, synchronizer creates subsidiary table to store changes in the source database and three triggers which help catching modifications. To delete triggers including "History_store" table, just uncheck “Use history table for synchronization” option and commit the session.

Your source database will be cleaned up from all subsidiary data right after session closure.

How can I configure Microsoft SQL Server 2005/2008/2008 R2/2012 Express?

All MS SQL Server 2005/2008/2008 R2/2012 Express editions could be specified as source and target database. Follow step-by-step instructions with screenshots for configuring MS SQL Express server.

How to configure permissions for MS SQL clients?

The following step-by-step guidance will demonstrate how to set the necessary permissions for MS SQL connection and to specify SQL server properties.

SQL Server related products. I’ve got the error ‘object was open’. How to fix it?

1. First of all, make sure you have the latest version of Microsoft Data Access Components (MDAC)installed.

2. Try to increase query timeout in the ‘Remote server connections’ property.

Remote server properties

3. One more advice on how to resolve this issue in synchronizing tools is using the 'Quantization' option . As the chances of errors occurring is higher in large tables, apply this feature to a table that has the 'object was open' error.

I’m trying your Oracle related products but some errors occur when connecting to an Oracle database.

You can follow the manual describing several errors related to Oracle connection and instructions on how to resolve them.

Click here to open the manual.

MySQL time-out error appears when converting a table of large size. What should I do?

The following MySQL settings should be revised.

1. MySQL server timed out and cancelled the connection. How to overcome it:

The wait_timeout variable represents the amount of time that MySQL will wait before killing an idle connection. Try to increase its value in your configuration file my.cnf. Finally, MySQL server should be restarted.

2. Server dropped a wrong or too large block of data.

If a packet sent to MySQL Server is too large this error can occur. MySQL decides that something wrong has happened and interrupts connection immediately. Try to increase the value of max_allowed_packet in my.cnf to eliminate that problem.

Which versions of Microsoft Access are supported?

Access 2000 and higher versions of MS Access are supported. (.mdb or .acdb files).

I’ve got “Error 2003: Can't connect to MySQL server on 'localhost' (10061)” given by the Access to MySQL converter.

The mentioned error implies that you possibly have MySQL server access restrictions. Please contact your sysadmin to get necessary privileges/ access rights. As well, check your firewall configuration. And finally, changing your MySQL server settings might be helpful.

Error 1452: Cannot add or update a child row: a foreign key constraint fails.

The error 1452 occurs only in a trial version of the MS Access to MySQL synchronizer. The tool is unable to build the accurate structure of foreign keys because of 50 first records limitation.

Why have I received this “Error 1005: Can't create table... “?

MySQL server returns this error in the following cases:

  • The referenced table is not an InnoDB table type;
  • Other table contains the referenced field that is non-unique;
  • Other table includes the referenced field with a dissimilar type to other id.

Note: In MySQL before v.5.0, the error also means that there is no index specified on the field id.

How to get rid of “Error 1062 Duplicate entry '1' for key 1”?

  • "Insert sync" and "Update sync" options should be activated for all tables.
  • At least, one unique primary key field per synchronizing table should be specified. Otherwise, the synchronization is not performed properly.

“ERROR 2013: Lost connection to MySQL server during query” appears. How to overcome it?

Usually, this error comes up because the connection to MySQL server during query execution is timed out. In this case increasing the remote query timeout may help avoid this error. If the error still occurs consult your server administrator for assistance.

What does Run-time error '3044' mean?

MS Access returns this error when linked tables in Access database cannot be found in accordance with the defined path.

Is it necessary to have MS Access installed for proper working of your tools?

This is an essential requirement for all Access-related converters and synchronizers. You should have MS Access installed on a computer where you have an Access database file.

Why Additional extra views have been created in destination database when converting from MS SQL database to MySQL?

MySQL Server is limited to creation a view with a subquery in the FROM clause. To overcome this limitation we create a view for each of the subquery. Every view is called by the name of this query + name alias + View.
Find More info at Stackoverflow

How to speed up Excel converters.

Uncheck "skip empty rows" option at the "Database structure configuration" window.

Trial versions limitations

Converters: It transfers 50 initial records only for each database table. All character data after 50 first records is watermarked with ***TRIAL*** text.

Synchronizers: When Trigger-based sync option is activated, it synchronizes 5 first records, the rest are not affected but show the actual time of the process. If not, the tool synchronizes only 50 first records for each database table. When copying data without syncing options checked, 50 first records are converted for each database table. All character data after 50 initial records is watermarked with ***TRIAL*** text.

What are the advantages of premium support?

Premium support is provided for 30% of the original price together with purchasing or upgrading of our program within a year. However, the maintenance checkbox is optional. Technical support is guaranteed to everybody but the premium priority is given to customers who have paid for it.

If you pay the maintenance fee with a product, your ticket or message will get the high priority and the appropriate status for reply. Usually, premium technical support and assistance is rendered within one working day. We’re constantly observing the queue and providing help in time. In short, you save your valuable time.

Why does the program fail to register my information after updating? What about the upgrade policy?

If you’re a holder of our program license you have the right to obtain minor and major upgrades absolutely for free in the course of year after the registration date. At the expiration of 1-year period you may renew the license for the additional annual term at a discounted price. However, it does not mean that you must upgrade in a year, you’re able to use a version purchased once and updated during a free period as much as you wish.

What is the difference between 1 Developer License and 1 Site Wide License?

Your choice of a license type depends on a number of developers which will be using a converter / synchronizer simultaneously.

1 Developer License gives the right to use a converter / synchronizer only on one system or a desktop computer terminal all at once.

1 Site Wide License is identical to corporate license and means that a program can be used by an unlimited number of developers at a single physical address.

How many Licenses should I purchase if I have a major database and minor ones to be converted / synchronized?

You need to purchase one license only for your principal machine despite of a number of conversion / synchronization sessions. You’re able to set up the program on any computer that has connection to a server. Following this scenario you should create several sessions. See the picture below.

major database with minor dbs syncronization