# Database Authentication & Scaling

## Database authentication

### Using Windows Authentication instead of SQL Server Authentication

You can set up your SQL Server connection to use Windows Authentication (SSPI) instead of SQL Server Authentication for the WorkflowGen back-end database and other external databases. This SQL authentication mode provides additional security, since no credentials are stored in the `web.config` file.

To configure Windows Authentication, use one of the following connection strings in the WorkflowGen `web.config` file, located in the `DRIVE:\Inetpub\wwwroot\wfgen` folder:

```
Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI; 
```

**OR**

```
Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;
```

You'll also need to modify the WorkflowGen application pool identity for a service account. To do this:

1. In **IIS Manager**, right-click on the **WorkflowGen** application pool, then select **Advanced Settings**.
2. In the **Process Model** section, select **Identity**, then click the `...` button.
3. Select **Custom Account**, then click **Set...**
4. Enter your credentials, then click **OK**.
5. Make sure the application pools for the other WorkflowGen web applications (`auth`, `graphql`, `hooks`, `scim`,`ws`, and `webforms`) match the `wfgen` application pool.
6. Make sure the custom account has read/write and modify permissions for the WorkflowGen SQL database and the WorkflowGen files and folders.
7. Use the same account as the application identity to run the WorkflowGen engine and directory synchronization services.

For more information on SQL Server connection strings, see <https://www.connectionstrings.com/sql-server/>.

## Database scaling

### Overview <a href="#database-scaling-overview" id="database-scaling-overview"></a>

The database scaling feature allows for the addition of database servers in order to dramatically improve server performance and response times. The additional read-only replica database servers will be used as dedicated read-only servers (SELECT SQL queries). The read-only database servers are replicated from the existing primary database using the SQL Server Replication service.

### Requirements

#### Overview

* SQL Server 2014 or later for all database servers
* SQL Server Replication feature must be installed on all SQL Server instances
* SQL Server Agent must be installed and configured on all SQL Server instances

### Setup and configuration <a href="#database-scaling-setup-and-configuration" id="database-scaling-setup-and-configuration"></a>

#### Overview

This section provides a general procedure for configuring the SQL Server Replication service on all the SQL Server instances and enabling the WorkflowGen web server in database scaling mode. This procedure is an example based on one WorkflowGen instance and two database server instances architecture.

If your current architecture differs from this example, you can adapt the procedure and some specific configuration options according to your needs.

Your current WorkflowGen environment must meet the following prerequisites:

* The initial WorkflowGen database is already installed on the primary database instance.
* The read-only database instance does not contain an existing WorkflowGen database.
* The WorkflowGen web server is fully configured and allows access to the Administration Module with a WorkflowGen administrator account.

#### SQL Server configuration options

It is mandatory to update the SQL Server's `max text repl size` configuration option with a higher value in order for the database replication to function properly. We recommend setting this to the maximum value of `2147483647` instead of the default value of `65536`.

Run the following SQL statements in the source (primary) and destination (read-only) SQL Server database instances (this example uses SQL Server 2008):

```
EXEC sp_configure "max text repl size", 2147483647;
GO
RECONFIGURE;
GO
```

For more information, see <http://msdn.microsoft.com/en-us/library/ms186225.aspx> and select your version of SQL Server from the drop-down menu.

#### SQL Server replication services

To set up a publication on the primary database:

1. Open SQL Server Management Studio.
2. Connect to the primary database instance.
3. Open the **Replication** node.
4. Right-click on **Local Publications** and choose **New Publication**. The **New Publication Wizard** will open. Click **Next**.
5. Select `WFGEN` in the **Publication Database** list. Click **Next**.
6. Select **Transactional publication** in the **Publication type** list. Click **Next**.
7. Check all **Tables** and **Views** (optional if the WorkflowGen Reporting Module is installed in the current database instance) in the **Articles** list. Click **Next**.
8. The **Article Issues** dialog box will open if you selected **Views** in the previous step. Click **Next**.
9. The **Filter Table Rows** dialog box will open. There is no need to filter the data in the publication. Click **Next**.
10. The **Snapshot Agent** dialog box will open. Check **Create a snapshot immediately and keep the snapshot available to initialize subscriptions**. Click **Next**.
11. In **Snapshot Agent Security**, provide valid service or administrator credentials for the Windows account. Click **Next**.
12. Check **Create the publication**. Click **Next**.
13. Enter the **Publication name** `WFGEN_PUB`.
14. Click **Finish**. If all is successful, close the **New Publication Wizard**.

To set up a subscription on the read-only database:

1. Open SQL Server Management Studio.
2. Connect to the read-only database instance.
3. Open the **Replication** node.
4. Right-click on **Local Subscriptions** and choose **New Subscription**. The **New Subscription Wizard** will open. Click **Next**.
5. Select `<Find SQL Server Publisher...>`.
6. Connect to the primary database instance.
7. Select `WFGEN_PUB` in the **Databases and publications** list. Click **Next**.
8. Select **Run each agent at its Subscriber (pull subscriptions)**. Click **Next**.
9. In the **Subscription Database** column, select `<New database...>`.
10. Enter the `WFGEN` as the **Database name** and click **OK**. Click **Next**.
11. In **Distribution Agent Security**, provide valid service or administrator credentials for the Windows account. Click **Next**.
12. In **Synchronization Schedule**, select **Run continuously**. Click **Next**.
13. In **Initialize Subscriptions**, select **Immediately**. Click **Next**.
14. Select **Create the subscription(s)**. Click **Next**.
15. Click **Finish**. If all is successful, close the New Subscription Wizard.
16. Create and add the SQL user `WFGEN_USER` to the `WFGEN` read-only database security with `db_datareader` and `db_datawriter` permissions.

{% hint style="info" %}
The `WFGEN_USER` account will be used in the connection string for the `WFGEN` read-only database in WorkflowGen.
{% endhint %}

### Database scaling in WorkflowGen

#### Enable the multi-database mode in WorkflowGen:

1. In the WorkflowGen **Administration Module**, open the **Configuration Panel** and select the **General** tab.
2. In the **Read-only database connection string field**, enter the WorkflowGen connection string of the read-only database instance.
3. Check **Enable** on **Multi-database** to turn on the database scaling feature.
4. Check each of the specific **Portal** pages and **Modules**:
   * If unchecked, it will use the primary database.
   * If checked, it will use the read-only replica database.
5. Save and close.

### References <a href="#database-scaling-in-workflowgen-references" id="database-scaling-in-workflowgen-references"></a>

* For more information on SQL Server replication, see <http://msdn.microsoft.com/en-us/library/ms151198.aspx> and select your version of SQL Server from the drop-down menu.
* For more information on the SQL Server `max text repl size` option, see <http://msdn.microsoft.com/en-us/library/ms186225.aspx> and select your version of SQL Server from the drop-down menu.