分享方式：

FILESTREAM (SQL Server)

  • 文章

只適用於 SQL Server - 僅限 Windows。

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

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

FILESTREAM 會使用 NT 系統快取來儲存檔案資料。 對系統快取中的檔案快取處理，有助於降低 FILESTREAM 資料對資料庫引擎效能可能產生的任何影響。 並不會使用 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) 資料，或是將資料儲存在檔案系統中的 varbinary(max) 物件。 資料的大小和使用情況可決定您是應該使用資料庫儲存體還是檔案系統儲存體。 如果下列條件成立，您應該考慮使用 FILESTREAM：

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

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

FILESTREAM 儲存體

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

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

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

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

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

整合式管理

由於 FILESTREAM 會實作為 varbinary(max) 資料行，並直接整合到資料庫引擎中，所以大多數的 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**.**Write() 的 Transact-SQL 區塊更新來執行資料的部分更新。
  • 當您刪除資料列，或是刪除或截斷包含 FILESTREAM 資料的資料表時，您會刪除檔案系統中的基礎 BLOB 資料。

檔案系統資料流存取

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

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

警告

FILESTREAM 容器是由 SQL Server 管理的資料夾。 請勿手動或透過其他應用程式，在 FILESTREAM 資料夾中新增或移除檔案。 如果這麼做，會導致備份和不一致錯誤。 如需詳細資訊，請參閱 MSSQLSERVER_3056、MSSQLSERVER_7908 和 MSSQLSERVER_7906。

陳述式模型

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

儲存體命名空間

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

交易檔案系統存取

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

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

交易持久性

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

隔離語意

隔離語意受到資料庫引擎交易隔離等級的管制。 讀取認可的隔離等級支援 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 (10.0.x) 上的成果 SQL Server 2008 R2 (10.50.x) 和更新版本上的成果
開啟以便讀取。 開啟以便讀取。 兩筆交易都成功。 兩筆交易都成功。
開啟以便讀取。 開啟以便寫入。 兩筆交易都成功。 交易 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 資料，資料庫引擎將無法保證資料的一致性與持續性或資料庫的完整性。

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

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

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

    篩選器名稱 Num 執行個體 海拔 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 目錄容器是否沒有超過 300,000 個檔案。 您可以使用目錄檢視中的資訊 sys.database_files 找出是檔案系統存放區 FILESTREAM-related 檔案中的哪些目錄。 有多個容器可以防止這種情況。 (如需詳細資訊，請參閱下一個項目符號項目。)

  • 只有一個 FILESTREAM 檔案群組，所有資料檔案都會建立在相同的資料夾下。 建立非常大量的檔案時，可能會受到大型 NTFS 索引的影響，這也可能會導致片段化。

    • 擁有多個檔案群組通常應該有助於處理這種情況 (應用程式使用資料分割或具有多個資料表，而每個資料表都會移至本身的檔案群組)。

    • 使用 SQL Server 2012 (11.x) 和更新版本時，在 FILESTREAM 檔案群組下可以有多個容器或檔案，並套用循環配置方案。 因此，每個目錄的 NTFS 檔案數目會減少。

  • 如果使用多個儲存了容器的磁碟區，備份和還原可能會由於多個 FILESTREAM 容器而加速進行。

    SQL Server 2012 支援每個檔案群組的多個容器，而且可以更輕鬆地進行。 管理大量檔案時，可能不需要複雜的分割配置。

  • SQL 執行個體中有非常大量的 FILESTREAM 容器時，啟動具有許多 FILESTREAM 容器的資料庫可能需要很長的時間，才能在 FILESTREAM 篩選器驅動程式中註冊這些容器。 將它們分散在多個不同的磁碟區，有助於改善資料庫啟動時間。

  • NTFS MFT 可能會形成片段，這可能會導致效能問題。 MFT 保留大小取決於磁碟區大小，因此不一定會遇到這種情況。

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

    • 可以使用 fsutil 行為集合 mftzone 2 保留更多 MFT 空間。

    • 建議將 FILESTREAM 資料檔案排除在防毒軟體掃描之外。

      注意

      Windows Server 2016 會自動啟用 Windows Defender。 請確定 Windows Defender 已設定為排除 Filestream 檔案。 若未如此，可能會導致備份和還原作業的效能降低。

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

相關內容