HomeWindows InstallationUsing a SQL Server databaseEnable Snapshot Isolation

3.6. Enable Snapshot Isolation

Overview

The default 'Transaction Isolation' used by SQL Server means that when one user is updating a table row, every other user will block when trying to read that row until the transaction writing to the row is commited or aborted.  This isn't how Isolation works in the older PureCM 'Native' database which allowed a single user to update a row whilst still allowing multiple users to read from it.

The 'Snapshot Isolation' (sometimes referred to as 'row versioning') in SQL Server 2005 and later essentially allows the single writer/multiple readers concept to work and thus provides better scalability for products like PureCM.

Implementation

To enable Snapshot Isolation for your database, you woud typically run a script similar to that below (which assumes a database name of 'purecm').  You would typically execute such a script in 'SQL Server Management Studio' in a query window.

ALTER DATABASE purecm SET allow_snapshot_isolation ON
ALTER DATABASE purecm SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE purecm SET read_committed_snapshot ON
ALTER DATABASE purecm SET MULTI_USER

SELECT is_read_committed_snapshot_on, snapshot_isolation_state_desc,snapshot_isolation_state FROM sys.databases WHERE name='purecm'

This page was: Helpful | Not Helpful