Configuring MS SQL Server Database Snapshot Isolation

Snapshot isolation enhances concurrency for OLTP applications. It is enabled automatically for new installations since Dispatcher Paragon MU23. If you are updating from older SafeQ 6 version (less than MU23), then it needs to be enabled manually.

How to check shapshot 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 SafeQ databases, then please continue with the next section.

How to set up the database

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

  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. Start Dispatcher Paragon Management Service and Dispatcher Paragon Infrastructure Service services again.

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