Error setting READ_COMMITTED_SNAPSHOT on database - j5 - 28.0 - Installation & Upgrade - Hexagon

When using MS SQL, setting the isolation level to 'READ_COMMITTED_SNAPSHOT' when connecting to a high availability cluster listener leads to an error.

Errors like the following are seen in the j5 Application log file but j5 still starts up and appears to work:

Error setting READ_COMMITTED_SNAPSHOT on database J5DBEgpTest: ('42000', '[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The operation cannot be performed on database "J5DBEgpTest" because it is involved in a database mirroring session or an availability group. Some operations are not allowed on a database that is participating in a database mirroring session or in an availability group. (1468) (SQLExecDirectW); [42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]ALTER DATABASE statement failed. (5069)')

j5 performance is substandard when on the default isolation level but it's not readily apparently what the cause of the problem is.

The solution is to do the following manually:

For a local SQL installation

1. Disable high availability.

2. Set ALL of the Customer Cluster databases to READ_COMMITTED_SNAPSHOT:

   ALTER DATABASE <Database> SET SINGLE_USER WITH ROLLBACK IMMEDIATE

   ALTER DATABASE <Database> SET READ_COMMITTED_SNAPSHOT ON

   ALTER DATABASE <Database> SET MULTI_USER

3. Enable high availability.

For an Azure SQL instance

The solution provided above does not work for Azure SQL instances as you are unable to run these SQL commands. Instead, update the Read_Committed_snapshot using SQL Server Management studio to configure this setting:

1. Right click on the Database.

2. Click Properties.

3. Click Options.

4. Find Read Committed Snapshot from list of options and change the value from False to True.

5. Click OK and this should update the option successfully.