Migration from MSSQL to MariaDB

Microsoft SQL Server is no longer supported by Relution. With an MSSQL database, Relution aborts the start with the message Microsoft SQL Server databases are not supported by Relution.

The following instructions describe how to migrate a Relution installation in version 5.24.2 from MSSQL to MariaDB using the HeidiSQL GUI. If support is needed, it is available from the Relution Helpdesk Team.


Prerequisites

  • Version: Relution 5.24.2 is the last version that runs on Microsoft SQL Server. Older installations are updated to 5.24.2 before the migration. The MariaDB schema (step 6, --database-migration-only) is created with exactly the same version 5.24.2, not with 5.25.0 or later, because the DATABASECHANGELOG table is not exported. Newer versions are installed only after the data import and the functional check have been completed successfully. The intermediate versions described under Update → must be observed.
  • MariaDB: Relution 5.24.2 requires at least MariaDB 10.5.2, current Relution versions require MariaDB 10.11. With an older MariaDB version, Relution aborts the start. MariaDB is only supported until the end of 2027, PostgreSQL is recommended (see System requirements →). For Docker installations, the subsequent switch is described under Migration from MariaDB to PostgreSQL →.
  • Java: The environment variable JAVA_HOME is set. Without JAVA_HOME, relution-foreground.bat exits without a message.

Migration

The migration takes place in two parts: first, Relution creates the schema in the new database, then the data is transferred with HeidiSQL.

Create the schema

  1. Relution is shut down.

  2. A MariaDB server is provided and the my.cnf is configured (cnf template →).

  3. A new, empty database with the character set utf8mb4 and the collation utf8mb4_general_ci is created with HeidiSQL. The schema (tables and views) is not created manually, but by Relution in step 6.

  4. The old and the new database are connected in HeidiSQL.

  5. In the application.yml, the values type, url, username and password under relution.database are changed to the new database:

    relution:
      database:
        type: mariadb
        url: jdbc:mariadb://<db_server>:3306/<db_name>?useServerPrepStmts=true
        username: <user>
        password: <password>
    
  6. Relution is started once against the new database so that only the database migration runs. For this purpose, the environment variable RELUTION_ARGUMENTS is set in a command prompt in the Relution installation directory, and relution-foreground.bat is then started:

    set RELUTION_ARGUMENTS=--database-migration-only
    windows-bin\relution-foreground.bat
    

    Arguments appended directly to relution-foreground.bat have no effect, because the script only passes the content of RELUTION_ARGUMENTS on to Java. The variable only applies to this command prompt. After the run, the command prompt is closed or set RELUTION_ARGUMENTS= is executed so that Relution does not start in migration-only mode again later.

  7. Relution exits automatically once the schema has been created. The log is then checked to verify that the migration has completed without errors.

Transfer the data

The data is written directly from the old to the new database using the HeidiSQL export function.

  1. Right-click on the old database
  2. Database export
  3. The tables DATABASECHANGELOG, DATABASECHANGELOGLOCK and JGROUPSPING as well as all views are deselected
  4. Max INSERT length to 10k
  5. Everything is deselected for databases and tables
    HeidiSQL database export dialog with databasechangelog and views deselected for MSSQL migration
  6. Output: new database server
  7. Database: new database
  8. Start of the migration

After the migration

  1. Relution is started.
  2. The Relution log is checked to verify that the start works correctly. The location of the log files is described under Relution Logs →.
  3. After logging in to Relution, it is checked whether resources can be loaded.
  4. Under Devices → Inventory, it is checked whether the device list (database views) loads.

How helpful was this article?