Configuring the FME Flow Database Connection

Introduction

In a distributed or fault-tolerant installation of FME Flow, you can provide your own database server. For details please see the documentation Provide a Database Server. Depending on your environment, you may need to specify additional configuration in the fmeDatabaseConfig.txt file. 

This article provides connection parameters to handle different database server configurations, mostly for SQL Server and Oracle. In general, additional configuration is not required for PostgreSQL. Parameters are described under the Database Connection header in the fmeDatabaseConfig.txt file.

After making any of these changes to the fmeDatabaseConfig.txt file, FME Flow must be restarted.

PostgreSQL

PostgreSQL connection settings are in the Database Connection section of fmeDatabaseConfig.txt that begins with DB_TYPE=postgresql. 

PostgreSQL database connection

Provide the DB_JDBC_URL connection string, DB_USERNAME, and DB_PASSWORD. The other connection parameters generally do not need to be modified. For example:

DB_TYPE=postgresql
DB_DRIVER=org.postgresql.Driver
DB_JDBC_URL=jdbc:postgresql://<ServerName>:5432/fmeflow
DB_USERNAME=fmeflow
DB_PASSWORD=fmeflow
DB_CONNECT_EXPIRY=60
DB_SQLSTMTS_PATH=C:/Apps/FMEFlow/Server/database

High-availability PostgreSQL installation

If you are connecting to a high-availability PostgreSQL installation, all nodes and their ports must be listed in the connection string before the fmeflow database. For example:

DB_JDBC_URL=jdbc:postgresql://<ServerName1>:5432,<ServerName2>:5432/fmeflow

SQL Server

SQL Server connection settings are in the Database Connection section of fmeDatabaseConfig.txt beginning with DB_TYPE=sqlserver. 

Database connection port

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

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

Unsecured SQL Server

If your SQL Server database server is not secured with a valid SSL certificate, change the  DB_JDBC_URL parameter’s encrypt value from true to false. For example:

DB_JDBC_URL=jdbc:sqlserver://<ServerName>;port=1433;databaseName=fmeflow;encrypt=false;trustServerCertificate=true

Always On availability group

If your SQL Server uses an Always On availability group, add the property MultisubnetFailover=true to the DB_JDBC_URL parameter. For example:

DB_JDBC_URL=jdbc:sqlserver://<ServerName>;port=1433;databaseName=fmeflow;encrypt=true;trustServerCertificate=true;MultisubnetFailover=true

Multi-node Azure high-availability environment

If you are connecting to a high-availability (HA) environment hosted on Microsoft Azure, add the property connectRetryCount=0 to the DB_JDBC_URL parameter. This property-value pair ensures a new node is tried if the first fails. For example:

DB_JDBC_URL=jdbc:sqlserver://<ServerName>;port=1433;databaseName=fmeflow;encrypt=true;trustServerCertificate=true;MultisubnetFailover=true;connectRetryCount=0

Local SQL Server Express

To use a local SQL Server Express, in addition to modifying the database connection parameters, the following prerequisites must be met: 

• 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.

Change the DB_JDBC_URL parameter to use the instanceName of SQLExpress. For example:

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

To resolve encryption issues due to invalid certificates, change encrypt=true to encrypt=false

Named instance of a SQL Server

If you wish to use your SQL Server’s instance name, add the property instanceName to the  DB_JDBC_URL. For example, if your SQL Server’s instance name is MSSQLSERVER:

 jdbc:sqlserver://<ServerName>;instanceName=MSSQLSERVER;databaseName=fmeserver;encrypt=true;trustServerCertificate=true

Domain service account 

To use a domain service account instead of a named database user or the default fmeflow database user, complete the following steps:

1. Specify the integratedSecurity property to 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.
   Example:

   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 the folder <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

Oracle

Oracle connection settings are in the Database Connection section of fmeDatabaseConfig.txt beginning with DB_TYPE=oracle. 

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_JDBC_URL=jdbc:oracle:thin:@localhost:1521:orcl

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

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

RAC and fault tolerant Oracle database

FME Flow can connect to an Oracle RAC (Real Application Clusters). However, we recommend setting the LOAD_BALANCE=off. This setting has been problematic for some environments when enabled. We also recommend Direct Node Access, instead of SCAN or TAF, again because it has caused issues in some environments. If you use SCAN or TAF, and then encounter FME Flow Database connectivity issues, you should exclude these and go to the Direct Node Access method for further testing to see if the issue persists.

Use the following syntax for the DB_JDBC_URL parameter for RAC direct node access:

DB_JDBC_URL=jdbc:oracle:thin:@(DESCRIPTION=
    (FAILOVER=ON)
    (LOAD_BALANCE=OFF)
    (ADDRESS_LIST=
        (ADDRESS=(PROTOCOL=TCP)(HOST=node1.example.com)(PORT=1521))
        (ADDRESS=(PROTOCOL=TCP)(HOST=node2.example.com)(PORT=1521))
    )
    (CONNECT_DATA=
        (SERVICE_NAME=myracdb)
    )
)