FILESTREAM and Snapshot Isolation Mode in SQL 2008

FILESTREAM and Snapshot Isolation mode are incompatible in SQL 2008.

In SQL 2008 R2 however...

http://msdn.microsoft.com/en-us/library/ms173763.aspx
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
go
ALTER DATABASE Operations SET ALLOW_SNAPSHOT_ISOLATION ON

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.