FILESTREAM/FILETABLE Clarifications for Implementation

Question

Recently our team was looking at FILESTREAM to expand the capabilities of our proprietary application. The main purpose of this app is managing the various PDFS, Images and documents to all of the parts we manufacture. Our ASP application uses a few third party tools to allow viewing of these files. We currently have 980GB of data on the Fileserver. We have around 200GB of Binary data in SQL Server that we would like to extract since it is not performing well hence FILESTREAM seems to be a good compromise to the two major data storage/access issues.

A few things are not exactly clear to us:

1. FILESTREAM Can or Cannot store its data on a drive that is not locally attached. We already have a File Server with a RAID 10 (1.5TB drives). This server stores all of the documents right now, would we have to move these drives to the SQL Server for FILESTREAM? That would be a tough bullet to bite since the server also is doubling as the Application Server (Two VMs on one physical server).

2. FILETABLE stores the common metadata about the files but where is the Full Text part of it stored to allow searching of files like doc/docx? Is this separate? Are you able to freely add criteria to this to search by? If so any links to clarify would be appreciated.

3. Can FILETABLE be referenced in another table with a foreign key?

Thank you in advance

EDIT: For those having these questions this web video covered everything and more in terms of explaining filestream from 2008 to 2012 and the cavets to consider (I would seriously rep him if I could): http://channel9.msdn.com/Events/TechDays/Techdays-2012-the-Netherlands/2270

In conclusion we will not be using FILESTREAM as it would be way to huge of an upsurge to accommodate for investment.

EDIT 2:

Update to #1 - After carefully assessing FileTable in addition to FILESTREAM we got a winning combination. We did have to move the files over to the new server (wasn't to painful since they were on the same VM).It honestly took more time to write an extraction tool to dump the binary data within SQL to the File System.

Update to #2 - This was seperate but again Bob had an excellent webinar explaining this: http://channel9.msdn.com/Events/TechEd/Europe/2012/DBI411

Update to #3 - Using TFT inheritance we recycled the Docs table we had (minus the huge binary blobs) which required very little changes in our legacy apps. This was a huge upshot for the developer team.

Decasyllable

Answer 1

The location that the files are stored in for FileTables has to be local, or at least must appear to SQL Server as being local so a clever san driver might trick it. Since the FileTables stuff is built on the FILESTREAM stuff I imagine the limitations to be the same.

The searching of filetables is done via the containstable function which is documented on MSDN the search criteria uses the same syntax as FULLTEXT searching AFAIK.

For all intent and purpose the FileTable is a typical table so can be joined, searched or whatever. The only thing is that you have to use some functions of sql server in order to change the FILESTREAM guids into something more useful like a file path.

Disembodied

Spot on. We did encounter a small hiccup since we use entity framework. We resorted to using a softkey (as in no FK constraint) to the stream_id. The EF designer completely ignores the FileTables. We just made a small ADO.Net project in our main project to make uploading/downloading of documents. – Decasyllable