SQL Server 2012 FileTables – AlwaysOn support and PathName()

When I was covering the FileTable-specific functions and methods, I didn't mention PathName(), a filestream-related function, that (naturally) can be used with FileTables. This posting is about PathName(), but, mostly about what happens with FileTable in an AlwaysOn availability group configuration. There were some surprises there. None of the FileTable-specific functions and methods mention AlwaysOn, but PathName() mentions it.
There is an additional parameter you can specify that matters if you use PathName() in an AlwaysOn availability group environment.

Remember that, in addition to FileTable, there have been enhancements to filestream in SQL Server 2012. Namely, you can have multiple filestream containers (filespecs) per-filestream filegroup. This has an effect on the paths returned by PathName(). In SQL Server 2008/R2, PathName() returns a path that looks something like this:

\\ZMV08\MSSQLSERVER\v1\Northwind\dbo\Employees2\Photo\DF707B77-9FA4-4837-91C6-363E23BCEBDD.

In SQL Server 2012, an analogous request would produce a path that looks like this:

\\ZMV04\MSSQLSERVER\v02-A60EC2F8-2B24-11DF-9CC3-AF2E56D89593\FTTest\dbo\Documents\file_stream\D46814E2-633D-E111-982F-000C2959648F\VolumeHint-HarddiskVolume1.

So, for at least one reason, there's a new "version" of filestream namespace names (I'm just looking at the v01 vs. v02 part of the pathname).

So let create an AlwaysOn Availability Group setup to try it out with FileTable and PathName(). Availability group is a new feature in SQL Server 2012 that appears, at first glance, to be an big extension to the database mirroring feature. An availability group can contain multiple databases, and up to 4 secondary replicas, in addition to the primary replica. The secondary replicas can be (optionally) read-only. Using availability groups requires using Windows Failover Clustering. And, perhaps because of this, availability groups support filestream storage (and by extension, FileTables). Availability groups also can have "listeners", separate endpoints that abstract connecting to an availability group.

So I've defined two SQL Server instances, each a default instance. On machines named "AlwaysOn1" and "AlwaysOn2" (disclaimer: I never proported to have an imagination for making up names). My availability group is named "AlwaysOnAG1" and encompasses the pubs and northwind database. 'Cause we all know sample databases need to be highly available. 😉 For reference, the availability group is using synchronous-commit mode and is set up for automatic failover (which means it could do automatic, manual, or even forced failover, if need be. Both nodes
are configured to be read-only when they are the secondary node. I also have a listener set up named "AlwaysOnAG1" that has a hardcoded IP
and listens on port 7001.

I've decided to put a FileTable on Northwind. And, from a previous experiement, I have a FileTable on a database named "FTTest". This
database does NOT participate in the availability group and only exists on the "AlwaysOn1" instance, not on "AlwaysOn2". But it produced one
of the surprises.

After enabling Northwind for FileTables (AlwaysOn1 is the current primary), I define a FileTable named "NWTest". With this configuration:
 –When SSMS connected to "AlwaysOn1"
    –The share opens from SSMS Object Explorer
    –Machine "AlwaysOn2" has an MSSQLServer share, but nothing is visible underneath.
    –Using the share \\AlwaysOn1\mssqlserver, I see both Northwind's filetable and FTTest's filetable.
 

–When SSMS connected to "AlwaysOn2"
    –Machine "AlwaysOn2" has an MSSQLServer share, but nothing is visible underneath.
    –Attempting to open the share from ObjectExplorer AlwaysOn2 node produces error: "The file location cannot be opened. Either access is
not enabled or your do not have permission for the same."

–When Connected to the "listener share" (i.e. \\AlwaysOnAG1\mssqlserver) I see both Northwind's filetable and FTTest's filetable. This was
surprising to me because FTTest is not a member of the availability group and I'm using it's "listener share" name. I can see both
FileTables even when I open the listener share from a different machine on the network (like the "AlwaysOn2" machine).
Just an observation…

The file system and SMB are more tolerant of temporary outages than SQL Server connections. But that's a subject for another blog post. So,
using the "listener share" I create open/edit close Somefile.txt in Notepad. Now, open the file again. Make some changes in the text. Now
failover the availability group from "AlwaysOn1" to "AlwaysOn2"…

During failover, I get the "Network resource unavailable" message for my Notepad. Then, as failover completes, the message goes away. Now I
can save my changes. Without incident.

So, after failover, what do things look like?
–When SSMS connected to "AlwaysOn1" (now secondary)
   –The share receives an error from SSMS Object Explorer.
   –Machine "AlwaysOn2" has an MSSQLServer share, with Northwind FileTable subdirectory.
   –Using the share \\AlwaysOn1\mssqlserver, I see FTTest's filetable (this database is not in the AG, remember), but not Northwind's FileTable subdirectories.
 

–When SSMS connected to "AlwaysOn2"(now primary)
   –The share opens from SSMS Object Explorer
   –Using the share \\AlwaysOn2\mssqlserver, I see Northwind's FileTable subdirectories/files.
 

–When connected using the listener share
   –I see only Northwind's FileTable subdirectories/files.

Notice that this is only observations with one specific configuration of the availability group. No guarentees as to what happens with other configurations.

OK, long-ish post again. But this post began with discussion of PathName() method on the filestream column. I'll conclude with that next.

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

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.