A high availability cluster listener leads to an error when using MS SQL and the isolation level is set to 'READ_COMMITTED_SNAPSHOT'.
j5 starts up and appears to work but errors like the following are seen in the j5 application log file:
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 apparent what the causes the problem.
The solution is to do the following manually:
For a local SQL installation
-
Disable high availability.
-
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
-
-
Enable high availability.
For an Azure SQL instance
The solution provided above doesn't 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:
-
Right-click the database.
-
Select Properties.
-
Select Options.
-
Find Read Committed Snapshot from list of options and change the value to True.
-
Select OK.