FILESTREAM (SQL Server)

適用範圍: 是SQL Server (所有支援的版本) - 僅限 Windows

FILESTREAM 可讓 SQL Server架構應用程式在檔案系統上儲存非結構化的資料,例如文件和影像。 應用程式可以利用檔案系統的豐富資料流 API 和效能,並同時維護非結構化資料與對應結構化資料之間的交易一致性。

FILESTREAM 會將 SQL Server Database Engine varbinary(max) 二進位大型物件 (BLOB) 資料作為檔案儲存在檔案系統上,以整合 與 NTFS 或 ReFS 檔案系統。 Transact-SQL 陳述式可以插入、更新、查詢、搜尋和備份 FILESTREAM 資料。 Win32 檔案系統介面提供了資料的資料流方式存取。

FILESTREAM 會使用 NT 系統快取來儲存檔案資料。 如此可減少 FILESTREAM 資料可能對 Database Engine 效能產生的任何影響。 並不會使用 SQL Server 緩衝集區;因此,此記憶體可用於查詢處理。

當您安裝或升級 SQL Server時,並不會自動啟用 FILESTREAM。 您必須使用 SQL Server 組態管理員和 SQL Server Management Studio來啟用 FILESTREAM。 若要使用 FILESTREAM,您必須建立或修改資料庫,以便包含特殊類型的檔案群組。 然後,請建立或修改資料表,讓它包含具有 FILESTREAM 屬性的 varbinary(max) 資料行。 完成這些工作之後,您就可以使用 Transact-SQL 和 Win32 來管理 FILESTREAM 資料。

使用 FILESTREAM 的時機

在 SQL Server中,BLOB 可以是將資料儲存在資料表中的標準 varbinary(max) 資料,或是將資料儲存在檔案系統中的 FILESTREAM varbinary(max) 物件。 資料的大小和使用情況可決定您是應該使用資料庫儲存體還是檔案系統儲存體。 如果下列條件成立,您應該考慮使用 FILESTREAM:

  • 平均來說,儲存的物件大於 1 MB。
  • 快速讀取權非常重要。
  • 您正在開發將中介層用於應用程式邏輯的應用程式。

如果是較小的物件,將 varbinary(max) BLOB 儲存在資料庫中通常會提供更好的資料流處理效能。

FILESTREAM 儲存體

FILESTREAM 儲存體會實作為 varbinary(max) 資料行,該資料行中的資料會當做 BLOB 儲存在檔案系統上。 BLOB 的大小只受到檔案系統磁碟區大小的限制。 標準 varbinary(max) 限制 (2 GB 檔案大小) 不適用於檔案系統中所儲存的 BLOB。

若要指定資料行應該將資料儲存在檔案系統上,請在 varbinary(max) 資料行上指定 FILESTREAM 屬性。 如此會讓 Database Engine 將該資料行的所有資料都儲存在檔案系統上,而不是儲存在資料庫檔案中。

FILESTREAM 資料必須儲存在 FILESTREAM 檔案群組中。 FILESTREAM 檔案群組是包含檔案系統目錄 (而非檔案本身) 的特殊檔案群組, 這些檔案系統目錄稱為「資料容器」。 資料容器是 Database Engine 儲存體與檔案系統儲存體之間的介面。

當您使用 FILESTREAM 儲存體時,請考慮以下事項:

  • 當資料表包含 FILESTREAM 資料行時,每一個資料列都必須有唯一的非 Null 資料列識別碼。
  • 您可以將多個資料容器加入至 FILESTREAM 檔案群組。
  • FILESTREAM 資料容器無法巢狀化。
  • 當您正在使用容錯移轉叢集時,FILESTREAM 檔案群組必須在共用磁碟資源上。
  • FILESTREAM 檔案群組可以在壓縮的磁碟區上。

整合式管理

由於 FILESTREAM 會實作為 varbinary(max) 資料行,並直接整合到 Database Engine中,所以大多數的 SQL Server 管理工具和功能不需要修改 FILESTREAM 資料即可運作。 例如,您可以搭配 FILESTREAM 資料使用所有的備份和復原模型,而且 FILESTREAM 資料會與資料庫中的結構化資料一起備份。 如果您不想要將 FILESTREAM 資料與關聯式資料一起備份,您可以使用部分備份來排除 FILESTREAM 檔案群組。

整合式安全性

在 SQL Server中,系統會維護 FILESTREAM 資料的安全性,就像是維護其他資料的安全性一樣:在資料表或資料行層級上授與權限。 如果使用者具有資料表中 FILESTREAM 資料行的權限,該使用者便可開啟關聯的檔案。

注意

FILESTREAM 資料上不支援加密。

只有執行 SQL Server 服務帳戶所使用的帳戶會被授與 FILESTREAM 容器的權限。 我們建議您不要將資料容器的權限授與其他帳戶。

注意

SQL 登入不會使用 FILESTREAM 容器。 只有 NTFS 或 ReFS 驗證會使用 FILESTREAM 容器。

使用 Transact-SQL 和檔案系統資料流存取來存取 BLOB 資料

在您將資料儲存在 FILESTREAM 資料行中以後,可以使用 Transact-SQL 交易或 Win32 API 來存取檔案。

Transact-SQL 存取

您可以藉由使用 Transact-SQL來插入、更新和刪除 FILESTREAM 資料:

  • 您可以使用插入作業,在 FILESTREAM 欄位中預先填入 null 值、空白值,或是相當簡短的內嵌資料。 但是,將大量的資料當做資料流處理成使用 Win32 介面的檔案時,會比較有效率。
  • 當您更新 FILESTREAM 欄位時,您會修改檔案系統中的基礎 BLOB 資料。 當 FILESTREAM 欄位設定為 NULL 時,與此欄位有關聯的 BLOB 資料會遭到刪除。 您無法使用實作為 UPDATE Transact-SQL .Write() 的 區塊更新來執行資料的部分更新。
  • 當您刪除資料列,或是刪除或截斷包含 FILESTREAM 資料的資料表時,您會刪除檔案系統中的基礎 BLOB 資料。

檔案系統資料流存取

Win32 資料流支援可在 SQL Server 交易的內容中運作。 在交易內時,您可以使用 FILESTREAM 函數來取得檔案的邏輯 UNC 檔案系統路徑。 然後,您可使用 OpenSqlFilestream API 取得檔案控制代碼。 之後,此控制代碼可由 Win32 檔案資料流介面 (如 ReadFile() 和 WriteFile()) 所使用,以透過檔案系統來存取及更新檔案。

由於檔案作業是交易式,所以您無法透過檔案系統來刪除或重新命名 FILESTREAM 檔案。

陳述式模型

FILESTREAM 檔案系統存取會使用檔案的開啟和關閉來建立 Transact-SQL 陳述式的模型。 當檔案控制代碼開啟時,此陳述式便會開始,而當此控制代碼關閉時,此陳述式就會結束。 例如,當寫入控制代碼關閉時,在資料表上註冊之任何可能的 AFTER 觸發程序便會引發,就像是 UPDATE 陳述式已完成一樣。

儲存體命名空間

在 FILESTREAM 中, Database Engine 會控制 BLOB 實體檔案系統命名空間。 有一個新的內建函數 PathName提供了對應至資料表內每一個 FILESTREAM 資料格之 BLOB 的邏輯 UNC 路徑。 應用程式會使用此邏輯路徑來取得 Win32 控制代碼,並在 BLOB 資料上運作 (透過一般 Win32 檔案系統介面)。 如果 FILESTREAM 資料行的值為 NULL,此函數就會傳回 NULL。

交易檔案系統存取

有一個新的內建函數 GET_FILESTREAM_TRANSACTION_CONTEXT()提供了一個 Token,它代表與工作階段有關聯的目前交易。 此交易必須已經開始,而且尚未中止或認可。 應用程式會藉由取得 Token,將 FILESTREAM 檔案系統資料流作業與開始的交易繫結。 如果沒有任何明確開始的交易,此函數就會傳回 NULL。

在認可或中止此交易之前,所有的檔案控制代碼都必須先關閉。 如果有控制代碼在交易範圍之外仍然為開啟狀態,針對此控制代碼的其他讀取將會造成失敗;對此控制代碼的其他寫入將會成功,因為實際資料將不會寫入磁碟中。 同樣地,如果關閉了 Database Engine 的資料庫或執行個體,則所有開啟的控制代碼都將會失效。

交易持久性

使用 FILESTREAM 時, Database Engine 會在交易認可之後確保從檔案系統資料流存取修改之 FILESTREAM BLOB 資料的交易持續性。

隔離語意

隔離語意受到 Database Engine 交易隔離等級的管制。 讀取認可的隔離等級支援 Transact-SQL 和檔案系統存取。 可重複的讀取作業以及可序列化和快照集隔離均受到支援。 中途讀取則不受到支援。

檔案系統存取的開啟作業不會等候任何鎖定。 如果開啟作業因為交易隔離而無法存取資料,開啟作業就會立即失敗。 如果開啟作業因為隔離違規而無法繼續,則資料流 API 呼叫會因為 ERROR_SHARING_VIOLATION 而失敗。

若要允許進行部分更新,應用程式可以發出裝置 FS 控制項 (FSCTL_SQL_FILESTREAM_FETCH_OLD_CONTENT),以便將舊的內容提取到開啟之控制代碼所參考的檔案。 如此將會觸發伺服器端的舊內容複本。 為了在處理極大型的檔案時取得更好的應用程式效能及避免可能的逾時,我們建議您使用非同步 I/O。

如果在寫入此控制代碼之後發出 FSCTL,則最後一個寫入作業將會保存下來,而之前對此控制代碼的寫入將會遺失。

檔案系統 API 和支援的隔離等級

當檔案系統 API 由於隔離違規而無法開啟檔案時,系統就會傳回 ERROR_SHARING_VIOLATION 例外狀況。 當兩筆交易嘗試存取相同的檔案時,就會發生這種隔離違規。 存取作業的結果主要取決於用來開啟檔案的模式,以及執行交易的 SQL Server 版本。 下表概述了兩筆存取相同檔案之交易的可能結果。

交易 1 交易 2 在 SQL Server 2008 上的結果 在 SQL Server 2008 R2 和更新版本上的結果
開啟以便讀取。 開啟以便讀取。 兩筆交易都成功。 兩筆交易都成功。
開啟以便讀取。 開啟以便寫入。 兩筆交易都成功。 交易 2 底下的寫入作業不會影響在交易 1 中執行的讀取作業。 兩筆交易都成功。 交易 2 底下的寫入作業不會影響在交易 1 中執行的讀取作業。
開啟以便寫入。 開啟以便讀取。 交易 2 的開啟作業失敗,並傳回 ERROR_SHARING_VIOLATION 例外狀況。 兩筆交易都成功。
開啟以便寫入。 開啟以便寫入。 交易 2 的開啟作業失敗,並傳回 ERROR_SHARING_VIOLATION 例外狀況。 交易 2 的開啟作業失敗,並傳回 ERROR_SHARING_VIOLATION 例外狀況。
開啟以便讀取。 開啟以便進行 SELECT。 兩筆交易都成功。 兩筆交易都成功。
開啟以便讀取。 開啟以便進行 UPDATE 或 DELETE。 兩筆交易都成功。 交易 2 底下的寫入作業不會影響在交易 1 中執行的讀取作業。 兩筆交易都成功。 交易 2 底下的寫入作業不會影響在交易 1 中執行的讀取作業。
開啟以便寫入。 開啟以便進行 SELECT。 交易 2 會封鎖直到交易 1 認可或結束交易,或者交易鎖定逾時為止。 兩筆交易都成功。
開啟以便寫入。 開啟以便進行 UPDATE 或 DELETE。 交易 2 會封鎖直到交易 1 認可或結束交易,或者交易鎖定逾時為止。 交易 2 會封鎖直到交易 1 認可或結束交易,或者交易鎖定逾時為止。
開啟以便進行 SELECT。 開啟以便讀取。 兩筆交易都成功。 兩筆交易都成功。
開啟以便進行 SELECT。 開啟以便寫入。 兩筆交易都成功。 交易 2 底下的寫入作業不會影響交易 1。 兩筆交易都成功。 交易 2 底下的寫入作業不會影響交易 1。
開啟以便進行 UPDATE 或 DELETE。 開啟以便讀取。 交易 2 的開啟作業失敗,並傳回 ERROR_SHARING_VIOLATION 例外狀況。 兩筆交易都成功。
開啟以便進行 UPDATE 或 DELETE。 開啟以便寫入。 交易 2 的開啟作業失敗,並傳回 ERROR_SHARING_VIOLATION 例外狀況。 交易 2 的開啟作業失敗,並傳回 ERROR_SHARING_VIOLATION 例外狀況。
開啟以便進行可重複讀取的 SELECT。 開啟以便讀取。 兩筆交易都成功。 兩筆交易都成功。
開啟以便進行可重複讀取的 SELECT。 開啟以便寫入。 交易 2 的開啟作業失敗,並傳回 ERROR_SHARING_VIOLATION 例外狀況。 交易 2 的開啟作業失敗,並傳回 ERROR_SHARING_VIOLATION 例外狀況。

從遠端用戶端寫出

遠端檔案系統對 FILESTREAM 資料的存取,是透過伺服器訊息區塊 (SMB) 通訊協定來啟用。 如果用戶端在遠端,則用戶端不會快取任何寫入作業。 寫入作業一定會傳送給伺服器, 資料可以在伺服器端快取。 我們建議您將在遠端用戶端上執行的應用程式合併小型寫入作業,以便使用較多的資料來減少寫入作業。

使用 FILESTREAM 控制代碼來建立記憶體對應檢視 (記憶體對應 I/O) 不受到支援。 如果將記憶體對應用於 FILESTREAM 資料, Database Engine 將無法保證資料的一致性與持續性或是資料庫的完整性。

改進 FILESTREAM 效能的建議和指導方針

SQL Server FILESTREAM 功能可讓您將 Varbinary (max) 二進位大型物件資料儲存為檔案系統中的檔案。 當 FILESTREAM 容器中有大量的資料列(這是 FILESTREAM 資料行和 Filetable 的基礎儲存體)時,您最後可能會有包含大量檔案的檔案系統磁片區。 若要在處理資料庫以及檔案系統的整合式資料時達到最佳效能,請務必確保檔案系統的調整速度最佳。 以下是一些可從檔案系統觀點取得的微調選項:

  • SQL Server FILESTREAM 篩選器驅動程式的高度檢查 [例如 rsfx0100.sys]。 針對與 FILESTREAM 功能儲存檔案的磁片區相關聯的存放裝置堆疊,評估所有載入的篩選驅動程式,並確定 rsfx 驅動程式位於堆疊的底部。 您可以使用 FLTMC.EXE 控制程式來列舉特定磁片區的篩選器驅動程式。 以下是來自 FLTMC 公用程式的輸出範例: C:\Windows\System32>fltMC.exe 篩選

    篩選名稱 實例數目 海拔 Frame
    Sftredir 1 406000 0
    MpFilter 9 328000 0
    luafv 1 135000 0
    FileInfo 9 45000 0
    RsFx0103 1 41001.03 0
  • 檢查伺服器是否已停用檔案的 [上次存取時間] 屬性。 此檔案系統屬性會保留在登錄中:
    索引鍵名稱: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\FileSystem
    名稱: NtfsDisableLastAccessUpdate
    類型:REG_DWORD
    值:1

  • 檢查伺服器是否已停用8.3 命名。 此檔案系統屬性會保留在登錄中:
    索引鍵名稱: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\FileSystem
    名稱: NtfsDisable8dot3NameCreation
    類型:REG_DWORD
    值:1

  • 檢查 FILESTREAM 目錄容器是否未啟用檔案系統加密或檔案系統壓縮,因為這些容器可能會在存取這些檔案時造成額外的負荷層級。

  • 從提升許可權的命令提示字元,執行 fltmc 實例,並確定沒有任何篩選器驅動程式會附加至您嘗試還原的磁片區。

  • 檢查 FILESTREAM 目錄容器是否有300000以上的檔案。 您可以使用 [ sys.database_files 類別目錄] 視圖中的資訊,找出檔案系統存放區檔案中的目錄 FILESTREAM-related 。 有多個容器可以防止這種情況。 (如需詳細資訊,請參閱下一個專案符號專案 ) 。

  • 如果只有一個 FILESTREAM 檔案群組,則會在相同的資料夾下建立所有資料檔案。 檔案建立非常大量的檔案可能會受到大型 NTFS 索引的影響,而這也可能會分散。

    • 使用多個檔案群組通常有助於進行此 (應用程式會使用資料分割,或有多個資料表,每個都要有自己的檔案群組) 。

    • 使用 SQL Server 2012 和更新版本時,您可以在 FILESTREAM 檔案群組下擁有多個容器或檔案,而且會套用迴圈配置資源配置配置。 因此,每個目錄的 NTFS 檔案數目會變得較小。

  • 如果使用多個儲存容器的磁片區,則多個 FILESTREAM 容器的備份和還原可能會變得更快。

    SQL Server 2012 支援每個檔案群組的多個容器,而且可以更輕鬆地進行作業。 管理較大量的檔案時,可能不需要複雜的資料分割架構。

  • NTFS MFT 可能會被分散,而這可能會造成效能問題。 MFT 保留大小取決於磁片區大小,因此您可能會遇到這種情況。

    • 您可以使用 defrag /A /V C: (變更 C:來檢查 MFT 片段,) 實際的磁片區名稱。

    • 您可以使用 fsutil behavior set mftzone 2 來保留更多的 MFT 空間。

    • FILESTREAM 資料檔案應該從防毒軟體掃描中排除。

      注意

      Windows Server 2016 會自動啟用 Windows Defender。 請確定 Windows Defender 已設定為排除 Filestream 檔案。 若無法這樣做,可能會導致備份和還原作業的效能降低。

      如需詳細資訊,請參閱 設定和驗證 Windows Defender 防毒軟體掃描的排除專案。

啟用及設定 FILESTREAM
建立啟用 FILESTREAM 的資料庫
建立儲存 FILESTREAM 資料的資料表
使用 Transact-SQL 存取 FILESTREAM 資料 建立 FILESTREAM 資料的用戶端應用程式
使用 OpenSqlFilestream 存取 FILESTREAM 資料
對 FILESTREAM 資料進行部分更新
避免與 FILESTREAM 應用程式中的資料庫作業相衝突
移動啟用 FILESTREAM 功能的資料庫
設定容錯移轉叢集上的 FILESTREAM
為 FILESTREAM 存取設定防火牆

FILESTREAM 與其他 SQL Server 功能的相容性
Filestream 及 FileTable 動態管理檢視 (Transact-SQL)
Filestream 和 FileTable 目錄檢視 (Transact-SQL)
Filestream 和 FileTable 系統預存程序 (Transact-SQL)