OpenLM Database migration – KB4025f
By default, OpenLM provides an embedded Firebird database as part of its basic installation.
Customers may want to migrate their tables to an external database, when they become too large for the embedded database to handle. OpenLM recommends considering migration when the Firebird database size approaches 1 GB.
Supported database types
OpenLM supports the following external database types: MS-SQL Server, Firebird Server, and MySQL. The OpenLM Data migration tool may migrate between any of these Database types to any other type (e.g. from the default embedded Firebird to the external MS-SQL Database).
Where do I get the OpenLM Database migration tool from?
The OpenLM Database migration tool can be downloaded from here.
Possible migration types:
The possible database migration formats are as elaborated in the following table:
SQL Server Firebird
|Source database||Destination database|
|SQL Server||SQL Server|
- The machine running the database migration process needs to comply with the following minimal hardware and software requirements:
- 64 bit Windows 7/8/10 or Windows Server operating systems.
- 8Gb RAM.
- Allocated storage space should be triple the size of the original database (Necessary only during the migration process itself)
- Prior to applying any changes to the OpenLM database, stop the “OpenLM Server” service, and backup the active database.
- The migration process may be quite lengthy and time consuming. You may consider running it on the target database machine, to avoid network delay. In that case, you will need to apply the above to the machine hosting the target database, and have a local copy of the source database there.
- When migrating a default Firebird database to another database type (e.g. MS-SQL), download and install the latest version of the Firebird Server 2.5 on the source Firebird database machine.
- Make sure your OpenLM license enables data migration: In the EasyAdmin web application click ‘Start’ → ‘Administration’ → “OpenLM License”, and ensure that your preferred external database entry (DB_Firebird, DB_MS-SQL, DB_MySQL) is available, and the quantity is non-zero. If otherwise – please contact the OpenLM Sales department to arrange for such a license.
- Create a blank destination database.
- The user running the database migration tool must possess administrative read / write privileges on the destination database.
- In case of migration to MS SQL Server, shrink the destination database, as explained in the documentation here.
- It is recommended to set the target database to be case sensitive.
- If are also using the OpenLM Reporting Hub on the same machine, then you should make sure to remove the variable “KETTLE_HOME” from your environmental variables.
- For MySQL, use this creation script: CREATE SCHEMA `openlm_database_name` DEFAULT CHARACTER SET utf8mb4;
- For MySQL databases, please run the script line:
SET SQL_SAFE_UPDATES = 0;
in order to avoid the “1175 you are using safe update mode …” error message.
- Upgrade your OpenLM system to the latest version available. Please consult the following documents.
For embedded Firebird databases:
For external databases:
Migrating the OpenLM database
- In the ‘Services’ window, stop the “OpenLM Server” service for the duration of the database migration process.
- Obtain a copy of the migration tool zip file (Data Migration.zip), and unzip it
- Run “Upgrade OpenLM Database from earlier versions” on the blank destination database.
- Activate the batch file “run-gui.bat”, located in the “Data Migration” folder.
- Fill in the database connection details as required, with the source database on the left and destination database on the right.
- Click the ‘Start’ button, and check the migration progress by viewing the bottom scrolling log.
See the image below for clarification:
Working with the newly migrated database
1. Open the “OpenLM Database configuration tool” (Windows’ Start → OpenLM → OpenLM Database Configuration), and point to the new database. See examples below for MS-SQL and MySQL databases:
2. Stop and start the OpenLM Server service.
That’s it !
OpenLM is now configured to work with your new Migrated Server database. For further information or support – please address email@example.com.