Configuring MS SQL Server Database Snapshot Isolation
Snapshot isolation enhances concurrency for OLTP applications.
It is enabled automatically for new installations where database is created by YSoft SafeQ installer since YSoft SafeQ MU23.
If you are updating from older YSoft SafeQ 6 version (less than MU23) or if the YSoft SafeQ databases were created manually, then snapshot isolation needs to be enabled manually.
If Payment System is in use, then snapshot isolation needs to be enabled for its database manually (if you did not do so before).
How to check snapshot isolation current state
Connect to the SQL Server and run the following query:
SELECT name, collation_name, state_desc, snapshot_isolation_state_desc, is_read_committed_snapshot_on, recovery_model_desc, containment_desc, is_trustworthy_on FROM sys.databases WHERE name like
'%SQDB6%'
If you see that snapshot_isolation_state_desc is OFF for YSoft SafeQ databases, then please continue with the next section.
How to set up the database
Stop YSoft SafeQ Management Service, YSoft Infrastructure Service and YSoft SafeQ LDAP Replicator services on all Management nodes.
If Payment System is in use, stop also YSoft SafeQ Payment System service.
If your database name does not equal SQDB6, please change the name according to your configuration. Connect to SQL Server and run the following commands:
SQL Server commandsALTER
DATABASE
[SQDB6]
SET
ALLOW_SNAPSHOT_ISOLATION
ON
ALTER
DATABASE
[SQDB6]
SET
READ_COMMITTED_SNAPSHOT
ON
ALTER
DATABASE
[SQDB6_IMS]
SET
ALLOW_SNAPSHOT_ISOLATION
ON
ALTER
DATABASE
[SQDB6_IMS]
SET
READ_COMMITTED_SNAPSHOT
ON
If you use separate database for data warehouse, reconfigure it as well:
ALTER
DATABASE
[SQDB6_DWH]
SET
ALLOW_SNAPSHOT_ISOLATION
ON
ALTER
DATABASE
[SQDB6_DWH]
SET
READ_COMMITTED_SNAPSHOT
ON
Same applies for Payment System database, if Payment System is in use:
ALTER
DATABASE
[SQDB6_YPS]
SET
ALLOW_SNAPSHOT_ISOLATION
ON
ALTER
DATABASE
[SQDB6_YPS]
SET
READ_COMMITTED_SNAPSHOT
ON
Start YSoft SafeQ Management Service, YSoft Infrastructure Service and YSoft SafeQ LDAP Replicator services again.
If Payment System is in use, start also YSoft SafeQ Payment System service.
See Microsoft documentation of Snapshot Isolation in SQL Server for more information.