SQL Server FileStream詳解_MsSql

FILESTREAM是SQL Server 2008中的一個新特性，允許以獨立檔案的形式存放大對象資料，而不是以往一樣將所有資料都儲存到資料檔案中。以往在對業務系統的檔案進行管理時有兩種方法，一種是將檔案儲存到伺服器檔案系統中，資料庫中只儲存了該檔案的路徑，在使用該檔案時應用程式串連到伺服器讀取檔案；另一種是將檔案以varbinary(max)或image資料類型儲存到SQL Server中。而SQL Server 2008提供了FILESTREAM，結合這兩種方式的優點。

FILESTREAM使SQL Server資料庫引擎和NTFS檔案系統成為了一個整體。Transact-SQL語句可以插入、更新、查詢、搜尋和備份FILESTREAM資料。FILESTREAM使用NT系統緩衝來快取檔案資料。這有助於減少FILESTREAM資料可能對資料庫引擎效能產生的任何影響。由於沒有使用SQL Server緩衝池，因此該記憶體可用於查詢處理。

以往我們對檔案管理有兩種方法：

 1.資料庫只儲存檔案的路徑，具體的檔案儲存在檔案伺服器(NFS)上，使用時，編程實現從檔案伺服器讀取檔案；

 2.將檔案直接以varbinary(max)或image資料類型儲存在資料庫中。

　　上面兩種檔案存放方式都有問題：第一種方法因為會訪問磁碟，故受I/O影響效能不是很好，而且不能很好的進行檔案備份；第二種方法雖然解決了檔案備份（資料庫的備份）問題，但是由於欄位的位元組數太大，對資料庫本身也會造成影響，效能也很低下。

　　微軟在SQL Server 2008推出了一種新的方式 - FileStream，它不是一種新的資料類型，而是一種技術，它使SQL Server資料庫引擎和NTFS檔案系統成為了一個整體，它結合了上面兩種方式的優點：FileStream使用NT系統來快取檔案資料，而對檔案資料的操作可使用Transact-SQL語句對其進行插入、更新、查詢、搜尋和備份。

一、FileStream配置

 1.配置SQL Server安裝執行個體：Start -> All Programs -> Microsoft SQL Server 2008 R2 -> Configuration Tools -> SQL Server Configuration Manager

 

右擊屬性，切換到FILESTREAM標籤，勾選如下配置

 

2. 開啟SQL Server，並配置如下 

 　　以上也可以通過如下指令碼執行：

Exec sp_configure filesteam_access_level, 2RECONFIGURE 

　　最後重啟SQL Server Service

 

二、執行個體展示

　　建立FileStream類型檔案/組

--Create filestreamgroup ALTER DATABASE [Archive]ADD FILEGROUP [FileStreamGroup] CONTAINS FILESTREAM GO--Create filestream and association with filestreamgroup aboveALTER DATABASE [Archive]ADD FILE ( NAME = N'FileStream', FILENAME = N'D:\Company\Data\SQL Server\FileStream') TO FILEGROUP [FileStreamGroup]GO

　　建立測試表（注意：如果表包含FILESTREAM列，則每一行都必須具有唯一的行ID）

--Create tableCREATE TABLE Archive.dbo.Attachment (  [ID] [UNIQUEIDENTIFIER] ROWGUIDCOL NOT NULL PRIMARY KEY,  [FileName] NVARCHAR(100) NULL,  [CreateUser] NVARCHAR(100) NULL,  [CreateDatetime] DATETIME NULL,  [Content] VARBINARY(MAX) FILESTREAM NULL )FILESTREAM_ON [FileStreamGroup] 

　　插入一些測試資料

 --Insert some recordsINSERT INTO Attachment VALUES (NEWID(),'File Name 1','shg.cpan', GETDATE(),NULL),(NEWID(),'File Name 1','shg.cpan', GETDATE(),CAST('' AS VARBINARY(MAX))),(NEWID(),'File Name 1','shg.cpan', GETDATE(),CAST('This is a attachment, which contains all introduction for filestream' AS VARBINARY(MAX))) 

　　從前台插入一些資料

using (SqlConnection conn = new SqlConnection("server=10.7.15.172;database=Archive;uid=sa;pwd=1234;Connect Timeout=180")){  conn.Open();  using (SqlCommand cmd = conn.CreateCommand())  {    string id = Guid.NewGuid().ToString();    cmd.CommandText = "INSERT INTO Attachment VALUES('" + id + "','File Name 2','shg.cpan','" + DateTime.Now + "',@content)";    SqlParameter param = new SqlParameter("@content", SqlDbType.VarBinary, 1000000000);    param.Value = File.ReadAllBytes(@"D:\Folder\131 u_ex151207.log");    cmd.Parameters.Add(param);    cmd.ExecuteNonQuery();  }  conn.Close();}

　　檢索資料 

SELECT DATALENGTH(CONTENT)/(1024.0 * 1024.0) AS MB,* FROM ATTACHMENT 　

結果

 

　　檔案系統

　　

 　　上面的檔案都是上傳的真實檔案，只不過沒有尾碼，如果重新命名加上尾碼，即可讀取，如最後一個是excel檔案，加上.xls，即可用Excel軟體開啟此檔案

三、注意事項

　　請注意以下事項：

 •並不是所有的檔案儲存體都適合使用FileStream，如果所儲存的檔案對象平均大於1MB考慮使用FileStream，否則對於較小的檔案對象，以varbinary(max)BLOB儲存在資料庫中通常會提供更為優異的流效能；
 •FileStream可以使用在故障叢集上（Failover Cluster），但此時FileStream檔案組必須位於共用磁碟資源上；
 •FILESTREAM 與其他 SQL Server 功能的相容性：https://msdn.microsoft.com/zh-cn/library/bb895334(v=sql.105).aspx