積沙成塔

大家都知道像是 SQL Server 這類的關聯式資料庫擅長處理大量結構化資料，對於檔案或目錄這類型的非結構化資料的管理及查詢，相對而言比較難以處理。

以一般網站應用程式開發為例，常常會需要提供使用者透過瀏覽器上傳檔案的功能，應用程式要如何記錄這些檔案的相關資訊，大致上都是採用這兩種方法，一是只在 DBMS 中紀錄檔案名稱、大小、類型等資訊，實際檔案還是存放在檔案系統當中，二是把上傳的檔案轉成二進位後儲存到 DBMS 中。

上述兩種方法都有其優缺點，前者的問題會在於當您備份資料庫時，儲存在檔案系統中的檔案或目錄，需要自行想辦法做備份，換言之，資料庫和實體檔案是獨立的。後者將實體檔案轉成二進位資料，容易在效能上或儲存空間上衍生問題。

現在這個難題在 SQL Server 2012 新增特殊資料表（FileTables）已經可以解決大部分的問題，它是以 FILESTREAM 技術為基礎，並提供和全文檢索（Full-Text Searc）和語意搜尋（Semantic Search）等進階搜尋功能整合。透過 FileTables 您可以只利用 T-SQL 就可以讀取檔案、目錄階層及檔案屬性等資訊，並且可以和備份功能整合，將來只要備份資料庫，就會連同 FileTables 中的檔案或目錄一同備份，又不會衍生效能或資料庫儲存空間的問題。接下來筆者就來介紹如何設定並使用 FileTables。

啟用 FileTables 有幾個必要條件，說明如下：

1. 執行個體層級必須啟用 FILESTREAM。
2. 必須為 FILESTREAM 建立專用的檔案群組。
3. 在資料庫層級起用非交易式存取（Non-transactional access）。
4. 建立 FileTables 資料表。

特別一提的是，假設您的 SQL Server 是安裝在 64 位元的作業系統上，而您的 SQL Server 是安裝 32 位元版本，您將無法啟用 FILESTRAM 功能，這點是在規劃安裝 SQL Server 前必須先考慮清楚的。

接著筆者說明上述四個步驟應該如何進行：

1. 執行個體層級必須啟用 FILESTREAM。
   您可以於【開始 > 執行 > 輸入 SQLServerManager11.ms】開啟 SQL Server Configuration Manager。勾選 Enable FILESTRAM for Transact-SQL access，並設定分享目錄名稱，接著重新啟動 Database Engine 服務。
   
   
   
   服務重新啟動後，再以下列 T-SQL 設定 FILESTREAM ACCESS LEVEL：
   
   

      1:  EXEC sp_configure filestream_access_level, 2 

      2:  GO

      3:  RECONFIGURE

      4:  GO



2. 必須為 FILESTREAM 建立專用的檔案群組。
   由於 FileTables 這種特殊的資料表，無法儲存在一般的檔案群組中，因此這個步驟必須在您建立資料庫時，必須以下列 T-SQL 建立 FILESTREAM 專用的檔案群組（如第 12 列的程式碼）：
   
   

      1:  DECLARE @DB_Name NVARCHAR(64) = N'FTDB'

      2:  DECLARE @device_directory NVARCHAR(256)

      3:  SELECT @device_directory = SUBSTRING(filename, 1, CHARINDEX(N'master.mdf', LOWER(filename)) - 1)

      4:  FROM master.dbo.sysaltfiles WHERE dbid = 1 AND fileid = 1

      5:   

      6:  IF DB_ID(@DB_Name) > 0

      7:  EXEC ('DROP DATABASE ' + @DB_Name)

      8:   

      9:  EXEC (

     10:  N'CREATE DATABASE ' + @DB_Name

     11:    + ' ON PRIMARY (NAME = N''FTData'', FILENAME = N''' + @device_directory + N'FTData.mdf'') '

     12:    + ', FILEGROUP FSFileGroup CONTAINS FILESTREAM( NAME = FTData1, FILENAME = N''' + @device_directory +N'FTData1'') '

     13:    + 'LOG ON (NAME = N''FTLog'',  FILENAME = N''' + @device_directory + N'FTLog.ldf'')'

     14:  )

     15:  GO



建立完成後您可以在剛剛建立好的資料庫屬性中看到類似下圖的結果：


3. 在資料庫層級起用非交易式存取（Non-transactional access）。
   
   
   
4. 建立 FileTables 資料表。
   您可以用下列兩種方式來建立 FileTable：
   
   

      1:  --方法一，指定目錄名稱及定序

      2:  CREATE TABLE MyFileTable AS FileTable

      3:      WITH ( 

      4:            FileTable_Directory = 'FileTables',--指定目錄名稱

      5:            FileTable_Collate_Filename = database_default--指定 name 資料行的定序

      6:           )

      7:  GO

      8:   

      9:  --方法二，使用預設的目錄名稱

     10:  CREATE TABLE MyFileTable2 AS FileTable

     11:  GO

    

若順利建立成功，您將於【Object Explorer > 資料庫名稱 > Tables > FileTables】項下看到您建立的 FileTable，同時您可以看到 SQL Server 可以幫您管理非結構化檔案中的那些資訊（如下圖）。

 

 

本文就先介紹到此，下一篇文章，筆者將介紹如何把檔案納入 SQL Server 2012 的 FileTables 管理。

 

【參考資料】

• FileTables (SQL Server)