FILESTREAM/FILETABLE的实现说明 [英] FILESTREAM/FILETABLE Clarifications for Implementation

问题描述

最近，我们的团队正在研究FILESTREAM，以扩展我们专有应用程序的功能.该应用程序的主要目的是管理我们制造的所有零件的各种PDF，图像和文档.我们的ASP应用程序使用一些第三方工具来允许查看这些文件.目前，文件服务器上有980GB的数据.我们希望在SQL Server中提取大约200GB的二进制数据，因为它表现不佳，因此FILESTREAM似乎很好地解决了两个主要的数据存储/访问问题.

有些事情我们不清楚:

1. FILESTREAM可以或不能将其数据存储在未本地连接的驱动器上.我们已经有一个带RAID 10(1.5TB驱动器)的文件服务器.该服务器现在存储所有文档，我们是否必须将这些驱动器移动到用于FILESTREAM的SQL Server?由于服务器的数量也将增加一倍，成为Application Server(一台物理服务器上有两台VM)，因此这将是很难接受的.

2. FILETABLE存储有关文件的通用元数据，但是其中的全文部分存储在何处以允许搜索诸如doc/docx之类的文件?这是分开的吗?您是否可以向其中自由添加条件以进行搜索?如果是这样，任何澄清的链接将不胜感激.

3. 可以使用外键在另一个表中引用FILETABLE吗?

提前谢谢

对于那些有这些疑问的人，该网络视频涵盖了解释2008年至2012年文件流的所有内容以及要考虑的注意事项(如果可以的话，我会认真回答他):解决方案

文件存储在FileTables中的位置必须是本地的，或者至少在SQL Server中必须是本地的，这样聪明的san驱动程序可能会欺骗它.由于FileTables内容是基于FILESTREAM内容构建的，因此我想它们的局限性是相同的.

文件表的搜索通过containstable函数完成，该函数记录在MSDN上的 /a>搜索条件使用与FULLTEXT搜索AFAIK相同的语法.

出于所有目的和目的，FileTable是典型的表，因此可以联接，搜索或其他方式.唯一的是，您必须使用sql server的某些功能才能将FILESTREAM引导更改为更有用的内容，例如文件路径.

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.

解决方案

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.

这篇关于FILESTREAM/FILETABLE的实现说明的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！