I have a current customer that is interested in changing their database backend for Laserfiche to SQL instead of Oracle. They have purchased LF RIO and have an existing United system that is Oracle based and has about 20 million images in it currently. They have had some issues w/ their Oracle DB that has caused some major performance issues and has not been something that could be fixed under the current setup. The customer wants to get away from using Oracle for Laserfiche because they have more admin resources available for SQL. Can you tell me if it is possible to switch from Oracle to SQL? I understand that there would need to be a migration of the data to the new db, my main concern is if it would require licensing or software modifications or can we simply create a new repository and use SQL for the DB?
Question
Question
Oracle to SQL migration
Answer
It’s definitely possible! The Microsoft website contains articles specifically designed for migrating Oracle Databases to SQL. I would suggest reading up on the conversion and connect your newly created repository to the new SQL database. They may have some resources available on a scripting process which will automate the majority of this process, and therefore, this would likely be the faster option. You also could export your volumes or briefcases and transfer those to the new repository, but that may take a while judging by the 20 million images needing to be exported and imported.
As for the licensing, that’s more of a question that you should direct to your region’s sales representative. As far as I know, there will need to be license modifications when changing to a different database type.
I tried to the MS tool and I don't recommend it. The main thing you'd need is LF to provide a list of all the data types for each table column side by side for Oracle and SQL.
Another option would be for LF to provide a config for the MS utility that setups up the data type conversions.
If you want to try it:
- I'd verify the data types are mapped properly
- Move data from Oracle to SQL. (no indexes, counters, procedures, etc. These failed)
- Run the LF SQL schema script to build a fresh DB (with all the correct procedures syntax, indexes, etc)
- Try to move the data from the temp SQL DB to the fresh LF DB. May have to drop constraints to do this then re-enable after moving this data via the SQL management tools.
- Manually update all the counters in the LF SQL to match Oracle
- Hope it works.
We decided to go the route of exporting the volumes.
- Set volume to read only.
- Export the volume where its currently stored to minimize the time and avoid copying the images. (DO NOT DETACH).
- Import volume to the LF server running SQL.
You loose entry IDs, so I ran a workflow to capture the current entry IDs in a new field on all docs before exporting the volume.
By tracking the old entry ID you can still look up audit trail history prior to the export date by the old entry ID.
There are a few other steps, but that's the big pieces.
Updates:
1. There is a bug somewhere that impacts export performance. I would bet its unique to Oracle as your DB. The amount of time to export a volume exponentially scales with the number of documents.
Ex: 15k docs = 1.3 hours; 20k docs = 2 hours; 30k docs = 4 to 6 hours; 45k docs = 23 hours.
I created lots of volumes and used workflow to migrate the docs to their volumes. Then, I exported them after hours and imported to the new SQL system. Mark volumes as Read Only in the old system until conversion.
I'm saving less-needed docs to export after conversion so I can run exports during business hours.
2. I should have named the new repository a different name. Since I didn't, IT is setting up login scripts that update registry keys for the client and snapshot to point to the new SQL/Rio server.
Hope that helps!