I'm experimenting with snapshot isolation level. Whenever I try to access a linked server, I get an error:
Remote access is not supported for transaction isolation level "SNAPSHOT"
I wasn't able to find anything meaningful in BOL or Google on this issue.
The limitation seems rather frustrating. Is there a workaround, other than caching all the remote data locally?
If you dig into the internals of how snapshot isolation is implemented in SQL Server, the reason why remote access is not supported becomes clear.
When snapshot isolation is enabled, up to 14 bytes of versioning information are appended to each row that is modified. The additional bytes contain the transaction sequence number of the transaction that modified the row, along with a pointer to the versioned row in tempdb.
A transaction running at snapshot isolation will see the version of a row that existed at the point it started (that exists at the time the first statement is executed, rather than BEGIN TRAN). It is the transaction sequence number that is used to identify the correct version in the version store. As there is no correlation between the sequence numbers on your local and remote server, it's impossible for the remote server to retrieve the correct version.
There are undoubtedly ways a SQL Server to SQL Server connection could manage this first issue, should the SQL development team decide it was worth doing. But there is a second problem, how would you deal with removing versions from the version store?
The background process responsible for cleanup again uses the transaction sequence number to determine if a row version is still required. This requires nothing more than identifying the oldest running transaction sequence number and removing any versions that are older than this. With a remote connection... sounds ugly, which is why it isn't supported.
Could a different approach be taken, perhaps enforcing REPEATABLE READ or SERIALIZABLE on the remote connection? No, not without compromising consistency as the row at the remote server could not be locked at the time your local transaction started. You could not rely on the behaviour, compromising ACID compliance.
Row Versioning Resource Usage and SQL Server 2005 Row Versioning-Based Transaction Isolation contain most of the salient detail.
How best to workaround this will depend on what you're trying to achieve. If you can expand on your specific scenario we can explore the alternative approaches.
