下表 : 使用 FileStream SQL Server 2008 中编程

下表

使用 FileStream SQL Server 2008 中编程

Bob Beauchemin

内容

使用的编程 Filestreams Transact SQL
编程 Filestreams 使用文件 I / O
SqlFileStream.NET 数据类型
FileStream 和事务
与 Filestreams 功能 Synergy

有一直大量讨论是否应该会在数据库中或文件系统存储大型 blob （二进制大对象），如文档和多媒体的项目。一个存量数据库是一个提供内置集成备份和还原，时间点恢复，交易记录，索引的专用的数据存储库和多得多。而在另一方面，在数据库中有大量数据可能会导致数据库碎片，，很好的恢复功能也意味着大型对象数据将始终写入两次，数据库本身的一次，一次事务日志。即使读取与 SQL 的大型数据意味着使用宝贵的数据库缓冲区刷新将否则缓存在一个副作用磁盘出内存中的数据。

若要结算此 controversy 一次，对于所有，Microsoft Research 做主题的研究和发布自己的结论在一个白皮书"BLOB 或不是 BLOB：大型对象存储在数据库或一个文件系统中的？" （请参阅 Research.Microsoft.com/Apps/Pubs/default.aspx?id=64525）。它们的结论是为"让我们所有在数据库中存储"看来的 setback 对 NTFS 文件系统的 SQL Server 2005 数据库进行了测试并建议"BLOBs 小比 256KB 由更有效地处理 SQL Server，NTFS 时效率大于 1MB 的 Blob。 break-even 此时将因在不同的数据库系统、文件系统和工作负荷"。一些程序员尝试使最好的两个领域将 NTFS 文件的位置存储在数据库行中，但这 sacrifices 事务的一致性、集成的查询和在的其他数据库功能。 SQL Server 2008 中, 您不必选择，称为文件流存储属性的新功能允许您定义数据实际上存储在文件系统的规格 SQL Server 表中的列。可以查询数据 Transact SQL 或流使用 C++ 和.NET 兼容的语言的 API，并保留所有其他数据库管理功能。在本专栏，我将介绍如何您希望编写使用这两种类型的 API 此混合存储模式。

首先，您可能已经注意到我引用为文件流存储属性功能。将文件流不是新的数据类型；一种新的存储机制。只能使用在 varbinary (max) 数据类型 SQL Server 2005 中引入的可用。若要使用文件流存储要求系统管理员启用它在操作系统级别使用 SQL Server 配置管理器和数据库管理员使用 sp _ configure SQL Server 实例级别上启用它。可以禁用、启用本地的访问权限或启用本地和远程访问文件流。此外，DBA 必须定义一个数据库会绑定到 SQL Server 数据库是 NTFS 文件系统位置的文件组。请注意文件组需要指向本地文件系统位置中；除非 NAS 设备显示为本地 NFS 卷通过 iSCSI filestreams 无法 Live 在远程服务器或网络可寻址存储 (NAS) 设备。访问服务器消息块 (SMB) 协议，文件流使用，因此如果您要允许文件 I / O 样式访问从外部计算机装有 SQL Server 必须允许到 SMB 端口的访问（通常为一个备用端口 139 端口 445，）通过防火墙。一旦管理的系统必备组件位置，则您只是定义一个 varbinary (max) 栏作为使用 FILESTREAM 属性表定义的一部分，然后为此列数据自动存储在文件中。此外，使用 FILESTREAM 列的每个表需要使用该 uniqueidentifier 数据类型 ROWGUIDCOL 属性定义的一列。使用 filestreams 的 Northwind 数据库的 Employees 表的版本看起来类似 图 1 。

图 1 Employees 表使用 Filestreams

CREATE TABLE [dbo].[Employees2](
  [EmployeeID] [int] IDENTITY NOT NULL PRIMARY KEY,
  [LastName] [nvarchar](20) NOT NULL,
  [FirstName] [nvarchar](10) NOT NULL,
  [Title] [nvarchar](30) NULL,
  -- filestream storage for photo column
  [Photo] [varbinary](max) FILESTREAM NULL,
  [ReportsTo] [int] NULL,
  -- identifier column
  [RowGuid] [UNIQUEIDENTIFIER]  NOT NULL  
        ROWGUIDCOL UNIQUE DEFAULT NEWID()
);

文件流同时使用传统的数据库日志和一特定的日志记录机制为传统的事务日志记录文件更改的扩展。在的扩展名是独立于常规的 SQL Server 数据库事务但集成登录使用备份和还原实用程序。有关更多信息从一个管理的文件流和数据库内部的角度，请参阅 Paul Randal 的极好白皮书"文件流 Storage SQL Server 2008 中"在 msdn.microsoft.com/library/cc949109。

几乎完全行为文件流对象可以从 SQL Server 程序员的角度来看，像 varbinary (max) 数据类型像使用几个警告。可以使用 Transact SQL 查询数据，而不在所有更改应用程序。简单的 Windows 窗体应用程序包括在本文的代码需要显示 Employees2 表我上面定义在 DataGrid 控件中没有更改。但是，增值从性能角度提供了使用文件流特定 API 增强功能读取和写入通过基于流的 API 数据时。其他的 Win 是特定列中数据的最大的大小是不再限制为 2GB 的文件流基于 varbinary (max) 列。这些特定的列的最大大小是无限制。意味着您可以存储，是例如 7GB 医疗图像，X-Ray 的图像像普通 SQL Server 表中的列中。文件流数据不会计算 4GB 最大大小限制 SQL Server Express Edition 数据库中, 以便甚至可以使用与 SQL Server 2008 Express Edition 中的 Blob。

我进入 API 之前，我希望强调的一旦已经保存文件使用 SQL Server 的文件流存储的数据，您必须不访问或更改数据 SQL Server 的控件之外。目录位置 Live 受允许只能通过 Windows 访问一个随机访问控制列表 (DACL) 的文件流的文件组的包含 SQL Server 服务帐户和系统管理员帐户。和系统管理员可以通过更改 DACL 删除其访问权限。当一个用户尝试使用流式 API 打开文件时，根据传统的 SQL 权限，了数据库、架构、表上, 执行了访问检查，并忽略列级别和 NTFS 权限。编辑文件流数据直接使用 Notepad.exe （或更可能是一个专用的照片编辑器程序）将通常导致损坏的数据库。但是，使用事务流式 API SQL Server 提供的，您可以编写自己的 SQL_Server 基于"事务性记事本"程序。

使用的编程 Filestreams Transact SQL

虽然编程 filestreams 与则 T-SQL 是普通的 T-SQL 编程一样，有几个忠告。使用该 varbinary (max) 的文件流列的第一个部分更新写入方法不允许使用。此外，因为流的 API 需要提供 SQL Server 文件路径名称（这就是流式 API 只能文件流列值为非空)、 T-SQL 是唯一的方法获取文件名称。尽管文件流列中插入 NULL 值不会创建一个文件，插入任何非空的值将导致要创建的文件。 UPDATE 到文件流列将导致要删除旧的文件和要在其位置创建新文件。在行上的 DELETE 操作将导致要删除相应文件。请注意使用 UPDATE 或 DELETE 操作删除该文件会不消失从文件系统立即，垃圾回收器线程将能物理地删除文件并回收空间。

T-SQL 内置的所有功能包括 SUBSTRING、 REPLACE、 LEN 和 CHARINDEX 文件流列的 varbinary (max) 使用的使用。只是针对数据库表中的列所允许的文件流存储；变量、参数（包括表值参数）、临时表的结构和表值函数的返回值中的列不能使用 FILESTREAM 属性。此外请注意只 XML 不能指定该文件流属性和 varbinary (max) 列；其他大型数据类型等 varchar (max) FILESTREAM 属性是可用。如果您想存储文件流存储中的字符或 XML 数据时，必须将列指定为 varbinary (max)，并使用 CAST 或 CONVERT 作为字符或 XML 处理数据。

才能访问使用流式 API 文件流数据，必须首先使用 T-SQL 获取路径名称，这使用（区分大小写）的 PathName() 方法，在文件流列上完成。文件流的逻辑路径版本，并且使用的 SQL Server 2008 版本 1 格式，路径名称是限制到（但不是等于）将同一行中，ROWGUIDCOL 列的值。尽管您可以使用文件流列视图和派生的表，一定要保留周围 ROWGUIDCOL，以防您需要使用 PathName() 方法。是例如表包含一个文件流和 rowguid 列的 T， 图 2 中编写代码。

图 2 创建一个视图

CREATE VIEW View1 
AS
SELECT RowGuidColumn , FileStreamColumn
FROM T;

SELECT FileStreamColumn.PathName() FROM View1;  -- works
GO

CREATE VIEW View2 
AS
SELECT FileStreamColumn FROM T;
GO

-- Fails because it is missing the RowGuidColumn
SELECT FileStreamColumn.PathName() FROM View2;
GO

最后，文件流列上不支持数据加密。这不是只满足数据加密 API 如 EncryptByKey，而且 SQL Server 2008 透明的数据加密功能。虽然文件流存储数据库可能会指定透明的数据加密，文件流文件不会被加密。

编程 Filestreams 使用文件 I / O

文件流存储列使用流式 I / O 之前, 有客户端上的一些要求。必须使用集成的安全性帐户时使用流式 I / O，因为无法传递 SQL 凭据，当打开 handle.because 文件流存储已使用驱动特殊文件系统筛选器程序，使用文件流存储的列进行编程实现包括使用不支持所有的 Win 32 操作的文件句柄。检索文件句柄的 nativeWin32 函数是 OpenSqlFilestream。此函数是 SQL Native Client 10 的一部分还包含 SQL Server ODBC 驱动程序、 OLE DB Provider 和网络库 DLL。该函数将返回支持大部分 Win 32 文件句柄的流式功能的 Win 32 句柄。虽然获取 Win 32 文件句柄需要路径名称和某些其他的选项，OpenSqlFilestream 函数将需要两条只能由 SQL Server 可以提供的信息。这些是通过使用文件流存储和事务令牌的列上调用 PathName() 方法返回文件的该名称。这意味着您编程与 OpenSqlFilestream 时, 您也要拆分，INSERT、 UPDATE 或到等效的两个"SQL 语句、 T-SQL 语句和流语句的 SELECT 语句。这些语句必须被依赖一起交易记录。尽管我将提及多有关交易记录和受支持的隔离级别更高版本，现在，就足以知道必须调用 T-SQL 函数 GET_FILESTREAM_TRANSACTION_CONTEXT() 获取事务令牌。此标记必须获得与打开该文件的同一个 Windows 用户的上下文中。请注意原始的 SQL 语句，要获取事务令牌必须是事务的一部分，否则事务令牌将为空并 OpenSqlFilestream 将失败。关闭该 HANDLE 之前，不可能会提交该事务。文件句柄使用特殊文件句柄支持的 API 是 ReadFile、 WriteFile、 TransmitFile、 SetFilePointer、 SetEndOfFile 或 FlushFileBuffers。试图调用任何其他文件 API 将返回 ERROR _ ACCESS _ DENIED。特殊的 HANDLE 具体来说不支持内存映射文件。

完整示例使用 ODBC 和 C++ 插入新行和文件流数据使用 OpenSqlFilestream SQL Server 2008 联机联机中提供。

使用一个 SELECT 的语句将要仅一个往返行程数据库路径名和交易记录标记； INSERT 或 UPDATE 语句，您将需要进行往返只有一个数据库操作以及。与下面的语句 SQL Server 2005 中, 引入在 T-SQL OUTPUT 子句提供到您的帮助。下面是信息的处理一行，并获取所有一往返行程中所需的 UPDATE 语句：

UPDATE dbo.Employees2 
SET name = 'NewName' WHERE id = 8
OUTPUT inserted.photo.PathName(),
       GET_FILESTREAM_TRANSACTION_CONTEXT()

我没有提到过使用 DELETE 语句，因为删除一行将同时删除该文件；您不能使用流式处理 I / O 执行 DELETE。但是，有您需要知道使用 INSERT 或 UPDATE 读取和写入 blob 时的一些事项。此外，我会将 Blob 更新分为两个用例：完成替换（重写）和部分更新。

插入包含使用 INSERT Blob 的行是使用流式 I / O 最佳原因之一。 SQL Server API 不支持流式使用 varbinary (max) 列的输入，尽管您可以通过使用 T-SQL 资料函数或写方法块中编写数据。（记住写方法禁止使用文件流存储时）。使用 INSERT 有趣的 twist 是插入 NULL 值创建任何文件。该 PathName() 方法也会返回 NULL，并且必须要到流数据没有文件。方法 INSERT，方法是插入空字符串而不是一个 NULL 值、检索路径名称和流到空的文件的内容。 T-SQL 语句将如下所示：

INSERT dbo.Employees2 (id, ... photo)
VALUES(1, ... CAST('' as VARBINARY(MAX))
OUTPUT inserted.photo.PathName(),GET_FILESTREAM_TRANSACTION_CONTEXT()

可以在数据中，然后打开用于写入和流文件。完成替换 UPDATE 工作类似 INSERT。您想更新非文件流列，返回路径名称和事务令牌和通过文件句柄的流数据中。除非您知道文件流存储列已经包含数据，是一个好主意设置为空字符串 T-SQL 中的该数据将更新语句。

文件流列的一个部分更新是有点更复杂的因为您可能需要阅读文件中进行更新前的信息。要完成此，可以使用句柄和 FSCTL_SQL_FILESTREAM_FETCH_OLD_CONTENT 参数使用 DeviceIoControl 函数。这将导致文件的内容的一个服务器端副本。执行此调用之后, ReadFile 将返回适当的数据，该调用 ReadFile 将返回的文件结束之前。如果您是读取并更新这种方式，则最好使用为重叠 I / O 高性能应用程序和尤其是那些可能会从 SQL Server 在同一台计算机访问文件流。请记住如果需要进行大量部分更新，使用文件流存储是要比用 SQL Server 的 varbinary (max) 没有文件流存储更慢。

SqlFileStream.NET 数据类型

.NET 编程人员通常不需要处理的 Win 32 文件句柄。尽管可以使用名为的 PInvoke （平台代码调用）在技术的.NET 程序中使用 OpenSqlFilestream，它将会更方便具有 OpenSqlFilestream 函数和处理封装在.NET 类中的访问的文件。在.NET Framework 3.5 SP 1 中类 System.Data.types.SqlFileStream 很好地填充物料清单。使用通常相同； SqlFileStream 的构造函数需要路径名称和一个交易记录标记为和某些选项。使用 SqlFileStream 插入一行所示在代码下载本专栏附带。

SqlFileStream 对象派生自 System.IO.Stream，并您使用它将使用的"普通的.NET 流。之间使用 SqlFileStream API 和 OpenSqlFilestream Win 32 函数的一些重要差别 SqlFileStream 使用默认缓冲区大小的 4K ； OpenSqlFilestream 的句柄不。此外，可以使用 SqlFileStream ReadWrite 模式将自动执行 DeviceIoControl 调用为了方便，本机 API 不支持此并且"部分更新"模式将涉及显式 DeviceIoCtrl 调用所示。若要使用 SqlFileStream 数据类型，必须.NET 3.5 SP 1 必须在客户端或 Web 服务器上安装。

FileStream 和事务

前面提到的而不是只存储 SQL Server 和文件系统上的文件中的文件名称中使用文件流存储的 au 一个是数据是事务性一致的对象。如果您 Transact SQL 中插入一行，插入文件使用流式 API，整个操作是成功，或失败。可能具有不存在的文件或 SQL Server 中没有相应的项的孤立的文件系统项目的文件名。 OpenSqlFilestream API 将确保这要求您具有无效的打开的事务在始终使用流式 API 时。但 SQL Server 支持的各种事务隔离级别和两个不同的事务语义：锁定和版本控制。程度流 API？在隔离级别的所有的 SQL Server 的锁管理器不能管理文件系统锁定

使用流式时, 非常有用为包含在原子操作的两个不同 SQL 语句的-T-SQL 部分和流部分的第二个语句。为了获取事务令牌，您需要先执行 T-SQL 部分。执行 T-SQL 命令使用 ADO.NET SqlConnection.BeginTransaction 方法或 System.Transactions 的 TransactionScope，必须首先从显式事务之前手动和自动事务管理在其他 API 上浮现的类似方法类似于 ODBC。交易记录必须保持打开直到关闭文件句柄。文件句柄处于打开状态，和将交易记录 uncommittable 时，不允许颁发 SqlTransaction.Save 调用。一个 SqlTransaction.rollback 调用将回滚事务，即使如果打开该文件。关闭文件句柄时，将触发触发器。

使用流式的 API 和文件流存储允许在所有四个锁定事务隔离级别，尽管始终与读取提交类似的流访问语义。使用读提交隔离级别（默认），如果为读取，其他读者能够读取该文件，将打开该文件，编写器将被阻止。如果打开该文件进行读取，它保持阻止交易记录的结束之前。未提交读的隔离级别被允许使用如果该文件的更新正在进行，未提交读的交易记录将读取旧版本的文件，而不是新的建议版本是正常的读提交行为的 caveat。可重复读和序列化的行为与使用流式处理为行时锁定数据库中的文件时。

版本控制隔离级别，即，读提交快照隔离和快照事务使用 filestreams 的数据库中不允许使用。这适用于整个的数据库不只是包含文件流列的表。 ALTER DATABASE 语句，使读提交快照隔离，启用快照事务将文件流存储的存在失败。

与 Filestreams 功能 Synergy

文件流可用于将大型数据存储在数据库中不需要事务日志开销的情况下，数据库的碎片行为但有时它将用于提升持久化计算列数据的一部分。这允许您不该 Blob 的属性的查询在读取该文件。例如，假设已将照片存储在 JPEG 格式，并希望单独存储颜色表的如背景色或照片的高度和宽度的部分。您可以只定义 varbinary (max) 列的该部分持久化计算的列；数据存储在行。使用上面，定义 Employees2 表的一个示例如下于：

ALTER TABLE dbo.Employees2
  ADD PhotoWidth AS dbo.ExtractWidth(Photo) PERSISTED;
A SQL query to obtain the photo width does not need to access the file at all:
SELECT Id, Photo 
FROM dbo.Employees2
WHERE PhotoWidth > 1200;

但是，应注意，类型 varbinary 持久化计算列中的索引不允许。

功能 Synergy 的一个很好的示例是 SQL Server 全文索引和搜索文件流存储的全文的组合。全文搜索筛选器组件存在文档类型如 Microsoft Office 文档、 PDF 文件和文本文件文件，并且文件流存储允许这些文件将存储在文件系统（而不是在数据库中。作为具体的示例可以将错误日志，或者 Web 日志文件的副本存储在文件流存储列，并全文索引列。在 SQL Server 2008 中, 全文搜索进程中运行的所以不需要搜索服务的进程外调用。类似下面的一个查询在进程工作，并可以非常快速：

SELECT id, Abstract 
FROM error_logs 
WHERE CONTAINS(ErrorText, 'unhandled');

我开始这篇文章的备选文件流提供的存储不仅集成备份和还原，但 SQL 数据和文件数据之间的事务一致性尽管文件流存储可以使用仅本地的磁盘存储。如果您只关心事务一致性，并且希望文件位于远程服务器或内容可寻址存储，SQL Server 2008 将包含附带功能远程 Blob 存储 (RBS)。此功能才作为 SQL Server 2008 功能包的一部分免费下载可用。 RBS 包含允许交易记录的操作和用于处理数据库表、 Blob 垃圾回收和事务参与中的 Blob 指针的存储过程的一系列的 API。内容可寻址存储供应商提供驱动程序为其特定的硬件。 NTFS 文件系统示例 RBS 驱动程序是可用的因为 CodePlex 和 EMC 下载已发布测试其 Centera 内容发送存储系统的 RBS 提供程序。 RBS API 不与文件流 API，但可能在将来对齐这些 API。

conclusion，文件流存储会使能够文件系统中存储为文件的较大的 Blob，并用 T-SQL 语句或事务流式 API 访问这些。使用此功能，您可以获得流以及较大的 Blob 的完整数据库集成的性能。

将您的问题和意见发送王俊元 mmdbdev@Microsoft.com

Bob Beauchemin 是一数据库中心应用程序的 Practitioner 和架构师、课程的作者和教师、编写器和开发人员技能合作伙伴在 SQLskills。他的写入书籍和文章 SQL Server、数据访问和集成技术和数据库安全性。您可以 brian.noyes bobb@sqlskills.com.

Last updated on 2015-10-14

通过

其他资源