Introduction
By default, FME Flow is deployed with a PostgreSQL database as its backend. When deploying FME Flow in a distributed way, it is possible to use a different backend database. In this article, we describe how to change the default FME Flow backend database in a distributed Windows deployment on Azure using Terraform to an Azure SQL database.
Using a newly created Azure SQL database
In the FME Flow (Distributed Deployment, Windows), an Azure-managed PostgreSQL database is created and used as FME Flow backend database. This database is configured by a script that is running during the launch of a new FME Flow Core VM. The configuration script running on the FME Server Engine VMs also requires database connection information so the FME Engine can communicate with the database. The parameters used for the configuration are:
- Database hostname
- FME Database username
- FME Database password
- Admin database user
- Admin database password
The database hostname is an output of the Azure SQL Terraform module, which can be used instead of the default PostgreSQL database. Here is the module for Azure SQL to be used with FME Flow:
In the Terraform example using an Azure SQL database, the FME and admin database usernames & passwords are chosen by the user. The admin credentials are used in the Azure SQL Terraform module to create the database. Both credential sets and the database hostname are then passed into the configuration scripts for FME Flow Core and Engine. It is recommended to manage all credentials used in a deployment in Azure Key Vault. Here are examples of the configuration scripts for Azure SQL:
These scripts need to replace the existing scripts on the Windows VM images. With Terraform, this is done by using Azure SQL-specific Azure virtual machine scale set (VMSS) modules utilizing the “custom_data” attribute in the VMSS modules:
This allows FME Flow to use an Azure SQL database without modifying the Windows VM images.
The main.tf file in the Terraform example indicates, in commented sections, which variables and modules are required to use an Azure SQL database.
Using an existing Azure SQL database
The most important prerequisite for using an existing database with the distributed FME Flow deployment is network connectivity between the database and the backend VNet created for the FME Flow deployment. There are different ways to accomplish this. In the default configuration, a network rule is added to the database server.
To use an existing Azure SQL or PostgreSQL database, the existing resources can be imported into the Terraform configuration. This way, the existing resources and any changes to them can also be managed by Terraform. Another option is to only provide the necessary variables in the variables.tf to make sure the FME core and engines can connect to the database.
Additional Resources
Please consider posting to the FME Community Q&A if you have further questions or issues that are not addressed in this article. There are also different support channels available.
The FME Flow documentation provides guidance on setting up multiple FME Flow databases.
You may also find it useful to browse other resources or forum questions regarding load balancers in case this addresses our concerns.