OK, back to PathName() with AlwaysOn, which I started on in the previous blog post. PathName() in SQL Server 2012 doesn't return the computer name by default ("AlwaysOn1" or "AlwaysOn2" in my example) but returns the VNN name (virtual network name). That is, it returns the availability group "listener share" name. In my case, the PathName() would start with \\AlwaysOnAG1. There's an additional option in SQL Server 2012 PathName(), that allows you to return the current replica name. So, when "AlwaysOn1" is the current primary replica, it returns that; when we fail over to "AlwaysOn2", that's what name is returned. In addition, GetFileNamespacePath() and FileTableRootPath() always return the availability group listener name, they don't have an option to return the current replica name.

Finally, I'll discuss using AlwaysOn, FilePath(), T-SQL access and secondary replicas.In theory, ADO.NET 4.02 (and, of course, SQLNativeClient 11ODBC/OLE DB and Microsoft JDBC 4.0 driver) allows you to declare your ApplicationIntent as ReadOnly or ReadWrite. ReadWrite is the default. Adding either ApplicationIntent=ReadOnly or ApplicationIntent=ReadWrite in the SSMS Connection dialog is accepted, but either one allows connection to the secondary replica. This is because, by default, secondary replicas are configured with "Readable Secondary=Yes" rather than "Readable Secondary=Read-intent". Configuring a secondary as "Readable secondary=Read-Intent" disallows connections that do not specify ApplicationIntent=ReadOnly in the connection string. Obviously, connecting as "ReadWrite" to a ReadOnly replica errors out if you try and do something other than read. Check out Sunil's excellent blog post on the motivations for these secondary replica settings.

Now that we're connected (in T-SQL) to the secondary replica, let's try and get the PathName() from here:

SELECT Name, file_stream.PathName() FROM NWTab;

Msg 33447, Level 16, State 1, Line 1
Cannot access file_stream column in FileTable 'NWTab', because FileTable doesn't support row versioning. Either set transaction level to something other than READ COMMITTED SNAPSHOT or SNAPSHOT, or use READCOMMITTEDLOCK table hint.

Now, this was surprising…I checked the snapshot info in sys.databases and also DBCC USEROPTIONS. Neither snapshot isolation level was turned on or being used. And setting the iso level to, say, repeatable read or even the dreaded read uncommitted didn't help. But using the READCOMMITTEDLOCK did. Hmmm…

But of course, Sunil had the answer for this one too…"To avoid this blocking completely, all transaction isolation levels used in an application on the secondary replica are mapped transparently to Snapshot Isolation…" And this behavior is more obliquely documented in the BOL, here. But Sunil's blog post does go on to say "You may ask how about locking hints? Well, all locking hints are ignored." But the READCOMMITTEDLOCK locking hint does work in this case (modulo the effects it might have on performance, as Sunil mentions). So at this point I wonder if the READCOMMITTEDLOCK locking hint on a FileTable has a special dispensation (other locking hints, do, in fact, fail to have the desired effect), but I'll need to revisit this in 2012 RTM. BTW, it's only using the file_stream column of the FileTable that causes this, other columns work fine. But PathName() and GetFileNamespacePath() are methods on the column, so you can't use these, or access the column in T-SQL, on readonly secondaries without the hint.

Back to BOL comment, changing the NON_TRANSACTED_ACCESS to READ_ONLY (on the primary replica) changes the behavior on the secondary as promised, but now I can't write to the FileTable using the file system. As expected.

Two more things to mention. Firstly, Msg 33447 isn't a mistaken error message. You can, of course, attempt to access the file_stream column in a FileTable from the *primary* replica using Snapshot isolation. Then you'll receive the error, and changing the iso level WILL help. When enabling read-committed snapshot or snapshot you DO get this useful (but slightly misleading, IMHO) message on the ALTER DATABASE DDL statement: "When the FILESTREAM database option NON_TRANSACTED_ACCESS is set to FULL and the READ_COMMITTED_SNAPSHOT or the ALLOW_SNAPSHOT_ISOLATION options are on, T-SQL and transactional read access to FILESTREAM data in the context of a FILETABLE is blocked.". That's not quite true; if the options are on and you *USE* snapshot iso levels, you'll get the error. Changing the iso level to a non-snapshot iso level or using the READCOMMITTEDLOCK hint WILL succeed on the primary.

Finally, remember that "ordinary" (i.e. non-FileTable) tables containing filestream columns DO support snapshot isolation levels as of SQL Server 2008 R2. It should be only the FileTable, with its non-transacted access, that has this behavior with snapshot isolation on the primary and readonly secondaries. And, in fact, this does test out to be the case, an "ordinary" filestream column works fine, even in the same database.

Enough? I even turned moderated blog comments back on (against my better judgement, the amount of spam comments is amazing), if this is a useful discussion vehicle. Or, you know where to find me… Cheers.

NB: Since writing this post, I discovered this post on Availability Group and Filetable – http://dangerousdba.blogspot.com/2012/07/filetable-with-alwayson-ags-bug.html. Thanks Rick!

@bobbeauch