MSSQL

Hyperscience supports the following implementations of MSSQL:

• On premise

  • For a list of supported MSSQL versions, see Infrastructure Requirements (Production).

• Azure SQL Managed Instance

  • Supported in Hyperscience v28 and later

  • To learn more, see Microsoft’s What is Azure SQL Managed Instance?

  • For information on how to create a managed instance, see Microsoft’s Quickstart: Create an Azure SQL Managed Instance.

    • Because Azure SQL Database does not support Servicer Broker, Hyperscience does not support Azure SQL Database.

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  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  SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE

3.  Enable Service Broker.

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:

• Using SQL Server login credentials.

• In v32.0.4 and later, using an Azure Active Directory (Azure AD) domain user and password to connect to Azure SQL Server Managed Instance service.

• In v32.0.4 and later, using an Azure Directory Federation Services (ADFS) domain user and password to connect to Azure SQL Server Managed Instance service.

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

• ALTER DATABASE permissions

• VIEW SERVER STATE permissions

• ALTER ANY CONNECTION 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:

• Enable Always On availability groups:

  MultiSubnetFailover=Yes

• Use a domain user for authentication:

  Authentication=ActiveDirectoryPassword

• Configure TLS:

  Encrypt=Yes

• Add certificate information:

  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. Configure TLS.

In order to enable TLS, 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. 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., [email protected])>>
FORMS_DB_PASS=<password_of_user_created_in_Step_5>
FORMS_DB_HOST=<host_URL>
FORMS_DB_PORT=<port>

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)), 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.