本文章是由機器翻譯。

在資料表

使用 SQL Server 2008 中的 FileStreams 進行程式設計

Bob Beauchemin

可從 MSDN 程式庫 的程式碼下載
瀏覽線上的程式碼

內容

使用的程式設計 Filestreams Transact SQL
程式設計檔案 I / O 的 Filestreams
SqlFileStream 的.NET 資料型別
FileStreams 和交易
與 Filestreams 的功能實現

有一直是許多有關是否討論大型的 BLOB (二進位大型物件),例如文件和多媒體的項目應該儲存在資料庫或檔案系統。 一方面,資料庫都是特定的資料儲存機制,提供內建整合式的備份及還原,時間點 Point-in-time 修復,交易,索引,和更多。 另一方面,資料庫中有大量的資料可能會造成資料庫分段,,好的修復功能也表示大型物件資料會永遠寫入兩次,一次到本身的資料庫和交易記錄檔一次。 即使讀取與 SQL 的大型資料時,表示使用珍貴的資料庫的緩衝區,並在清除資料,在記憶會否則是體中快取出為副作用的磁碟。

若要解決這個 controversy 一次,為所有,Microsoft Research 研究主題並在白皮書中發行其結論 」 BLOB 或不 BLOB: 中的資料庫或在檔案系統的大型物件儲存嗎? 」 (請參魷 \ cs6 \ f1 \ cf6 \ lang1024 Research.Microsoft.com/Apps/Pubs/default.aspx?id=64525)。 他們的結論是一個 setback 的 「 讓我們來儲存所有在資料庫 」 意見中,因為它們測試 NTFS 檔案系統對 SQL Server 2005 資料庫,並且建議使用 「 小比 NTFS 更有效率的 Blob 大於 1 MB 時,會更有效率地由 SQL Server,處理 256KB BLOBs。 此時 break-even 會因不同的資料庫系統 」、 「 檔案的系統和 「 工作負載而異 」。 某些程式設計人員會嘗試藉由在資料庫的資料列中, 儲存的 NTFS 檔案的位置中有兩個世界的但是這犧牲交易的一致性]、 [整合式的查詢和 [其他資料庫功能。 在 SQL Server 2008 中, 請您不需要選擇,; 新的功能,稱為 filestream 儲存屬性可讓您定義資料行規格資料實際上儲存在檔案系統中的 SQL Server 表格中。 您可以查詢使用 Transact SQL 或資料流 API 使用 C ++ 和.NET 相容的語言資料,並保留所有其他資料庫的管理功能。 此的資料行中,我將說明如何,您要程式使用的 API 的兩個型別這個混合式儲存體模型。

第一次,您可能已經注意到我稱為 「 為 filestream 儲存屬性的功能。 在 filestream 不是新的資料型別,新的儲存機制。 這是使用只能與 SQL Server 2005 中引入 max 資料型別。 若要使用 filestream 儲存,請要求系統系統管理員啟用它在作業系統層級使用 SQL Server 組態管理員並資料庫管理員在使用 sp_configure 在 SQL Server 執行個體層級上啟用。 filestream 可以停用、 啟用本機存取,或啟用本機和遠端存取。 此外,DBA 必須定義一個資料庫的繫結至 SQL Server 資料庫的 NTFS 檔案系統 」 位置的檔案群組。 請注意檔案群組必須指向本機檔案系統位置,; filestreams 無法 Live 在遠端伺服器] 或 [在網路可定址的儲存 (NAS) 」 裝置上,除非 NAS 裝置呈現為本機 iSCSI 透過 NFS 磁碟區]。 存取在伺服器訊息區 (SMB) 通訊協定,filestream 使用,因此如果您要允許檔案 I / O-樣式從存取 「 電腦 」 外的安裝 SQL Server,您就必須允許 SMB 連接埠的存取 (連接通常埠 445,作為後援的連接埠 139) 透過防火牆。 一旦系統管理的必要條件位置在請您只要定義資料表定義與 FILESTREAM 屬性的一部分 varbinary (最大) 資料行,及您的資料,此資料行自動儲存在檔案。 另外,每個使用 FILESTREAM 的資料行的資料表所需要的使用,具有 ROWGUIDCOL 屬性的 uniqueidentifier 資料型別定義的資料行。 使用 filestreams 的 Northwind 資料庫的 Employees 資料表的版本,應該像 [圖 1

[圖 1 的員工資料表使用 Filestreams

CREATE TABLE [dbo].[Employees2](
  [EmployeeID] [int] IDENTITY NOT NULL PRIMARY KEY,
  [LastName] [nvarchar](20) NOT NULL,
  [FirstName] [nvarchar](10) NOT NULL,
  [Title] [nvarchar](30) NULL,
  -- filestream storage for photo column
  [Photo] [varbinary](max) FILESTREAM NULL,
  [ReportsTo] [int] NULL,
  -- identifier column
  [RowGuid] [UNIQUEIDENTIFIER]  NOT NULL  
        ROWGUIDCOL UNIQUE DEFAULT NEWID()
);

filestream 會使用傳統的資料庫記錄檔和一個特定的記錄機制,當做記錄檔案變更的傳統的交易記錄檔的副檔名。 副檔名是分開使用一般的 SQL Server 資料庫的交易記錄但整合式備份,並還原公用程式的。 如 filestream 從一個系統管理和資料庫內部的觀點的更多資訊,請參閱 Paul Randal 的絕佳的白皮書 (英文) 「 filestream Storage SQL Server 2008 中的 」 在 msdn.microsoft.com/library/cc949109。

從 SQL Server 程式設計人員的觀點,filestream 物件可以幾乎完全運作像 varbinary (最大) 資料型別會與一些警告。 您可以使用 Transact SQL 查詢資料,而不變更所有的應用程式。 簡單的 Windows Form 應用程式,包括在本文的程式碼將需要您,沒有顯示我在一個 DataGrid 控制項中定義上述 Employees2 資料表的變更。 不過,從效能觀點來看所加入的值會是使用 filestream-特定 API 的增強功能讀取和寫入資料流為基礎的 API 透過資料時。 額外贏得是特定的資料行中的資料的大小上限不再限於 2GB filestream-基礎 max 資料行。 這些特定的資料行的最大大小是限制大小寫。 這表示您可以存放,例如,7GB 醫療影像,X-Ray 的影像像在一般 SQL Server 資料表中資料行中。 在 4GB 最大大小限制,在 SQL Server Express Edition 資料庫,不會計算 filestream 資料,所以即使可以使用的二進位大型物件 (BLOB),在 SQL Server 2008 Express Edition。

之前我進入 API 先我想壓力,,一旦您已儲存檔案使用 SQL Server 的 filestream 存放區的資料,您必須無法存取或變更資料以外的 SQL 伺服器控制項。 其中 filestream 的即時保護的 Discretionary 存取控制清單 (DACL),讓只能由 Windows 存取的檔案群組的目錄會包含 SQL Server 服務帳戶 」 和 「 系統管理員帳戶。 和系統管理員可以藉由變更在 DACL 中移除其存取。 當使用者嘗試開啟檔案,使用資料流的 API 時, 對傳統的 SQL 權限資料庫、 結構描述、 資料表,執行存取檢查,且資料行層級和 NTFS 權限會被忽略。 編輯 filestream 資料直接使用 Notepad.exe (或更可能是特定的 Photo Editor 程式) 通常會損毀的資料庫。 不過,使用交易資料流 API,SQL Server 提供,您可以撰寫自己的 SQL Server 「 交易式 Notepad 」 程式。

使用的程式設計 Filestreams Transact SQL

雖然程式設計的 filestreams T-SQL,就如同一般的 T-SQL 程式設計,有一些警告。 首先,部分更新 filestream 資料行使用 max] 寫入方法不允許。 此外,因為資料流的 API 需要 SQL Server 所提供的檔案路徑名稱 (也就是,資料流的 API 只能使用 filestream 資料行值為非 NULL 時)、 T-SQL 中是唯一方法取得檔案名稱。 雖然 filestream 資料行中插入 NULL 值,不會建立檔案,插入任何非 NULL 值會導致要建立一個檔案。 要刪除舊的檔案] 及 [它的位置中建立新檔案,會導致 filestream 資料行的 UPDATE。 在 DELETE 作業,在資料列上的,將會造成對應檔案被刪除。 請注意檔案刪除的 UPDATE 或 DELETE 作業會不會消失從檔案系統立即,記憶體回收行程執行緒將會使用實際上刪除檔案並收回空間。

所有 T-SQL) 內建函式,搭配 max 包括 SUBSTRING、 REPLACE、 LEN 和 CHARINDEX 一個 filestream 資料行使用。 filestream 儲存為只允許資料庫資料表中的資料行 ; 請變數、 參數 (包括資料表值的參數)、 暫存資料表結構,] 和 [資料表值函式的傳回值中的資料行可能會無法使用 FILESTREAM 屬性。 也請注意 FILESTREAM 屬性是使用只 max 資料行,其他大型的資料型別會像 varchar(Max),XML 可能未指定 filestream 屬性。 如果要在 filestream 儲存區中儲存字元] 或 [XML 資料則您必須為 max 指定資料行,並使用 CAST 或 CONVERT,以字元為 XML 中處理資料。

為了存取使用資料流的 API filestream 資料,您必須先使用 T-SQL 取得路徑名稱,使用 (區分大小寫) 的 PathName() 方法 filestream 資料行上完成。 Filestream 的邏輯的路徑是版本,而以版本 1 格式使用的 SQL Server 2008,路徑名稱會是局限至 (但不是等於) [ROWGUIDCOL 資料行中同一列的值。 雖然您可以使用 filestream 檢視和衍生資料表資料行,請務必保留在 ROWGUIDCOL 周圍,若您需要使用 PathName() 方法。 例如,如果資料表,其中包含一個 filestream 和 rowguid 資料行的 T,請 [圖 2 ] 中撰寫程式碼。

[圖 2 中建立資料檢視

CREATE VIEW View1 
AS
SELECT RowGuidColumn , FileStreamColumn
FROM T;

SELECT FileStreamColumn.PathName() FROM View1;  -- works
GO

CREATE VIEW View2 
AS
SELECT FileStreamColumn FROM T;
GO

-- Fails because it is missing the RowGuidColumn
SELECT FileStreamColumn.PathName() FROM View2;
GO

最後,filestream 資料行上不支援資料加密。 這不是只有資料加密 EncryptByKey,類似的 API 也適用 「 SQL Server 2008 透明化的資料加密功能,則為 true。 雖然 filestream 儲存的資料庫可能會指定透明化的資料加密,filestream 檔案將不會加密。

程式設計檔案 I / O 的 Filestreams

與 filestream 儲存資料行中使用資料流的 I / O 之前, 有在用戶端上的某些需求。 當使用資料流 I / O,而不開啟 handle.because filestream 存放區所使用的是特殊的檔案系統驅動篩選程式,與使用 filestream 儲存的資料行的程式設計實作時,請傳遞 SQL 憑證的方式為牽涉到使用檔案控制代碼不支援所有的 Win32 作業時,您就必須使用的整合式安全性] 帳戶。 擷取檔案控制代碼,nativeWin32 功能,是 OpenSqlFilestream。 此函式會是 SQL Native Client 10 的一部分也包含 SQL Server ODBC 驅動程式、 OLE DB 提供者和網路程式庫 DLL。 函式會傳回 Win32 控制代碼所支援的大部分 Win32 的檔案控制代碼的串流功能。 而取得 Win32 檔案控制代碼時,需要路徑名稱和一些其他的選項,在 OpenSqlFilestream 函式需要兩項資訊,可以只由 SQL Server 所提供的函式。 這些都是使用 filestream 儲存區及交易語彙基元的資料行上呼叫 PathName() 方法傳回檔案的名稱]。 這表示當您程式設計與 OpenSqlFilestream 時, 您要分割在 INSERT、 UPDATE 或兩個 「 SQL 陳述式、 T-SQL 陳述式和資料流的陳述式的對等用法為 SELECT 陳述式。 這些陳述式必須與交易一起繫結。 雖然我將說明更有關交易和支援的隔離等級稍後,現在,是足以知道您必須呼叫 T-SQL 函式 GET_FILESTREAM_TRANSACTION_CONTEXT() 以取得交易語彙基元。 在相同的 Windows 使用者,與開啟檔案的內容中,必須取得此語彙基元。 請注意,原始的 SQL 陳述式,以取得交易語彙基元必須是交易的一部分,否則交易語彙基元將會是 NULL 而 OpenSqlFilestream 將會失敗。 您可能會無法認可交易,直到關閉 [HANDLE。 檔案控制代碼的 API,支援使用特殊的檔案控制代碼會是 ReadFile、 WriteFile、 TransmitFile、 SetFilePointer、 SetEndOfFile 或 FlushFileBuffers。 嘗試呼叫任何其他檔案 API 會傳回 ERROR_ACCESS_DENIED。 記憶體映射檔特別是不支援特殊的 HANDLE。

一個完整的範例 使用 ODBC 和 C ++ 插入使用 OpenSqlFilestream 一個新資料列和 filestream 資料 在 SQL Server 2008 線上叢書 》 中有提供。

使用 SELECT 陳述式,您會對單一往返資料庫路徑名稱和交易語彙基元 ; 使用 INSERT 或 UPDATE 陳述式,您將會想要讓只有一個資料庫來回往返以及]。 使用這些陳述式,T-SQL OUTPUT 子句,在 SQL Server 2005,是以您的協助。 以下是資訊的處理單一資料列中,並取得所有,您需要在一個往返的 UPDATE 陳述式:

UPDATE dbo.Employees2 
SET name = 'NewName' WHERE id = 8
OUTPUT inserted.photo.PathName(),
       GET_FILESTREAM_TRANSACTION_CONTEXT()

我沒提到使用 DELETE 陳述式,因為刪除資料列也會刪除檔案 ; 您無法在執行一個 DELETE 使用 「 資料流 I / O。 不過,也會有的一些您需要知道使用 INSERT 或 UPDATE 讀取和寫入將 BLOB (二進位大型物件時。 此外,我將在這裡將 BLOB (二進位大型物件更新分成兩種使用案例: 完成取代 (重新寫入) 和部分更新。

插入資料列,包含使用 INSERT Blob,是其中一個最佳使用資料流 I / O 的原因。 SQL Server API 不支援使用 max,資料行的輸入資料流,雖然您可以撰寫資料區塊使用 T-SQL STUFF 函數或寫入方法。 (請記住寫入方法禁止使用 filestream 儲存時)。 有趣的要素,與 INSERT,是插入 NULL 值建立沒有檔案。 則 PathName()) 方法也將會傳回 NULL,就沒有檔案資料流資料。 在方法的 INSERT 就是若要插入一個空字串,而不是 NULL 值,擷取路徑名稱,傳送內容串流到空的檔案。 T-SQL 陳述式看起來會像這樣:

INSERT dbo.Employees2 (id, ... photo)
VALUES(1, ... CAST('' as VARBINARY(MAX))
OUTPUT inserted.photo.PathName(),GET_FILESTREAM_TRANSACTION_CONTEXT()

然後,您就可以在資料中開啟資料流的寫入檔案。 完整取代像是 INSERT 會使用 UPDATE。 您要更新非 filestream 的資料行,在傳回的路徑名稱和交易語彙基元和資料流資料的檔案控制代碼。 除非您知道您 filestream 儲存資料行已經包含資料,最好將空字串,在 T-SQL 資料就會更新陳述式。

filestream 資料行的一部分的更新是有點複雜的因為您可以讀取檔案中的資訊之前執行的更新程式中。 若要完成這個工作,您可以使用控制代碼和 FSCTL_SQL_FILESTREAM_FETCH_OLD_CONTENT 參數使用 DeviceIoControl 函數。 這會造成檔案內容的伺服器端複本。 之後執行此呼叫,ReadFile 將 ReadFile 呼叫會傳回檔案的結尾之前,傳回適當的資料。 如果您是讀取並更新這種方式,最好使用高效能的應用程式] 及 [特別是那些來自 SQL Server 為相同的機器存取 filestream 重疊 I / O。 請記住如果您需要執行部分更新的許多,使用 filestream 儲存區是比使用不 filestream 儲存的 SQL Server 的 max 更慢。

SqlFileStream 的.NET 資料型別

.NET 程式設計人員通常不希望處理的 Win32 檔案控制代碼。 雖然您可以使用稱為的 PInvoke (平台程式碼叫用) 技術的.NET 程式中用於 OpenSqlFilestream,將會更方便具有 OpenSqlFilestream 函式和檔案處理封裝在.NET 類別的存取。 在.NET Framework 3.5 SP1,System.Data.types.SqlFileStream 類別剛好填滿,帳單]。 使用方式是大部分相同,; SqlFileStream 的建構函式需要的路徑名稱和交易語彙基元,以及一些選項]。 插入資料列使用 SqlFileStream 所示本專欄所附下載程式碼。

SqlFileStream 物件會衍生自 System.IO.Stream,而且您使用它會使用的 「 一般的.NET 資料流。 使用 SqlFileStream API 和 OpenSqlFilestream Win32 函式一些重要差異是 SqlFileStream 使用 4K 的預設緩衝區大小,; OpenSqlFilestream 的控制代碼則不會。 此外,使用 SqlFileStream ReadWrite 模式會自動執行 DeviceIoControl 的呼叫以方便使用,原生 API 不支援這,而且部分更新模式將相關的明確 DeviceIoCtrl 呼叫。 若要使用 SqlFileStream 資料型別,您必須.NET 3.5 SP1 必須安裝在用戶端或 Web 伺服器上。

FileStreams 和交易

前面提過的吸引使用 filestream 儲存,而不是直接儲存在 SQL Server] 和 [檔案系統上的檔案的檔案名稱的其中一個是資料交易的一致性。 如果您在 Transact SQL 插入資料列,並插入檔案,使用資料流的 API,整個作業成功或失敗。 任何可能會有檔案名稱不存在的檔案或 SQL Server 中沒有對應的項目的被遺棄的檔案系統項目。 OpenSqlFilestream API 會確保此要求您擁有有效的開啟的交易,在所有時間,使用資料流的 API 時。 但是 SQL Server 支援各種交易隔離等級和兩個不同的交易語意 (Semantics): 鎖定和版本控制。 如何串流的 API 運作的隔離等級,所有的 SQL Server 鎖定管理員不會管理檔案系統鎖定?

它使用資料流時, 很有用想到您不可部分完成的作業,以包含兩個不同 SQL 陳述式--一張供 T-SQL 部分使用,做為第二個陳述式串流處理的部分。 為了取得交易語彙基元,您需要先執行 T-SQL) 部分。 執行 T-SQL 命令,您必須先明確交易使用 ADO.NET SqlConnection.BeginTransaction 方法或 System.Transactions 的 TransactionScope 之前, 手動和自動交易管理會顯示在其他 API 的類似的方法會像 ODBC。 交易必須保持開啟直到關閉檔案控制代碼。 檔案控制代碼是開啟和會讓交易 uncommittable 時,不允許發行 SqlTransaction.Save 呼叫。 會發出 SqlTransaction.rollback 呼叫將系統回復的交易即使在開啟檔案。 關閉檔案控制代碼時,引發觸發程序。

雖然永遠是類似於讀取認可的資料流存取語意 (Semantics),則在所有四個鎖定的交易隔離等級,允許使用資料流的 API 和 filestream 儲存。 如果檔案開啟的讀取,其他讀取器將能夠讀取的檔案,請使用讀取認可 」 隔離等級 (預設)-寫入器會被封鎖。 如果檔案開啟來讀取,其保持封鎖直到異動的結束為止。 允許的如果檔案的更新進行中,讀取未認可交易會讀取檔案,舊版本,而不是新的建議版本為一般讀取未認可的行為,警告讀取未認可的隔離等級。 可重複讀取 」 和 「 可序列化的行為就是使用時的資料列被鎖定在資料庫中,為資料流的檔案時。

版本控制的隔離等級 — 也就是,「 讀取認可的快照集隔離 」 和 「 快照集交易 (使用 filestreams 資料庫中不允許。 此為 true,整個資料庫不只是包含 filestream 資料行之資料表。 ALTER DATABASE 陳述式,可讓讀取認可的快照集隔離,而且啟用快照集交易就會失敗的 filestream 儲存。

與 Filestreams 的功能實現

filestream 用於將大型資料儲存在資料庫而不造成額外負荷交易記錄檔,並且資料庫的片段行為但有時即可用來找出永續性的計算資料行資料的一部分。 這可讓您在讀取檔案時,不將 BLOB (二進位大型物件的屬性上的查詢。 例如,假設您 JPEG 格式儲存相片要分開儲存例如背景色彩或影像的高度和寬度,色彩表的部分。 您可以只定義永續性計算資料行的 [max] 資料行的 ; 在資料中資料列的預存。 使用上述,定義 Employees2 資料表的一個範例將如下所示:

ALTER TABLE dbo.Employees2
  ADD PhotoWidth AS dbo.ExtractWidth(Photo) PERSISTED;
A SQL query to obtain the photo width does not need to access the file at all:
SELECT Id, Photo 
FROM dbo.Employees2
WHERE PhotoWidth > 1200;

要注意,但是,不允許的型別 varbinary 的永續性計算資料行索引。

功能實現的一個很好的範例會是 SQL Server 全文檢索索引和全文檢索搜尋 filestream 儲存的組合。 全文檢索搜尋篩選器元件存在,例如 Microsoft Office 文件、 PDF 的檔案和文字檔案,文件型別,且 filestream 儲存允許這些檔案會儲存在檔案系統中,而不是在資料庫中儲存]。 一個具體的範例您可以將錯誤記錄檔或 Web 記錄檔的複本儲存在 filestream 儲存資料行並全文檢索索引資料行。 在 SQL Server 2008,全文檢索搜尋執行同處理序,因此不需要進行跨處理序 (Out-Of-Process 搜尋服務的呼叫。 類似下列查詢在處理序中運作,而且可以相當快速:

SELECT id, Abstract 
FROM error_logs 
WHERE CONTAINS(ErrorText, 'unhandled');

我開始本文的建議 filestream 所提供的儲存區不只是整合備份和還原,但 SQL 資料檔案的資料之間的交易一致性雖然 filestream 儲存區無法使用只會將本機的磁碟儲存。 如果您擔心只能使用交易的一致性並位於遠端伺服器或內容的可定址的儲存檔案,SQL Server 2008 就會包含同一系列文件功能遠端 BLOB (二進位大型物件儲存 (RBS)。 這項功能是免費下載做為 SQL Server 2008 功能套件的一部分。 RBS 所組成的 API 允許交易管理和一系列的預存程序處理 BLOB (二進位大型物件的指標,在資料庫資料表、 BLOB (二進位大型物件回收和交易的參與。 內容的可定址儲存廠商提供其特定的硬體驅動程式。 NTFS 檔案系統範例 RBS 驅動程式是可用的如 CodePlex 及 EMC 下載已發行 Beta 版本產品 RBS Centera 內容-處理儲存系統提供者。 RBS API 不 filestream 的 API,類似,但在未來可能會對齊這些 API。

在 conclusion,filestream 儲存讓您能夠為檔案的大型的 Blob 儲存在檔案系統中並使用 T-SQL 陳述式或交易式的資料流 API 中存取它們。 這的項功能,您可以取得的資料流,以及完整資料庫整合大型的 Blob 的效能。

您提出問題或意見,請將王俊元寄 mmdbdev@Microsoft.com

Bob Beauchemin 是,資料庫中心應用程式 practitioner 和架構設計人員、 課程作者和老師、 寫入器及 SQLskills 在開發人員技術的協力廠商。 在 SQL Server、 資料存取和整合的技術和資料庫安全性,他的寫入書籍與文章。 您可以將他在到達 bobb@sqlskills.com.