MSSQL

MSSQL

Hyperscience supports the following implementations of MSSQL:

Configuring MSSQL

To configure MSSQL for your Hyperscience instance, follow these steps.

1. Create your database with case-sensitive collation.

Microsoft SQL Server defaults to case-insensitive collation. Hyperscience requires the MSSQL database ( FORMS_DB_NAME in the example configuration below) to be created with case-sensitive collation.

CREATE DATABASE <FORMS_DB_NAME> COLLATE SQL_Latin1_General_CP1_CS_AS

2. Configure the isolation level.

The isolation level of the database should be set to READ_COMMITTED_SNAPSHOT with the following command:

ALTER DATABASE <FORMS_DB_NAME> SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE

3. Enable Service Broker.

Does not apply to Azure SQL Managed Instance
If you are using Azure SQL Managed Instance, Service Broker is enabled by default and cannot be disabled.

If you are using MSSQL on premise, enable Service Broker with the following command:

ALTER DATABASE [<FORMS_DB_NAME>] SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE

To automatically apply the ALTER statement for both the isolation level and Service Broker, add the following to the “.env” file:

SQLSERVER_SKIP_DB_INITIALIZATION=false

However, please note that doing so may cause deployment failure in some cases.

4. Create a SQL Server user for Hyperscience.

You can connect Hyperscience to SQL Server in one of the following ways:

The user you create for Hyperscience must have the following permissions:

Adding the VIEW SERVER STATE and ALTER ANY CONNECTION permissions allows us to monitor and periodically clean up any unnecessary database sessions that may be preventing submissions in Hyperscience from processing in a timely manner.

5. Configure SQLSERVER_OPTIONS_EXTRA_PARAMS parameter.

The SQLSERVER_OPTIONS_EXTRA_PARAMS parameter allows you to configure the following options:

MultiSubnetFailover=Yes

Authentication=ActiveDirectoryPassword

Encrypt=Yes

Encrypt=No

TrustServerCertificate=Yes

You need to add the SQLSERVER_OPTIONS_EXTRA_PARAMS parameter to the “.env” file. You can delimit multiple options by using a semicolon. The options’ order does not matter.

Here is an example of defining multiple options:

SQLSERVER_OPTIONS_EXTRA_PARAMS=Authentication=ActiveDirectoryPassword;MultiSubnetFailover=Yes

In the sections below, you can find more information about each of the above-mentioned options.

a. Enable Always On availability groups.

If using Always On availability groups, ensure Service Broker is enabled, and add the MultiSubnetFailover=Yes option to the SQLSERVER_OPTIONS_EXTRA_PARAMS parameter. Here is an example of adding the MultiSubnetFailover=Yes option to the SQLSERVER_OPTIONS_EXTRA_PARAMS parameter:

SQLSERVER_OPTIONS_EXTRA_PARAMS=MultiSubnetFailover=Yes

b. Use a domain user for authentication.

To use a domain user for authentication, you need to add the Authentication=ActiveDirectoryPassword option to the SQLSERVER_OPTIONS_EXTRA_PARAMS parameter. Here is an example of adding the Authentication=ActiveDirectoryPassword option to the SQLSERVER_OPTIONS_EXTRA_PARAMS parameter:

SQLSERVER_OPTIONS_EXTRA_PARAMS=Authentication=ActiveDirectoryPassword

Azure AD users are created and managed directly within the Azure Active Directory cloud service, whereas ADFS federates an on-premise Active Directory instance to Azure Active Directory.

c. [v41 and earlier] Configure TLS.

In order to enable TLS in v41 and earlier, TLS must be configured for your server. You also need to add the Encrypt=Yes option to the SQLSERVER_OPTIONS_EXTRA_PARAMS parameter. Here is an example of adding the Encrypt=Yes option to the SQLSERVER_OPTIONS_EXTRA_PARAMS parameter:

SQLSERVER_OPTIONS_EXTRA_PARAMS=Encrypt=Yes

d. [v42 and later] Disable TLS.

In v42 and later, the database connection uses TLS encryption by default. This change is due to the upgrade of the ODBC driver to v18. For more information, see Microsoft’s ODBC DSN and Connection String Keywords and Attributes (opens in new tab).

To disable TLS, do one of the following:

SQLSERVER_OPTIONS_EXTRA_PARAMS=Encrypt=No

e. Add certificate information.

If you're using a self-signed certificate, you can configure your authentication process to trust the certificate without validating it. To do so, add the TrustServerCertificate=Yes option to the SQLSERVER_OPTIONS_EXTRA_PARAMS parameter. Here is an example of adding the TrustServerCertificate=Yes option to the SQLSERVER_OPTIONS_EXTRA_PARAMS parameter:

SQLSERVER_OPTIONS_EXTRA_PARAMS=Encrypt=Yes;TrustServerCertificate=Yes

6. Add MSSQL variables.

The following variables should also be included in the “.env” file:

FORMS_DB_TYPE=mssql
FORMS_DB_NAME=<name_of_database_from_Step_1>
FORMS_DB_USER=<username_of_user_created_in_Step_5> <<If you’re using an Azure AD or ADFS user, you need to include the user’s domain after the username. (e.g., james@example.com)>>
FORMS_DB_PASS=<password_of_user_created_in_Step_5>
FORMS_DB_HOST=<host_URL>
FORMS_DB_PORT=<port>

In v32 and later, you can choose to store your system-level credentials in a secrets manager. To learn more about our secrets-management integration, see Secrets Management.

Proactively set up transaction logs and monitor the amount of disk space the logs are using. If you've configured your SQL Server's recovery model with recovery_model=full (see Microsoft's Recovery Models (SQL Server) (opens in new tab)), the size of the transaction logs can grow indefinitely unless you take action to periodically shrink them. To learn more about shrinking the log file's size, see Microsoft's Manage the size of the transaction log file.