FILESTREAM (SQL Server)

適用于:yesSQL Server (所有支援的版本) - 僅限 Windows

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

FILESTREAM 藉由將 Varbinary (max) ) 二進位大型 (物件儲存為檔案系統上的檔案,將 SQL Server Database Engine 與 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 (儲存 資料的最大) 物件。 資料的大小和使用情況可決定您是應該使用資料庫儲存體還是檔案系統儲存體。 如果下列條件成立,您應該考慮使用 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**.**Write () 的 Transact-SQL 區塊式更新來執行資料的部分更新。
  • 當您刪除資料列,或是刪除或截斷包含 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,資料庫引擎可確保從檔案系統串流存取修改之 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 (最大) 二進位大型物件資料儲存為檔案系統中的檔案。 當您在 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 和更新版本時,您可以在 FILESTREAM 檔案群組下有多個容器或檔案,並套用迴圈配置配置。 因此,每個目錄的 NTFS 檔案數目將會較小。

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

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

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

  • NTFS MFT 可能會變得分散,這可能會造成效能問題。 MFT 保留大小取決於磁片區大小,因此您可能會遇到或可能未遇到此問題。

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

    • 您可以使用 fsutil 行為設定 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)