Search

FME Flow - Creating the FME Flow Database on SQL Server

SteveatSafe
SteveatSafe

Introduction

Applies to FME Flow Distributed Installations using SQL Server or Express Installs that are reconfigured to use SQL Server as the FME Flow Database.  This article is for environments that do not allow the default schema to be used (dbo) and must use another schema name. 

Our documentation instructs the FME Flow Administrator (or DBA) to run SQL scripts against the SQL Server Instance that creates the database, login, and user as well as the tables and indexes. (reference Documentation here )
If you run the scripts as is they will do the following:

  • Create a database called 'fmeflow'
  • Create the FME Flow tables in the database called fmeflow, in the default schema dbo.
  • Create a login called 'fmeflow'
  • Create a user called 'fmeflow' with a password.

FME Flow admins and SQL Server DBA's often tweak these scripts to change the username, password, login or database name.  Those changes are supported.    

However, some company standards require the database objects (tables & indexes) to be created in a schema other than the default dbo. In this case, the shared scripts do not indicate how this can be done and if FME Flow even supports the schema change.  The short answer is yes, FME Flow does support this configuration change however it is necessary to alter the user and set the default schema to the new schema value.  
The statements are:

CREATE SCHEMA [<schema>] 
GO
ALTER USER <username> WITH DEFAULT_SCHEMA = <schema>
GO
as an example: 
CREATE SCHEMA [fmeflow]
GO
ALTER USER fmeflow WITH DEFAULT_SCHEMA = fmeflow
GO
The issue is the sqlserver_createDB.sql won't create the objects in the new schema.  One way around this is for the DBA to log in as the new 'fmeflow' user and run this script - but will need to recognize the 'Create Database' must be commented out because that will have had to be created already.    Let's look at what changes are needed in the scripts for this to execute properly.

As mentioned, it isn't enough to edit the existing scripts and add the above lines.  It won't quite work that way because the schema won't exist until the database can be created.  
So you'll need to edit the scripts and move the create database out of the sqlserver_createDB.sql script and into the sqlserver_createUser.sql script. I found that I had to use the following sequence:
  1. As DBA: Create the new 'fmeflow' database
  2. As DBA: Create schema, login, and user within the new 'fmeflow' database.
  3. As the new 'fmeflow' database User, log in and run the sqlServer_createDB.sql (with the 'create database' line commented out). This creates the objects in the new schema.
I hope this helps someone.
PS: There may be a way for the schema to be referenced in a script like sqlserver_createDB.sql and not have it referenced before each table but I didn't find a way in my research.  If you know, please comment!​​​​
  

 

Was this article helpful?

Comments

1 comment

Date Votes
  • Comment author
    rudy_v
    • Edited

    Hi,

    Links in page 404 error.

    Also why do I need to install FME Flow, to get the SQL Server Scripts. I want to create the Database and install the tables before I install FME Flow. I should be able to download the SQL Server Scripts for each version of FME Flow

    0

Please sign in to leave a comment.