Tuesday, March 20, 2012

FILESTREAM and Snapshot Isolation Mode in SQL 2008

FILESTREAM and Snapshot Isolation mode are incompatible in SQL 2008.

In SQL 2008 R2 however...

In SQL Server 2008 R2, snapshot isolation has been extended to support FILESTREAM data. Under snapshot isolation mode, FILESTREAM data read by any statement in a transaction will be the transactionally consistent version of the data that existed at the start of the transaction.

Why?  Good info here from the original SQL 2008 documentation: http://msdn.microsoft.com/en-us/library/cc949109.aspx
When FILESTREAM data is accessed through the Win32 APIs, only the read-committed isolation level is supported. Transact-SQL access also allows the repeatable-read and serializable isolation levels. Furthermore, using Transact-SQL access, dirty reads are permitted through the read-uncommitted isolation level, or the NOLOCK query hint, but such access will not show in-flight updates of FILESTREAM data.

Sample failure in SQL 2008 (any edition):
use master
Msg 5099, Level 16, State 3, Line 1
ALTER DATABASE failed because the READ_COMMITTED_SNAPSHOT and the ALLOW_SNAPSHOT_ISOLATION options cannot be set to ON when a database has FILESTREAM filegroups. To set READ_COMMITTED_SNAPSHOT or ALLOW_SNAPSHOT_ISOLATION to ON, you must remove the FILESTREAM filegroups from the database.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

No comments: