Database Guide › SQL Server Guidelines › Upgrading SQL Server › Upgrading to SQL Server 2005 › Add Support for Snapshot Isolation Locking

Add Support for Snapshot Isolation Locking

Snapshot isolation is a new locking model introduced in SQL Server 2005. It provides improved concurrency handling to reduce contention between Read and Write operations. If you upgrade a SQL Server 2000 server to SQL Server 2005 on a CMS or gateway, you must manually add support for snapshot isolation.

Note: Because the infrastructure service is stopped, there is no wgninfra ‑exec command to perform this task.

To add support for snapshot isolation

1. On the CA DLP server, stop the infrastructure service plus any other CA DLP services that may be running.
2. Locate the script wgn_snapshot_iso.sql.

   Find this in the \System\Scripts\MSSQL subfolder in the CA DLP installation folder.

3. Run the script using a database tool such as Microsoft SQL Server Management Studio.

   When you run the script, you must have access to the entire database. The infrastructure service is already stopped (see step 1), but you must also ensure there are no other connections open to the database.