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

  1. 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%'

  2. 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

  1. Stop YSoft SafeQ Management Service, YSoft Infrastructure Service and YSoft SafeQ LDAP Replicator services on all Management nodes.

    1. If Payment System is in use, stop also YSoft SafeQ Payment System service.

  2. 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 commands
    ALTER 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

  3. 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

  4. 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

  5. Start YSoft SafeQ Management Service, YSoft Infrastructure Service and YSoft SafeQ LDAP Replicator services again.

    1. 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.