FME Flow: 2024.2

Changing the Database Provider for the FME Flow Database

To change the database provider for the FME Flow Database, perform an “in-place” backup and restore procedure:

  1. Backup Your FME Flow Configuration
  2. Configure the New Database Server
  3. Configure the Database Connection
  4. Restart FME Flow
  5. Restore Your FME Flow Configuration
  6. Remove Dependency, Disable, and Stop the Previous Database Service

FME Flow supports PostgreSQL (recommended), SQL Server, or Oracle databases.

Note  PostgreSQL is the recommended database server with FME Flow, offering enhanced performance and stability through targeted optimizations.

Backup Your FME Flow Configuration

Perform a backup of your FME Flow configuration.

Configure the New Database Server

Run the necessary database configuration scripts and post-configuration scripts. Follow the steps in Configure the FME Flow Database on a Separate Database Server.

Configure the Database Connection

Note  

If using an Oracle database server, you must obtain the Oracle Database JDBC driver and place it in the following directory:

  • Windows:
    • <FME FlowDir>\FMEFlow\Utilities\jdbc
  • Linux:
    • /opt/fmeflow/Utilities/jdbc

Driver versions recommended: 12.1 (ojdbc7.jar), 12.2 (ojdbc8.jar), 18.3 (ojdbc8.jar), 19.3 (ojdbc8.jar), 19.3, 19.6, 19.7 (ojdbc8.jar).

Driver versions not recommended: All ojdbc10.jar versions.

For more information, see https://www.oracle.com/technetwork/database/application-development/jdbc/downloads/index.html.

  1. Encrypt the database password you generated in the previous step. In an initial installation of FME Flow, this password is encrypted in installation files by default. However, it is no longer encrypted when the database server is updated. Follow the procedure in Encrypting the FME Flow Database Password.
  2. Locate the fmeDatabaseConfig.txt configuration file.
  3. Under the heading ‘FME SERVER SETTINGS START’, locate the section titled 'Database Connection' and update the parameters for the database you want to use for your repository.

    4. Parameters

    DB_TYPE - Identifies the database server: postgresql, sqlserver, oracle.

    DB_DRIVER - The JDBC driver name used for connecting to the database.

    DB_JDBC_URL - The JDBC URL used for connecting to the database.

    DB_USERNAME - The database user name.

    DB_PASSWORD - The database user password.

    DB_CONNECT_EXPIRY - The database connection expiry time, in seconds.

    DB_SQLSTMTS_PATH - The path to the SQL statement resource bundle.

    Examples

    DB_TYPE=postgresql

    DB_DRIVER=org.postgresql.Driver

    DB_JDBC_URL=jdbc:postgresql://localhost:5432/fmeflow

    DB_USERNAME=fmeflow

    DB_PASSWORD=fmeflow

    DB_CONNECT_EXPIRY=60

    DB_SQLSTMTS_PATH=C:/Apps/FMEFlow/Server/database

    To use a local installation of SQL Server Express

    Prerequisites:

    • In SQL Server Configuration Manager, ensure the Protocols for SQLExpress setting for TCP/IP traffic is set to Enable. (In SQL Server Express, this setting is disabled by default.)
    • Ensure the Windows service SQL Server Browser is enabled and running for SQL Server Express. This service is required for the JDBC driver to locate a dynamic port. Alternatively, use SQL Server Configuration Manager to configure a fixed port, and specify port=<xxxx> in the DB_JDBC_URL parameter setting, where <xxxx> is the value of the specified port. For example, port=1433.
    • In SQL Server Management Studio, set Server authentication to SQL Server and Windows Authentication Mode. This configuration is required because FME Flow uses the DB_USERNAME property to authenticate.

    DB_TYPE=sqlserver

    DB_DRIVER=com.microsoft.sqlserver.jdbc.SQLServerDriver

    DB_JDBC_URL=jdbc:sqlserver://localhost;instanceName=SQLEXPRESS;databaseName=fmeserver;encrypt=true;trustServerCertificate=true

    DB_USERNAME=fmeserver

    DB_PASSWORD=$FMEserver

    DB_CONNECT_EXPIRY=60

    DB_SQLSTMTS_PATH=C:/Apps/FMEServer/Server/database

    Note  To resolve encryption issues due to invalid certificates, specify encrypt=false in place of encrypt=true;trustServerCertificate=true.

    To use a named instance of a SQL Server

    Specify the instanceName property with the DB_JDBC_URL parameter:

    DB_TYPE=sqlserver

    DB_DRIVER=com.microsoft.sqlserver.jdbc.SQLServerDriver

    DB_JDBC_URL=jdbc:sqlserver://<ServerName>;databaseName=<database>;instanceName=<InstanceName>

    DB_USERNAME=fmeflow

    DB_PASSWORD=$FMEflow

    DB_CONNECT_EXPIRY=60

    DB_SQLSTMTS_PATH=C:/Apps/FMEFlow/Server/database

    To use a domain service account instead of a named database user or fmeflow (the default database username)

    Complete the following steps:

    1. Specify the integratedSecurity property with the DB_JDBC_URL parameter. FME Flow uses the Log on as credentials of the FME Flow Core and FME Flow Application Server services to connect to the SQL Server database. Do not set the DB_USERNAME or DB_PASSWORD parameter values.

      2. DB_TYPE=sqlserver

      DB_DRIVER=com.microsoft.sqlserver.jdbc.SQLServerDriver

      DB_JDBC_URL=jdbc:sqlserver://<ServerName>;databaseName=<database>;integratedSecurity=true

      DB_USERNAME=

      DB_PASSWORD=

      DB_CONNECT_EXPIRY=60

      DB_SQLSTMTS_PATH=C:/Apps/FMEFlow/Server/database

    2. Go to directory <FMEFlowDir>\Server\fme\ and complete the following:
      1. Locate the file with a name that begins mssql-jdbc-auth*.dll
      2. Copy the file to the <FMEFlowDir>\Server\lib\fmeutil\ directory.
      3. In the <FMEFlowDir>\Server\lib\fmeutil\ directory, rename the file to sqljdbc_auth.dll

    To specify the database connection port

    Specify the port after <ServerName> in the DB_JDBC_URL parameter. For example:

    DB_JDBC_URL=jdbc:sqlserver://<ServerName>;port=1755;

    To specify an Always On availability group

    If using an Always On availability group, include the MultisubnetFailover property with the DB_JDBC_URL parameter:

    DB_JDBC_URL=jdbc:sqlserver://<ServerName>;databaseName=<database>;MultisubnetFailover=true

    To specify an on-premise database with a System ID (SID)

    Use the following syntax for the DB_JDBC_URL parameter:

    DB_JDBC_URL=jdbc:oracle:thin:@<host>:<port>:<sid>

    For example:

    DB_TYPE=oracle

    DB_DRIVER=oracle.jdbc.driver.OracleDriver

    DB_JDBC_URL=jdbc:oracle:thin:@localhost:1521:orcl

    DB_USERNAME=fmeflow

    DB_PASSWORD=fmeflow

    DB_CONNECT_EXPIRY=60

    DB_SQLSTMTS_PATH=C:/Apps/FMEFlow/Server/database

    To specify an on-premise database with a service name

    Use the following syntax for the DB_JDBC_URL parameter:

    DB_JDBC_URL=jdbc:oracle:thin:@<host>:<port>/<servicename>

    For example:

    DB_JDBC_URL=jdbc:oracle:thin:@hostname.mydomain.com:1521/mydatabase1

    To specify an Oracle Cloud database

    Use the following syntax for the DB_JDBC_URL parameter:

    DB_JDBC_URL=jdbc:oracle:thin:@<ip>:<port>/<servicename>

    For example:

    DB_JDBC_URL=jdbc:oracle:thin:@129.44.44.2:1521/orcl_iad2fd.mycorpz1.mycorp.myvcn.com

Restart FME Flow

Restart all of the FME Flow System Services.

Restore Your FME Flow Configuration

Perform a restore of your FME Flow configuration:

Remove Dependency, Disable, and Stop the Previous Database Service

If the database service from which you migrated is the PostgreSQL database that was included in a default installation of FME Flow (such as an express install), you must remove the dependency of the FME Flow Core on that database, and then disable and stop it. For more information, see Removing the FME Flow Core Dependency on the FME Flow Database.