Connecting Imixs-Office-Workflow to Microsoft SQL Server

While Imixs-Office-Workflow comes with PostgreSQL as its default database, you can easily configure it to work with Microsoft SQL Server. This guide walks you through the setup process when running Imixs-Office-Workflow on Wildfly.

Setting Up the JDBC Driver

First, you’ll need to configure the Microsoft SQL Server JDBC driver module. Create the following directory structure:

├── modules
│   └── com
│       └── microsoft
│           └── sqlserver
│               └── jdbc
│                   └── main
│                       ├── module.xml
│                       └── mssql-jdbc-12.8.1.jre11.jar

In the /main directory, place the Microsoft JDBC driver and create a module.xml file with this configuration:

<?xml version="1.0" encoding="UTF-8"?>
<module xmlns="urn:jboss:module:1.6" name="com.microsoft.sqlserver.jdbc">
    <resources>
        <resource-root path="mssql-jdbc-12.8.1.jre11.jar" />
    </resources>
</module>

Configuring the Datasource

Next, you’ll need to configure the driver and datasource in your standalone.xml file. Add the following configuration to the datasources subsystem:

...
<subsystem xmlns="urn:jboss:domain:datasources:7.0">
    <datasources>
        <datasource jta="true" jndi-name="java:/jdbc/office" pool-name="office"
                   enabled="true" use-ccm="true">
            <connection-url>${env.MSSQL_CONNECTION}</connection-url>
            <driver>mssql</driver>
            <driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>
            <security>
                <user-name>${env.MSSQL_USER}</user-name>
                <password>${env.MSSQL_PASSWORD}</password>
            </security>
            <validation>
                <check-valid-connection-sql>select 1</check-valid-connection-sql>
            </validation>
        </datasource>
        <drivers>
            <driver name="mssql" module="com.microsoft.sqlserver.jdbc">
                <driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>
            </driver>
        </drivers>
    </datasources>
    .....
....

This configuration connects the datasource to the installed JDBC driver and uses environment variables to establish the connection to your MS SQL Server.

Docker Deployment Example

Here’s how to configure the datasource in your docker-compose.yaml file:

services:

#######################
# Microsoft SQL
#######################
  db:
    image: mcr.microsoft.com/mssql/server:2022-preview-ubuntu-22.04
    environment:
      ACCEPT_EULA: "Y"
      MSSQL_SA_PASSWORD: "yourStrong12345Password"
    volumes: 
      - dbdata:/var/lib/mssql/data

#######################
# Imixs-Documents
#######################
  app:
    image: imixs/imixs-documents:2.1.0
    depends_on:
      - db
    environment:
      MSSQL_CONNECTION: "jdbc:sqlserver://db:1433;databaseName=office;encrypt=true;trustServerCertificate=true"
      MSSQL_USER: "sa"
      MSSQL_PASSWORD: "yourStrong12345Password"
      TZ: "Europe/Berlin"
    ports:
      - "8080:8080"
      - "8787:8787"
    volumes:
      - ./docker/configuration/standalone-mssql.xml:/opt/jboss/wildfly/standalone/configuration/standalone.xml
      - ./docker/configuration/modules/com/microsoft/:/opt/jboss/wildfly/modules/com/microsoft/

With these configurations in place, your Imixs-Office-Workflow installation will be ready to use Microsoft SQL Server as its database backend. This setup provides you with a robust and scalable database solution for your workflow management needs.