Sql Server FileTable drawbacks

Question

I'm looking at using File Tables instead of simply storing the file as binary data in the database, or storing a column in the database that contains a URL to a file share. Our apps use Entity Framework, so that's one minor snag that I see, but one that can pretty easily be worked around. Another is that, from what I can tell, the files must be stored on the same server as the database in a folder path such as "MachineName\InstanceName\FileShare" (Can this be configured so that the folder can exist on another server?).

Are there any additional drawbacks to using FileTables?

Answer 1

As for filetable drawbacks, aside from the slightly fiddly setup required to get them going, the only thing I can think of is that if your BLOBs are less than 1Mb in size, the documentation suggests that storing them in the database rather than on the filesystem will give better performance.

With regards to putting the filestream filegroup on a network share, this won't work. One of the common misconceptions with filestream/filetable is that the fileshare that is created is just a normal windows fileshare like any other. It is not, the files are stored in a special location on the filesystem called an NTFS data container, they are then presented to the outside world via the NTFS streaming APIs as a fileshare. The files have to be local to the machine because those NTFS APIs do not work over UNC.

The only way you can really make it work on a different machine is to use an iSCSI drive so that the NTFS APIs will still work, but the files are physically on the other machine.