FME Version
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:
- 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 - Go to the folder <FMEFlowDir>\Server\fme\ and complete the following:
- Locate the file with a name that begins mssql-jdbc-auth*.dll
- Copy the file to the <FMEFlowDir>\Server\lib\fmeutil\ directory.
- 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)
)
)
Comments
0 comments
Please sign in to leave a comment.