Sql Server 2008 FileSteam全攻略

SqlServer2005及以前的版本，存储二进制大数据量对象(Blob)都是采用varbinary(max)的字段。Sql Server 2008通过将 varbinary(max) 二进制大型对象 (BLOB) 数据以文件形式存储在文件系统上，FILESTREAM 使 SQL Server 数据库引擎和 NTFS 文件系统成为了一个整体。Transact-SQL语句可以插入、更新、查询、搜索和备份 FILESTREAM 数据。通过 Win32 文件系统接口可以流式方式访问数据。

在 SQL Server 中，BLOB 可以是将数据存储在表中的标准 varbinary(max) 数据，也可以是将数据存储在文件系统中的 FILESTREAM varbinary(max) 对象。数据的大小和应用情况决定您应该使用数据库存储还是文件系统存储。如果满足以下条件，则应考虑使用 FILESTREAM：

·         所存储的对象平均大于 1 MB。

·         快速读取访问很重要。

·         您开发的是使用中间层作为应用程序逻辑的应用程序。

·         对于较小的对象，将 varbinary(max) BLOB 存储在数据库中通常会提供更为优异的流性能。

FILESTREAM 存储以 varbinary(max) 列的形式实现，在该列中数据以 BLOB 的形式存储在文件系统中。BLOB 的大小仅受文件系统容量大小的限制。文件大小为 2 GB 的 varbinary(max) 标准限制不适用于存储在文件系统中的 BLOB。

若要指定列应将数据存储在文件系统中，请对 varbinary(max) 列指定 FILESTREAM 属性。这样数据库引擎会将该列的所有数据存储在文件系统，而不是数据库文件中。

FILESTREAM 数据必须存储在 FILESTREAM 文件组中。FILESTREAM 文件组是包含文件系统目录而非文件本身的专用文件组。这些文件系统目录称为“数据容器”。数据容器是数据库引擎存储与文件系统存储之间的接口。

使用 FILESTREAM 存储时，请考虑以下内容：

·         如果表包含 FILESTREAM 列，则每一行都必须具有唯一的一个非空行 ID。

·         不能嵌套 FILESTREAM 数据容器。

·         使用故障转移群集时，FILESTREAM 文件组必须位于共享磁盘资源上。

·         FILESTREAM 文件组可位于压缩卷上。

最后需要注意的是：即使启用了透明数据加密，也不会加密 FILESTREAM 数据。

 

 

 

 

 

 

1.       开启FileSteam功能

在安装Sql Server 2008数据库实例时，会有一个选项提示此数据库是启开启FileStream功能，选上安装即可。

如果在安装时没有选此项，那么按如下步骤也可以开启FileStream功能

·         在“开始”菜单中，依次指向“所有程序”、“Microsoft SQL Server 2008”和“配置工具”，然后单击“SQL Server 配置管理器”。

·         在服务列表中，右键单击“SQL Server 服务”，然后单击“打开”。

·         在“SQL Server 配置管理器”管理单元中，找到要在其中启用 FILESTREAM 的 SQL Server 实例。

·         右键单击该实例，然后单击“属性”。

·         在“SQL Server 属性”对话框中，单击“FILESTREAM”选项卡。

·         选中“针对 Transact-SQL 访问启用 FILESTREAM”复选框。

·         如果要在 Windows 中读取和写入 FILESTREAM 数据，请单击“针对文件 I/O 流访问启用 FILESTREAM”。在“Windows 共享名”框中输入 Windows 共享的名称。

·         如果远程客户端必须访问存储在此共享中的 FILESTREAM 数据，请选择“允许远程客户端针对 FILESTREAM 数据启用流访问”。

·         单击“应用”。

·         在 SQL Server Management Studio 中，单击“新建查询”以显示查询编辑器。

·         在查询编辑器中，输入以下 Transact-SQL 代码：
EXEC sp_configure filestream_access_level, 2
RECONFIGURE

·         单击“执行”。

 

2.       创建FileStream数据库

由于 FILESTREAM 使用一种特殊类型的文件组，因此，在创建数据库时，必须至少为一个文件组指定 CONTAINS FILESTREAM 子句。

·         在 SQL Server Management Studio 中，单击“新建查询”以显示查询编辑器。

·         将下面示例的 Transact-SQL 代码复制到查询编辑器中。此 Transact-SQL 代码可创建一个启用了 FILESTREAM 的数据库，称为 FileStreamTestDb。

CREATE DATABASE FileStreamTestDb

ON

PRIMARY ( NAME = FileStreamTestDb,

    FILENAME = 'c:/data/FileStreamTestDb.mdf'),

FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM( NAME = FileStreamGroup1,

    FILENAME = 'c:/data/filestream1')

LOG ON  ( NAME = FileStreamTestDblog,

    FILENAME = 'c:/data/FileStreamTestDb_log.ldf')

GO

注意： 对于此脚本，C:/Data 目录必须存在。

·         单击“执行”。

在运行上面的示例后，filestream.hdr 文件和 $FSLOG 文件夹将出现在 c:/Data/filestream1 文件夹中。filestream.hdr 文件是 FILESTREAM 容器的头文件。filestream.hdr 文件是重要的系统文件。它包含 FILESTREAM 标头信息。请勿删除或修改此文件。其实filestream.hdr文件已经被数据库占用，在Sql Server启动的状态下，也是无法删除此文件的

3.       创建FileStream数据表

·         在 SQL Server Management Studio 中，单击“新建查询”以显示查询编辑器。

·         将下面示例的 Transact-SQL 代码复制到查询编辑器中。此 Transact-SQL 代码可创建一个启用了 FILESTREAM 的表，称为 FileStreamTestTable。

use FileStreamTestDb

go

CREATE TABLE FileStreamTestTable

(

                [Id] [uniqueidentifier] ROWGUIDCOL NOT NULL UNIQUE,

                [FileName] NVARCHAR(64) ,

                [BlobData] VARBINARY(MAX) FILESTREAM NULL

)

GO

·         单击“执行”。

 

4.       FileStream测试

insert into dbo.FileStreamTestTable values(NEWID(),'TestNullFile',null);

insert into dbo.FileStreamTestTable values(NEWID(),'TestFile',CAST('this is a TestFile' as varbinary(max)));

update FileStreamTestTable set BlobData= (select * From openrowset(BULK 'C:/mytest.jpg', SINGLE_BLOB) as mytest )

 

执行上面三条语句，然后去c:/data/filestream1目录看看结果，里面已经有了两条数据了，因为插入null时，默认是不向磁盘上写文件的。

按照FileStream的说明，删除包含 FILESTREAM 字段的行时，会同时删除其基础文件系统文件。执行下面的语句

Delete from dbo.FileStreamTestTable where [FileName] = 'TestFile';

去目录看看结果，发现里面还是3个文件，但是数据库中的记录确实已经删除了。执行下面的语句：

Checkpoint

Go

再去目录看看结果，发现里面没有文件了。

 

 

 

 

可以使用 Win32 在 FILESTREAM BLOB 中读取和写入数据。您需要执行以下步骤：

·         读取 FILESTREAM 文件路径。

·         读取当前事务上下文。

·         获取 Win32 句柄，并使用该句柄在 FILESTREAM BLOB 中读取和写入数据。

 

using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data.SqlClient; using System.Data.SqlTypes; using System.IO; namespace FileStreamTest { class Program { static void Main(string[] args) { SqlConnection sqlConnection = new SqlConnection( "Integrated Security=true;server=(local)"); SqlCommand sqlCommand = new SqlCommand(); sqlCommand.Connection = sqlConnection; try { sqlConnection.Open(); //The first task is to retrieve the file path //of the SQL FILESTREAM BLOB that we want to //access in the application. sqlCommand.CommandText = "SELECT BlobData.PathName()" + " FROM FileStreamTestDb.dbo.FileStreamTestTable" + " where FileName = 'TestFile'"; String filePath = null; Object pathObj = sqlCommand.ExecuteScalar(); if (DBNull.Value != pathObj) filePath = (string)pathObj; else { throw new System.Exception( "BlobData.PathName() failed" + " to read the path name " + " for the Chart column."); } //The next task is to obtain a transaction //context. All FILESTREAM BLOB operations //occur within a transaction context to //maintain data consistency. //All SQL FILESTREAM BLOB access must occur in //a transaction. MARS-enabled connections //have specific rules for batch scoped transactions, //which the Transact-SQL BEGIN TRANSACTION statement //violates. To avoid this issue, client applications //should use appropriate API facilities for transaction management, //management, such as the SqlTransaction class. SqlTransaction transaction = sqlConnection.BeginTransaction("mainTranaction"); sqlCommand.Transaction = transaction; sqlCommand.CommandText = "SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()"; Object obj = sqlCommand.ExecuteScalar(); byte[] txContext = (byte[])obj; //The next step is to obtain a handle that //can be passed to the Win32 FILE APIs. SqlFileStream sqlFileStream = new SqlFileStream(filePath, txContext, FileAccess.ReadWrite); byte[] buffer = new byte[512]; int numBytes = 0; //Write the string, "EKG data." to the FILESTREAM BLOB. //In your application this string would be replaced with //the binary data that you want to write. string someData = "EKG data."; Encoding unicode = Encoding.GetEncoding(0); sqlFileStream.Write(unicode.GetBytes(someData.ToCharArray()), 0, someData.Length); //Read the data from the FILESTREAM //BLOB. sqlFileStream.Seek(0L, SeekOrigin.Begin); numBytes = sqlFileStream.Read(buffer, 0, buffer.Length); string readData = unicode.GetString(buffer); if (numBytes != 0) Console.WriteLine(readData); //Because reading and writing are finished, FILESTREAM //must be closed. This closes the c# FileStream class, //but does not necessarily close the the underlying //FILESTREAM handle. sqlFileStream.Close(); //The final step is to commit or roll back the read and write //operations that were performed on the FILESTREAM BLOB. sqlCommand.Transaction.Commit(); } catch (System.Exception ex) { Console.WriteLine(ex.ToString()); } finally { sqlConnection.Close(); } return; } } }