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 Dispatcher Paragon installer since Dispatcher Paragon MU23.
If you are updating from older Dispatcher Paragon version (less than MU23) or if the Dispatcher Paragon 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 Dispatcher Paragon databases, then please continue with the next section.

How to set up the database

  1. Stop Dispatcher Paragon Management Service, Dispatcher Paragon Infrastructure Service and Dispatcher Paragon LDAP Replicator services on all Management nodes.

    1. If Payment System is in use, stop also Dispatcher Paragon 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 Dispatcher Paragon Management Service, Dispatcher Paragon Infrastructure Service and Dispatcher Paragon LDAP Replicator services again.

    1. If Payment System is in use, start also Dispatcher Paragon Payment System service.

See Microsoft documentation of Snapshot Isolation in SQL Server for more information.