Configure the FME Server Database on a Separate Database Server

Database Configuration - Microsoft SQL Server

It is assumed that SQL Server is installed.

One notation used is <FMEServerDir>, which is the installation directory of FME Server. In Windows, this is typically C:\Program Files\FMEServer.

SQL scripts are provided to help with SQL Server database configuration. These SQL scripts are located in the <FMEServerDir>\Server\database\sqlserver\ directory of the FME Server Core machine.

Note: This directory refers to the machine on which the FME Server Core is installed (not the machine on which the database server is installed). To run these scripts, you must transfer this directory to the database machine, or reference it through a UNC path.

If you are upgrading, you should back up any job history you want to keep.

Instructions Using SQL Server Management Studio

Open the SQL Server Management Studio and connect to the SQL Server database engine.
Open sqlserver_createDB.sql and run the script against the fmeserver database, as shown below:

On the Object Explorer pane, expand the Security folder, right-click Login, and then select New Login:

On the Login – New page, click General to open the page shown in the example below.

At the Login name field, enter fmeserver.
Make sure the option button is active for SQL Server authentication.
In the Password field enter the password of your choice, and in the Confirm password field enter the same password again.
Make sure the Enforce password policy is active. If not, click the checkbox to activate it.
If required, click the checkbox for Enforce password expiration to clear it.
At the Default database field enter fmeserver.

On the same Login – New page, click User Mapping to open the page shown in the next figure.

At the Users mapped to this login location, in the Map column click the checkbox to activate it and map the login name to the fmeserver database.
At the Database role membership for location, notice that the fmeserver database is specified and check db_owner to assign that role to this database.
Click OK to close this page.

Database Configuration - Oracle

Before proceeding, you must provide the following:

Install an Oracle database server.
Obtain the Oracle Database JDBC driver. For more information, see https://www.oracle.com/technetwork/database/application-development/jdbc/downloads/index.html.

<FMEServerDir> is the installation directory of FME Server. In Windows, this is typically C:\Program Files\FMEServer.

If you are upgrading, you should back up any job history you want to keep.

Note: If your version of Oracle requires a service name, rather than session ID (SID), for database connections, you must update the DB_JDBC_URL parameter in configuration file fmeCommonConfig.txt accordingly. For more information, see this FME Community article.

Windows

This section describes how to configure an Oracle database. You can configure the Oracle database from other Oracle database tools depending on your personal preference.

For an Oracle database configuration, various SQL scripts are provided to help.

These SQL scripts are located in the <FMEServerDir>\Server\database\oracle directory of the FME Server Core machine.

Note: This directory refers to the machine on which the FME Server Core is installed (not the machine on which the database server is installed). To run these scripts, you must transfer this directory to the database machine, or reference it through a UNC path.

oracle_createUser.sql – This script creates FME Server database user and password, and grants all required permissions to the FME Server database user. By default, the user and password are both named fmeserver.

Note: If oracle_createUser.sql returns an error on Oracle Database 12c, modify the script to allow a common user to be created under the root container, as follows:

CREATE USER C##fmeserver IDENTIFIED BY fmeserver;

Alternatively, modify the script to create a local user in a pluggable database (PDB) container. For example:

ALTER SESSION SET CONTAINER=fmeserver;

CREATE USER fmeserver IDENTIFIED BY fmeserver;

oracle_createDB.sql – This script creates all FME Server related database packages, tables, indexes, views, and triggers.
oracle_dropUser.sql – This script drops the FME Server database user and the database packages, tables, indexes, views, and triggers associated with the FME Server database user.

Using the Command Prompt

To use the command prompt to configure the database, follow these instructions:

Install Oracle SQL*Plus, if you haven’t already.
Open Oracle SQL*Plus and log on as a user with adequate privileges, such as SYSDBA.

In this example, the following parameters were used and entered:

User: SYS AS SYSDBA
Password: fmeserver
String: XX-ORACLE11G:1521/ORCL AS SYSDBA

Create an FME Server database user as follows:

From the SQL prompt, run the oracle_createUser.sql script by entering the following command:

@”<FMEServerDir>\Server\database\oracle\oracle_createUser.sql”

Note: If oracle_createUser.sql returns an error on Oracle Database 12c, modify the script to allow a common user to be created under the root container, as follows:

CREATE USER C##fmeserver IDENTIFIED BY fmeserver;

Alternatively, modify the script to create a local user in a pluggable database (PDB) container. For example:

ALTER SESSION SET CONTAINER=fmeserver;

CREATE USER fmeserver IDENTIFIED BY fmeserver;

By default, the provided SQL script creates an FME Server database user named fmeserver with the password fmeserver. The default TABLESPACE is the Oracle DB configured default. If the created user doesn’t have read/write permissions on the default TABLESPACE, change the default TABLESPACE or create an appropriate TABLESPACE.

This command creates a TABLESPACE on the Oracle DB server called FMESERVER shown in the following example:

CREATE SMALLFILE TABLESPACE FMESERVER DATAFILE 'E:\ORADATA\FMESERVER01.DBF' SIZE 100000K REUSE AUTOEXTEND ON NEXT 2048K MAXSIZE 1024M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO DEFAULT NOCOMPRESS;

By default this command uses the TABLESPACE “FMESERVER” as shown in the following example:

CREATE USER fmeserver IDENTIFIED BY fmeserver DEFAULT TABLESPACE "FMESERVER" TEMPORARY TABLESPACE "TEMP";

When an FME Server database user is created, you can connect to it by logging on again to SQL*Plus.

In this example, the following parameters were used and entered on the user interface:

User: fmeserver
Password: fmeserver

Note: The preceding example assumes your user name and password are fmeserver.

When connected to the FME Server database, run the oracle_createDB.sql script by entering the following command:

@”<FMEServerDir>\Server\database\oracle\oracle_createDB.sql”

Note: If you drop the FME Server database and login user, run the oracle_dropUser.sql script.

Linux

Oracle SQL*Plus is a freeware client for connecting to an Oracle database. You can download it from the Internet using the following address:

http://www.oracle.com/technology/software/tech/sql_plus/index.html

For more information, see the Oracle web site at:

http://www.oracle.com/technology/sample_code/tech/sql_plus/htdocs/demobld.html

The instructions that follow describe how to configure Oracle for use with FME Server. These instructions describe using SQL*Plus for the database setup. Depending on your personal preference, you can also configure the Oracle database using other Oracle tools.

For Oracle configurations, various SQL scripts are provided to help with Oracle database configuration. These SQL scripts are located in the <FMEServerDir>/Server/database oracle/ directory of the FME Server Core machine.

Note: This directory refers to the machine on which the FME Server Core is installed (not the machine on which the database server is installed). To run these scripts, you must transfer this directory to the database machine, or reference it through a UNC path.

oracle_createUser.sql – This script creates FME Server database user and password, and grants all required permissions to the FME Server database user. By default, the user and password are both named fmeserver.

Note: If oracle_createUser.sql returns an error on Oracle Database 12c, modify the script to allow a common user to be created under the root container, as follows:

CREATE USER C##fmeserver IDENTIFIED BY fmeserver;

Alternatively, modify the script to create a local user in a pluggable database (PDB) container. For example:

ALTER SESSION SET CONTAINER=fmeserver;

CREATE USER fmeserver IDENTIFIED BY fmeserver;

oracle_createDB.sql – This script creates all FME Server related database packages, tables, indexes, views, and triggers.
oracle_dropUser.sql – This script drops the FME Server database user and the database packages, tables, indexes, views, and triggers associated with the FME Server database user.

Using the Command Prompt

To use the command prompt to configure the database, follow these instructions:

Install the Oracle Client Tools, which includes SQL*Plus, if you haven’t already.
Run sqlplus and log on as a user with adequate privileges, such as SYSDBA.

For example:

sqlplus SYS/<password>@<Host>:<Port>/<service> AS SYSDBA
Create an FME Server database user. From the SQL prompt, run the oracle_createUser.sql script by entering the following command:

@”<FMEServerDir>/Server/database/oracle/oracle_createUser.sql”

Note: If oracle_createUser.sql returns an error on Oracle Database 12c, modify the script to allow a common user to be created under the root container, as follows:

CREATE USER C##fmeserver IDENTIFIED BY fmeserver;

Alternatively, modify the script to create a local user in a pluggable database (PDB) container. For example:

ALTER SESSION SET CONTAINER=fmeserver;

CREATE USER fmeserver IDENTIFIED BY fmeserver;

By default, the provided SQL script creates an FME Server database user named fmeserver with the password fmeserver. The default TABLESPACE is the Oracle DB configured default.

If the created user doesn’t have read/write permissions on the default TABLESPACE, you need to change the default TABLESPACE or create an appropriate TABLESPACE.

This command creates a TABLESPACE on the Oracle DB server called FMESERVER shown in the following example:

CREATE SMALLFILE TABLESPACE FMESERVER DATAFILE '/ORADATA/FMESERVER01.DBF' SIZE 100000K REUSE AUTOEXTEND ON NEXT 2048K MAXSIZE 1024M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO DEFAULT NOCOMPRESS;

By default this command uses the TABLESPACE “FMESERVER” shown in this example:

CREATE USER fmeserver IDENTIFIED BY fmeserver DEFAULT TABLESPACE "FMESERVER" TEMPORARY TABLESPACE "TEMP";
When an FME Server database user has been created, you can connect to it by logging on again to SQL*Plus or by entering the following command:

CONNECT fmeserver/fmeserver@<Host>:<Port>/<service>;
When connected to the FME Server database, run the oracle_createDB.sql script by entering the following command:

@”<FMEServerDir>/Server/database/oracle/oracle_createDB.sql”

Database Configuration - PostgreSQL

PostgreSQL is an open source database that can be downloaded from the Internet. For more information, see the PostgreSQL web site at:

http://www.postgresql.com

It’s assumed that PostgreSQL is already installed .

One notation used is <FMEServerDir>, which is the installation directory of FME Server. In Windows, this is typically C:\Program Files\FMEServer.

It’s also assumed that you have JRE 5 or higher and PostgreSQL database installed before you proceed further. If you want to use the JDBC driver, there are several steps to perform.

If you are upgrading, you should back up any job history you want to keep.

Windows

This section describes how to configure a PostgreSQL database. You can configure this database from other PostgreSQL database tools, depending on your personal preference.

For a PostgreSQL database configuration, various SQL scripts are provided to assist you. These SQL scripts are located in the following directory:

<FMEServerDir>\Server\database\postgresql

Note: This directory refers to the machine on which the FME Server Core is installed (not the machine on which the database server is installed). To run these scripts, you must transfer this directory to the database machine, or reference it through a UNC path.

postgresql_createUser.sql – This script creates the FME Server database user and password, and grants all required permissions to this user. By default, both the user name and password are fmeserver.
postgresql_createDB.sql – This script creates the FME Server database.
postgresql_createSchema.sql – This script creates all FME Server related tables, indexes, views, and triggers.
postgresql_dropUser.sql – This script drops the FME Server database user.
postgresql_dropDB.sql – This script drops the FME Server database.

Using the Command Prompt

To use the command prompt to configure the database, follow these instructions:

Install PostgreSQL SQL shell (psql) if you haven’t done so already.
Open psql and log on as a user with the appropriate privileges:

Create an FME Server database user as follows:

From the SQL prompt, run the postgresql_createUser.sql script by entering the following command:

\i '<FMEServerDIR>/Server/database/postgresql/postgresql_createUser.sql'

By default, the provided SQL script creates an FME Server database user named fmeserver with the password fmeserver:

Create an FME Server database.

From the SQL prompt, run the postgresql_createDB.sql script by entering the following command:

\i '<FMEServerDIR>/Server/database/postgresql/postgresql_createDB.sql'

By default, the provided SQL script creates an FME Server database named fmeserver and grants all privileges on the database to user fmeserver.

Quit psql and log on as the FME Server database user.

Create the FME Server database schema as follows:

From the SQL prompt, run the postgresql_createSchema.sql script by entering the following command:

\i '<FMEServerDir>/Server/database/postgresql/postgresql_createSchema.sql'

By default, the provided SQL script creates all FME Server related tables, indexes, views, and triggers.

Linux

This section describes configuring PostgreSQL from both the command prompt and a user interface, such as the PostgreSQL Query Browser and the PostgreSQL Administrator.

For PostgreSQL configurations, various SQL scripts are provided to help with PostgreSQL database configuration. These SQL scripts are located in the <FMEServerDir>/Server/database/postgresql/ directory of the FME Server Core machine.

Note: This directory refers to the machine on which the FME Server Core is installed (not the machine on which the database server is installed). To run these scripts, you must transfer this directory to the database machine, or reference it through a UNC path.

postgresql_createUser.sql – This script creates the FME Server database user and password, and grants all required permissions to the FME Server database user. By default the user is named fmeserver and the password is fmeserver.
postgresql_createDB.sql – This script drops the FME Server database and creates a new one with the required FME Server tables. By default this database is named fmeserver. Be careful when running this script because it drops the existing FME Server database.
postgresql_createSchema.sql – This script creates all FME Server related tables, indexes, views, and triggers.

Note: This script must be run as user fmeserver.

postgresql_dropDB.sql – This script drops the existing FME Server database, which is named fmeserver by default.
postgresql_dropUser.sql – This script drops the FME Server database user.

Using the Command Prompt

To use the command prompt to configure the database, follow these instructions:

Install PostgreSQL Server if you haven’t already.
Open a command prompt and change to the following directory, which contains the PostgreSQL SQL scripts:

<FMEServerDir>/Server/database/postgresql/

Run the postgresql program with the postgresql_createUser.sql script:

psql -d postgres -h <hostname> -p <port> -f postgresql_createUser.sql
Run the postgresql program with the postgresql_createDB.sql script:

psql" -d postgres -h <hostname> -p <port> -f postgresql_createDB.sql
Run the postgresql program with the postgresql_createSchema.sql script, as user fmeserver:

psql -U fmeserver -d fmeserver -h <hostname> -p <port> -f postgresql_createSchema.sql

Note: If you drop the FME Server database and login user, run the postgresql program with the appropriate postgresql_dropDB.sql or postgresql_dropUser.sql scripts.

After Database Creation

On the machine on which the FME Server database server is installed, open <PostgreSQLDir>\data\pg_hba.conf or, on PostgreSQL 10, open <PostgreSQLDir>\data\pg10\pg_hba.conf.

Change the following lines:

host all all 127.0.0.1/32 md5

host all all ::1/128 md5

to:

host all all 0.0.0.0/0 md5

host all all ::/0 md5

Restart the FME Server Database service.