# Configuration

## Overview

This section explains how to completely configure a WorkflowGen database container. Everything is configurable via an environment variable.

{% hint style="info" %}

* This image is based on [microsoft/mssql-server-windows-express](https://hub.docker.com/r/microsoft/mssql-server-windows-express/) (SQL Server 2017) for the Windows LTSC 2019 version, and on [mcr.microsoft.com/mssql/server](https://hub.docker.com/_/microsoft-mssql-server) (SQL Server 2019) for the Linux (Ubuntu 18.04) version.
* To support Windows LTSC 2019, the base image has been rebuilt using the [open sourced Dockerfile](https://github.com/microsoft/mssql-docker/tree/master/windows/mssql-server-windows-express) and hosted on Advantys' Docker Hub repository. Therefore, the actual base image is [advantys/mssql-server-windows-express](https://hub.docker.com/r/advantys/mssql-server-windows-express).
* The Windows version of this image is intended for development and testing only. For production workloads, use the Linux version.
  {% endhint %}

## Environment variables

### Variables specific to the base images

Some variables are available in the base images that provide functionalities related to SQL Server. For the Linux version, see the [mcr.microsoft.com/mssql/server](https://hub.docker.com/_/microsoft-mssql-server) Docker Hub page and the [Deploy and connect to SQL Server Linux containers](https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-docker-container-deployment?view=sql-server-ver15\&pivots=cs1-bash) Microsoft article. For the Windows version, see the [microsoft/mssql-server-windows-express](https://hub.docker.com/r/microsoft/mssql-server-windows-express/) Docker Hub page.

{% hint style="info" %}
Some environment variables in the base images are required. For example, you have to provide a value for the `SA_PASSWORD` environment variable.
{% endhint %}

### Variables specific to WorkflowGen

The WorkflowGen database container adds special environment variables to enable additional features related to WorkflowGen. The following table provides descriptions for each of them:

<table data-header-hidden><thead><tr><th valign="top">Variable</th><th valign="top">Description &#x26; values</th></tr></thead><tbody><tr><td valign="top"><strong>Variable</strong></td><td valign="top"><strong>Description &#x26; values</strong></td></tr><tr><td valign="top"><code>WFGEN_DATABASE_NAME</code></td><td valign="top"><p>The name of the WorkflowGen database</p><p></p><p><strong>Default value</strong>: <code>WFGEN</code></p></td></tr><tr><td valign="top"><code>WFGEN_DATABASE_CONTAINMENT</code></td><td valign="top"><p>Sets the WorkflowGen database to contain database users for more portability (see <a href="https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/contained-database-authentication-server-configuration-option?view=sql-server-ver15">contained database authentication server configuration option</a> for more information)</p><p></p><p><strong>Possible values</strong>: <code>Y</code> (default), <code>N</code></p></td></tr><tr><td valign="top"><code>WFGEN_DATABASE_USER_USERNAME</code></td><td valign="top"><p>The username of the database user that has access to the WorkflowGen database</p><p></p><p><strong>Default value</strong>: <code>WFGEN_USER</code></p></td></tr><tr><td valign="top"><code>WFGEN_DATABASE_USER_PASSWORD</code></td><td valign="top"><p><strong>Required variable</strong></p><p><br>The password of the database user that has access to the WorkflowGen database</p></td></tr><tr><td valign="top"><code>WFGEN_DATABASE_FILE_PATH</code></td><td valign="top"><p><strong>Do not modify for Linux version</strong></p><p></p><p>Internal path to the <code>.mdf</code> and <code>.ldf</code> database files inside the container</p><p></p><p><strong>Default value:</strong></p><ul><li>Windows: <code>C:\wfgen\sql</code></li><li>Linux: <code>/var/opt/mssql/data</code></li></ul></td></tr><tr><td valign="top"><code>WFGEN_ADMIN_USERNAME</code></td><td valign="top"><p>The username of the WorkflowGen administrative user</p><p></p><p><strong>Default value</strong>: <code>wfgen_admin</code></p></td></tr><tr><td valign="top"><code>WFGEN_ADMIN_PASSWORD</code></td><td valign="top"><p><strong>Required variable</strong></p><p></p><p>The password of the WorkflowGen administrative user</p></td></tr><tr><td valign="top"><code>WFGEN_AUTH_APPLICATION</code></td><td valign="top"><p>Indicates if the authentication method of WorkflowGen is applicative or not</p><p></p><p><strong>Possible values</strong>: <code>Y</code> (default), <code>N</code></p></td></tr></tbody></table>

## Format-based environment variables

### Secrets

When using an orchestrator such as Kubernetes, you'll probably want to secure secrets using their built-in secret management tools. Follow the specific guide for your orchestrator to know how to create a secret.

For Kubernetes, see <https://kubernetes.io/docs/concepts/configuration/secret/>.

It's recommended to inject secrets into WorkflowGen containers as files because they won't be exposed as environment variables and they'll be removed from the container when it's stopped or removed.

{% hint style="info" %}
Secrets management is only possible using an orchestrator.
{% endhint %}

In order to get the secret value in the file, you need to suffix any environment variable you want to get the value of in this way with `_FILE` and set its value to the path of the file containing the secret. The container will then get the value in the file at the specified path and set the environment variable without the suffix with that value.

For example, let's say you want to set sa account password in SQL Server to `strong(!)Pass` using the environment variable `SA_PASSWORD`, but you want to use a secret for the value. All you have to do is suffix the environment variable name with `_FILE`so that it becomes `SA_PASSWORD_FILE`. Then, set the value of this variable to the path of the file containing the password.

#### 📌 Example with Docker Swarm orchestrator

{% tabs %}
{% tab title="PowerShell" %}

```powershell
# Create the secret for the license serial number
'strong(!)Pass' | docker secret create SA_PASSWORD -

# Create the container service in Docker Swarm
docker service create `
    # ...
    --env WFGEN_APP_SETTING_ApplicationSerialNumber_FILE=/run/secrets/SA_PASSWORD `
    --secret SA_PASSWORD `
    # ...
    advantys/workflowgen-sql:7.18.3-ubuntu-18.04
```

{% endtab %}

{% tab title="Bash" %}

```bash
# Create the secret for the license serial number
echo 'strong(!)Pass' | docker secret create SA_PASSWORD -

# Create the container service in Docker Swarm
docker service create \
    # ...
    --env WFGEN_APP_SETTING_ApplicationSerialNumber_FILE=/run/secrets/SA_PASSWORD \
    --secret SA_PASSWORD \
    # ...
    advantys/workflowgen-sql:7.18.3-ubuntu-18.04
```

{% endtab %}
{% endtabs %}

For Kubernetes, you would create a ConfigMap that complements your secret like this:

```yaml
apiVersion: v1
kind: ConfigMap
metadata:
  name: database-config
data:
  SA_PASSWORD_FILE: /mnt/secrets/SA_PASSWORD
---
apiVersion: v1
kind: Secret
type: Opaque
metadata:
  name: database-secret
data:
  # "c3Ryb25nKCEpUGFzcwo=" is the base64-encoded value of "strong(!)Pass"
  SA_PASSWORD: 'c3Ryb25nKCEpUGFzcwo='

```

Then, you would map the ConfigMap as environment variables and mount the secret as a volume like this:

```yaml
apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: wfgen-database
spec:
  selector:
    matchLabels:
      # ...
  template:
    metadata:
      labels:
        # ...
    spec:
      containers:
        - name: database
          image: advantys/workflowgen-sql:7.18.3-ubuntu-18.04
          # ...
          envFrom: # ConfigMap as environment variables
            - configMapRef:
                name: database-config
          # ...
          volumeMounts:
            # ...
            - mountPath: /mnt/secrets # Mount Secret as a volume
              readOnly: true
              name: secrets
      volumes:
        # ...
        - name: secrets # Mount Secret as a volume
            secret:
              secretName: database-secret
```

## Using an orchestrator

### Kubernetes

Kubernetes also has a built-in object called **ConfigMap** to manage pod configuration. See the [Configure a Pod to Use a ConfigMap](https://kubernetes.io/docs/tasks/configure-pod-container/configure-pod-configmap/) Kubernetes article for more information and how to use it. You should use this object to configure environment variables for WorkflowGen.

You can also manage sensitive information by protecting it further in the orchestrator in a secure area. See the [Secrets](https://kubernetes.io/docs/concepts/configuration/secret/) Kubernetes article for more information and instructions on how to use it. You should use this object to protect sensitive information such as the WorkflowGen license key, usernames, passwords, cryptographic keys, API keys, etc.

## Using an external configuration manager

Some popular configuration managers support Docker containers out-of-the-box. Here are a few links to their specific documentation to get you started:

### Chef

* [Docker Cookbook](https://supermarket.chef.io/cookbooks/docker)
* [Chef and Kubernetes](https://www.chef.io/solutions/chef-and-kubernetes/)

### Ansible

* [Ansible and Docker](https://www.ansible.com/integrations/containers/docker)
* [k8s – Manage Kubernetes (K8s) objects](https://app.gitbook.com/s/w6fOTdny4suR6kqgplYs/preparation)

### Puppet

* [How to run Puppet in Docker](https://www.puppet.com/blog/puppet-docker)
* [How to Install and Configure Kubernetes with the Puppet Kubernetes Module](https://app.gitbook.com/s/w6fOTdny4suR6kqgplYs/preparation)

## About security features

The Linux version of the database has some security features that can be used to improve the overall security of the database. For more information on security features in SQL Server for Linux, see [Deploy and connect to SQL Server Linux containers](https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-docker-container-deployment?view=sql-server-ver15\&pivots=cs1-bash#nonrootuser) in the Microsoft SQL Server documentation.

The Windows version of the container doesn't have the security features of the Linux version. It's advised to use the Windows version only for development and testing purposes.

## Performance & high availability

You can configure replication with this container, but you'll have to make a custom image. For more information about making a custom image, see the [Custom Image](https://docs.workflowgen.com/docker/custom-image) page of this section. For more information about configuring replication in SQL Server, see [Configure a SQL Server Availability Group for read-scale on Linux](https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-availability-group-configure-rs?view=sql-server-ver15) in the Microsoft documentation.

## Use with Kubernetes

You should always deploy the database container within a StatefulSet so that each container has its own separated storage. It also ensures that each container has a unique DNS name inside the cluster so that it can be found easily by other containers. You can also configure each of the instances based on an incremental identifier so that you can set a read/write instance and several read-only instances. Here's an example of a simple StatefulSet deployment with the WorkflowGen database container:

```yaml
apiVersion: v1
kind: Service
metadata:
  name: database
spec:
  type: ClusterIP
  clusterIP: None
  ports:
    - port: 1433
      targetPort: mssql
      protocol: TCP
      name: mssql
  selector:
    app.kubernetes.io/name: workflowgen
    app.kubernetes.io/component: database
---
apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: database
spec:
  replicas: 1
  serviceName: database
  selector:
    matchLabels:
      app.kubernetes.io/name: workflowgen
      app.kubernetes.io/component: database
  template:
    metadata:
      labels:
        app.kubernetes.io/name: workflowgen
        app.kubernetes.io/component: database
    spec:
      terminationGracePeriodSeconds: 10
      nodeSelector:
        kubernetes.io/os: linux
      containers:
        - name: database
          image: advantys/workflowgen-sql:7.18.3-ubuntu-18.04
          imagePullPolicy: Always
          securityContext:
            runAsUser: 0
            runAsGroup: 0
          resources:
            requests:
              memory: "1Gi"
              cpu: "500m"
            limits:
              memory: "2Gi"
              cpu: "1"
          envFrom:
            - configMapRef:
                name: database-config
          ports:
            - name: mssql
              containerPort: 1433
          livenessProbe:
            initialDelaySeconds: 30
            timeoutSeconds: 5
            exec:
              command:
                - pwsh
                - -NoLogo
                - -NoProfiles
                - /usr/local/bin/healthcheck.ps1
          readinessProbe:
            initialDelaySeconds: 20
            timeoutSeconds: 5
            exec:
              command:
                - pwsh
                - -NoLogo
                - -NoProfiles
                - /usr/local/bin/healthcheck.ps1
          volumeMounts:
            - mountPath: /var/opt/mssql
              name: sqldata
            - mountPath: /mnt/secrets
              readOnly: true
              name: secrets
      volumes:
        - name: secrets
            secret:
              secretName: database-sec
  volumeClaimTemplates:
    - metadata:
        name: sqldata
      spec:
        accessModes:
          - ReadWriteOnce
        storageClassName: default
        resources:
          requests:
            storage: 100Gi
```

You can use this example as a starting point to configure multiple database containers. For more information about StatefulSets, see [StatefulSets](https://kubernetes.io/docs/concepts/workloads/controllers/statefulset/) in the Kubernetes documentation. You might need custom code to be able to configure multiple instances properly. See the [Custom Image](https://docs.workflowgen.com/docker/custom-image) page of this section for more information about how you can add custom code to the container.