Migrating Structural Metadata History and Settings

When migrating from FMR 10 to version 11 it is possible to migrate metadata via an exported SDMX JSON file as an alternative to using the Backup and Restore as discussed in this article. The Backup and Restote too is only available in FMR V11.3 and higher so this option is particulary useful if you are upgrading to V11, V11.1 or V11.2.

However it is possible to preserve the following information from FMR 10 by copying certain tables into the database of a new FMR 11 installation:

• All metadata structures
• The individual history of each structure
• Many of the settings
• Some of the audit information

The instructions are detailed below. Please take care with your database names to not apply changes against the old version 10 database. The database for FMR 11 should be an empty database.

For information about the purpose of each table in FMR, please refer to this page .

Instructions

To achieve this task we want to copy particular tables from the FMR 10 database to a clean database for FMR 11 and then make modifications to those tables before attempting to start FMR 11. The following instructions detail how to make an intermediary SQL file and edit that file.

Create a SQL file of database tables

Using the database tool of your choice (for example SQLYog) connect to your FMR 10 database. Create a file which is the SQL Dump of the table Structure and Data for the following tables (note the spelling of particular tables):

• registry_audit
• registry_maintainable_sdmx
• registry_maintainables
• registry_settings
• registry_settings_dv
• registry_settings_ser
• sdmx_backup
• sdmx_transaction
• sdmx_transaction_item
• tx_backup

How you perform this task is down to the individual tool and the database you are using.

For MySQL databases, the tool SqlYog has a convenient way to achieve this. Via the menu option: Database -> Backup/Export -> Backup Table(s) as SQL Dump... within the modal that appears, select only the tables listed above. Ensure the radio button "Structure and Data" is checked. And save this to a single file.

The file produced may be quite large - the size depends on the content of your Registry.

Edit the generated SQL file

View the generated file in an editor of your choice. Notepad will be sufficient for this, but feel free to use any editor you choose.

Any occurrences of your FMR version 10 database within the file will need to be changed to reference the FMR version 11 database. It is important to change these values otherwise when you execute the SQL file it will operate against the wrong database. Once these changes have been made save the file.

By way of example, generating a file from SqlYog will create a file containing 3 occurrences of the FMR version 10 database. These are:

• In an intial comment
• In a "CREATE DATABASE" statement
• in a "USE" statement

It is important to change these to the new FMR 11 database otherwise the script will run against the wrong database.

Load the SQL file into your database

Now it is time to load the modified file against the version 11 database. At this stage this database should be empty. How the file is loaded and executed is down to the tool you are using.

Using SqlYog use the menu option: Database -> Import -> Execute SQL script. In the modal that appears select the file and click "Execute". Wait whilst the file executes.

Once it has finished check the output for errors. If there are no errors your version 11 database should now contain the 10 tables listed above.

Make final modifications to the tables and data

Before starting FMR the following modifications to the tables will need to be performed. Again how this is achieved is down to the tools you are using.

NOTE: ensure that you are operating on your FMR 11 database. If necessary prepend SQL commands with a USE statement for your database. For example:

USE `FMR_11_DB`;

Modify the Settings Table

It is important that particular information is removed from the Registry Settings table. This information is:

• Any stored passwords - FMR 11 uses a stronger encryption technique than FMR 10, so previous passwords cannot be used.
• The "installed version". By doing this FMR will start on the Install Wizard.
• The Registry "Server URL" - this needs to be specified on the second page of the Install Wizard anyway, but removing it ensures it needs to be re-entered.

To achieve this run, the following SQL command. Ensure you are running against the correct database.

DELETE FROM registry_settings WHERE NAME = 'installed.version';
DELETE FROM registry_settings WHERE NAME = 'registry.url';
DELETE FROM registry_settings WHERE NAME LIKE 'EXCEL_TEMPLATE_PWD_%';
COMMIT;

Modify the Transaction Item Table

The structure of the table sdmx_transaction_item has changed in FMR 11. This table has a column "id" which in version 10 was of type "number". This must be changed to be of type VARCHAR(255) and also should not be null.

How you achieve this change varies depending on your database and your tool. The following commands are supplied for the following database types and are supplied for your reference.

MySQL:

 ALTER TABLE sdmx_transaction_item MODIFY COLUMN id VARCHAR(255) NOT NULL;

SQL Server:

 ALTER TABLE sdmx_transaction_item ALTER COLUMN id VARCHAR(255)

Oracle:

 ALTER TABLE sdmx_transaction_item MODIFY id VARCHAR(255)

Start FMR 11

Your FMR database for version 11, should now contain 10 tables and you should have made the modifications specified above. It is now time to start the Tomcat that will be running FR11 and which will re-direct you to the install wizard.

• Start the Tomcat running your FMR version 11.
• On step 1 of the Install Wizard specify the details for your version 11 FMR database.
• The Registry should now show install page 2. If it doesn't then the wrong database has been supplied.
• On install wizard step 2 enter the "server url" and credentials password for the root user.
• Complete the install wizard.
• The front page of the Regsitry should now be shown.

Checking that Structures were Migrated

Once you have performed the above you should find that the structures and individual history have been migrated over. Please check your web server log file "FusionRegistry.log" and look for any unusual errors. To check the structures and settings have been migrated and are available, please check the following pages:

• On the front page of the Registry ensure that the appropriate version is shown
• Export Structures / Bulk Actions - all of your structures should be listed here
• Select an individual item (e.g. a Data Structure Definitiion). Locate a structure that has been modified by selecting it in the top table. Ensure that the Revisions drop-down shows the correct date for the revisions. Check that the "Compare Revision" control works correctly.

Log on as the root user.

• Activity -> Structure Submissions should show the structures and the history of each
• Server Integrity - there should be no structures listed here (this page shows illegal structures)
• Settings pages - should show the settings from the previous Registry

If you have issues, please store your Fusion Registry log file and get in contact with Metadata Technology for further advice.