sqlserver關於filestream檔案流、filetable檔案表的總結

個人理解：

FileStream、Filetable和Oracle的外部表有點像，但又不完全像，相似點都是表中的實際資料存放在檔案系統中，表只記錄後設資料。

Oracle外部表的資料存放在檔案系統中,使用directory name和資料庫聯絡在一起,oracle外部表是隻讀表

FileStream的表的filestream欄位的資料實際上存放在檔案系統中,使用資料庫FILESTREAM檔案組對應的目錄和檔案系統聯絡在一起,也就是所謂的"資料容器",是可讀寫的表

Filetable表的資料,個人理解實際上也是存放在檔案系統中,它是基於FileStream基礎上來的，不僅使用資料庫FILESTREAM檔案組對應的目錄和檔案系統聯絡在一起, 也使用資料庫Directory名稱和檔案系統聯絡在一起,這樣從Windows應用程式訪問它們，就好像它們儲存在檔案系統中，而不必對您的客戶端應用程式進行任何更改，比如使用作業系統、 命令提示符、批處理檔案、C# 或 Visual Basic.NET的介面System.IO的新建貼上複製刪除等直接往\\testmachine\MSSQLSERVER\filestream_testdb\table_1裡面新增更新刪除檔案即完成了對filetable的dml操作。是可讀寫的表

適用場景

想對一些儲存的共享檔案做定期的備份，又不想讓備份太麻煩，就可以使用filetable檔案表，使用檔案表的形式，把共享檔案當成資料庫，然後備份資料庫，資料庫可以全量備份和增量備份，這樣以後想恢復的時候也方便，想恢復到哪天就恢復到哪天

一些實驗結論

1、在filetable檔案表中對應的檔案系統檔案，可以在作業系統中刪除，但是不能修改，比如123.txt檔案，可以刪除，但是無法使用notepad開啟進行修改，notepad視窗顯示the request is not support

2、以上1的notepad視窗不關閉，filetable檔案表對應的資料庫可以正常進行sqlsderver的backup備份，且可以正常restore恢復，恢復後，可以在恢復後的資料庫對應的檔案系統中看到123.txt檔案

3、filetable1和filetable2都是檔案系統表時，無法執行insert into filetable1 select * from filetable2這樣的操作，會報錯Column name or number of supplied values does not match table definition.

4、filetable檔案表的資料庫進行備份後，在異機恢復後，異機的檔案系統裡自動有了filetable對應的檔案，比如filetable檔案表的資料庫對應的檔案系統在pc1的路徑下有檔案\\PC1\MSSQL\dir1\table1,恢復到異機後,異機自動有了\\異機\MSSQL\dir1\table1

5、SELECT * into table1 from filetable1;其中filetable1是filetable檔案表，但是table1就是普通表而非filetable檔案表，也就是說FileTable 中的 SELECT INTO 語句將不在建立的目標表上傳播 FileTable 語義(就像常規表中的 FILESTREAM 列一樣)。 所有目標表列的行為就像常規列的行為一樣。

6、FileTable 不支援分割槽。

7、FileStream、FileTable的資料庫無法搭建mirror，會報錯

A database cannot be enabled for both Database Mirroring and FILESTREAM or for both Database Mirroring and MEMORY_OPTIMIZED_DATA storage.

8、always on時FileStream、FileTable的FILESTREAM 檔案組不需要位於共享磁碟資源

9、FileTable有兩個DIRECTORY_NAME，一個是資料庫級的，一個是表級的。

10、僅FileStream沒有Filetable時，對應檔案系統目錄就是FILESTREAM檔案組對應的目錄，即資料容器

11、Filetable檔案表，對應檔案系統目錄有兩個

11.1、\\SERVERNAME\FILESTREAM_SHARE_NAME\FILESTREAM_DIRECTORY_NAME\FILETABLE_DIRECTORY

11.2、FILESTREAM檔案組對應的目錄，即資料容器

12、FileTable對應檔案系統目錄假如是\\testmachine\MSSQLSERVER\filestream_testdb\table_1時，在這個目錄手工新增一個作業系統檔案比如test.txt後，再查詢select * from testdb.dbo.table1就可以看到一條新增的記錄了，執行delete from testdb.dbo.table1後，發現剛剛新增的檔案不存在了

13、FileTable檔案表對應的檔案系統目錄結構為\\SERVERNAME\INSTANCE_DIRECTORY\DB_DIRECTORY\TABLE_DIRECTORY,例項目錄、資料庫目錄、表目錄分別參見SERVERPROPERTY('FilestreamShareName')、sys.database_filestream_options、sys.filetables

FileStream

官方文件

在SQL Server中，BLOB可以是將資料儲存在表中的標準varbinary(max)資料，也可以是將資料儲存在檔案系統中的FILESTREAM varbinary(max)物件。FILESTREAM 透過將SQL Server資料庫引擎varbinary(max)二進位制大型物件(BLOB)資料作為檔案儲存在NTFS或ReFS檔案系統中，將與該檔案系統整合在一起。 Transact-SQL語句可插入、更新、查詢、搜尋和備份FILESTREAM資料。FILESTREAM 儲存以varbinary(max)列的形式實現，在該列中資料以BLOB的形式儲存在檔案系統中。 BLOB的大小僅受檔案系統容量大小的限制。檔案大小為2GB 的varbinary(max)標準限制不適用於儲存在檔案系統中的BLOB。若要指定列應將資料儲存在檔案系統中，請對varbinary(max)列指定FILESTREAM屬性。 這樣資料庫引擎會將該列的所有資料儲存在檔案系統，而不是資料庫檔案中。FILESTREAM 資料必須儲存在FILESTREAM檔案組中。 FILESTREAM檔案組是包含檔案系統目錄而非檔案本身的專用檔案組。這些檔案系統目錄稱為“資料容器” 。 資料容器是 資料庫引擎儲存 與 檔案系統儲存 之間的介面,可以將多個資料容器新增到 FILESTREAM 檔案組。

開啟FileStream 功能

1. 開啟 SQL Server 配置管理器， 在SQL server 服務下找到你要啟用的SQL Server服務(預設例項一般是 MSSQLServer)，右擊該服務，選擇屬性，在屬性視窗你可以看到FileStream 標籤，選中” 針對 Transact-SQL 訪問啟用 FILESTREAM"

2.執行以下命令，或右鍵例項--properties--advanced--filestream，選擇full access enabled

EXEC sp_configure filestream_access_level, 2

RECONFIGURE

3.對資料庫test增加一個FILESTREAM型別的filegroup組filestream1

ALTER DATABASE [test] ADD FILEGROUP [filestream1] CONTAINS FILESTREAM

4、對資料庫test增加一個FILESTREAM型別的file檔案file_stream1,filename的值G:\DEFAULT.DATA\file_filestream1是目錄非檔案，且父目錄G:\DEFAULT.DATA必須先存在，子目錄file_filestream1會自動建立(子目錄不能先存在否則會報錯目錄已經存在導致無法建立子目錄)。

ALTER DATABASE test

add FILE (name = 'file_stream1',FILENAME = 'G:\DEFAULT.DATA\file_filestream1') TO FILEGROUP filestream1

5、

5.1、建立普通表使用FILESTREAM

CREATE TABLE table1

(

Id INT NOT NULL PRIMARY KEY,

Photo VARBINARY(MAX) FILESTREAM NULL,

RowGuid UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL

UNIQUE DEFAULT NEWID()

)

G:\DEFAULT.DATA\file_filestream1目錄下新增了一個目錄假設為1，且子目錄1下面還自動生成了一個子目錄，假設為1_1

5.2、建立基於FILESTREAM的filetable檔案表

ALTER DATABASE [test] SET FILESTREAM (NON_TRANSACTED_ACCESS = FULL,DIRECTORY_NAME = N'test');

--設定filestream的完全非事務性訪問，且必須設定資料庫級別的directory名稱

CREATE TABLE table2 AS FILETABLE FILESTREAM_ON filestream1 WITH

(

FILETABLE_DIRECTORY = N'table2', FILETABLE_COLLATE_FILENAME = database_default

)

其中FILETABLE_COLLATE_FILENAME表示排序規則，值可以為database_default或SQL_Latin1_General_CP1_CI_AS等等

G:\DEFAULT.DATA\file_filestream1目錄下又新增了一個目錄假設為2，且子目錄2下面還自動生成了一個子目錄，假設為2_1

6、往上面5例子中的table1表插入資料

INSERT INTO test.dbo.table1 VALUES ( 1, NULL,newid ());

G:\DEFAULT.DATA\file_filestream1\2\2_1下沒有檔案

INSERT INTO test.dbo.table1 VALUES ( 3, CAST('Seismic Data' as varbinary(max)),newid ());

G:\DEFAULT.DATA\file_filestream1\2\2_1下生成了一個檔案00000022-00000088-0002

7、更新table1表的記錄

update test.dbo.table1 set photo=CAST('Xray 1' as varbinary(max)) where id=3

G:\DEFAULT.DATA\file_filestream1\2\2_1下生成了一個檔案00000022-000000c5-0005

8、刪除table1表的記錄

delete from test.dbo.table1

G:\DEFAULT.DATA\file_filestream1\2\2_1下面的兩個檔案還在

使用 FILESTREAM 儲存時，請考慮以下內容：

如果表包含 FILESTREAM 列，則每一行都必須具有唯一的一個非 Null 行 ID。

不能巢狀 FILESTREAM 資料容器。

使用故障轉移群集時，FILESTREAM 檔案組必須位於共享磁碟資源上。個人已實驗過，always on時FILESTREAM 檔案組不需要位於共享磁碟資源

FileTable

官方文件

FileTable是基於SQL Server 2008的FILESTREAM特性上而來的,允許我們把Windows檔案儲存在SQL Server中，也就是說可以在SQL Server中將檔案和文件儲存在稱作FileTable的特別的表中，但是從Windows應用程式訪問它們，就好像它們儲存在檔案系統中，而不必對您的客戶端應用程式進行任何更改。而且這些FileTable表又可以使用SQL Server進行備份和恢復，在檔案系統中對檔案的建立更新刪除都會反映到FILETABLE表中，對FILETABLE表執行DML操作，這些改動同樣會反映到檔案本身的檔案系統屬性，FILETABLE表中的每一行資料代表了一個檔案，表中的欄位代表了檔案的一些屬性列，像檔案建立時間和修改時間，以及檔案的路徑。FileTable 功能使企業客戶能夠在 SQL Server 資料庫中儲存非結構化檔案資料和目錄層次結構。

FileTable關於事務性和非事務性的問題，FileTable作為一張SQL Server表是支援事務性的，但是由於對檔案的操作是屬於一個Windows檔案系統的操作行為，是不具備事務性的，所以也可以在資料庫級別配置FILESTREAM資料的非事務性訪問級別如NON_TRANSACTED_ACCESS = FULL。但是不代表說FileStream本身是必須開啟這個選項的。也就是如果我不使用FileTable，我再新增FILESTREAM FILEGROUP的時候是不需要啟用non-transactional access選項。

建立filetable檔案表

開啟FILESTREAM功能並建立好了FILESTREAM的file和filegroup後，再配置一下NON_TRANSACTED_ACCESS、DIRECTORY_NAME就可以建立filetable了

ALTER DATABASE [testdb] ADD FILEGROUP [file_stream1] CONTAINS FILESTREAM

ALTER DATABASE [testdb] ADD FILE (name = 'filestream001',FILENAME = 'G:\DEFAULT\filestream1') TO FILEGROUP file_stream1

ALTER DATABASE [testdb] SET FILESTREAM(NON_TRANSACTED_ACCESS = FULL,DIRECTORY_NAME = N'filestream_testdb');

CREATE TABLE table1 AS FILETABLE FILESTREAM_ON file_stream1

WITH

(

FILETABLE_DIRECTORY = N'table_1', FILETABLE_COLLATE_FILENAME = database_default

)

建立完表之後你可以透過Windows Explorer在FileStream共享路徑下新建、刪除、修改檔案，共享路徑為

\\SERVERNAME\FILESTREAM_SHARE_NAME\FILESTREAM_DIRECTORY_NAME\FILETABLE_DIRECTORY，即進入\\testmachine\MSSQLSERVER\filestream_testdb\table_1目錄

FILESTREAM_SHARE_NAME：見SSMS-->右鍵例項-->Properties-->Advanced-->FILESTREAM-->FILESTREAM SHARE NAME,預設是MSSQLSERVER

FILESTREAM_DIRECTORY_NAME：見SSMS-->右鍵資料庫-->Properties-->Options-->FILESTREAM-->FILESTREAM Directory Name

例如filetable表名testdb.dbo.table1

1、對應檔案系統目錄就是G:\DEFAULT.DATA\filefilestream1\1\1_1,首先FILESTREAM檔案組目錄G:\DEFAULT.DATA\filefilestream1\目錄，table1表建立後，檔案組目錄下新增了一個目錄假設為1，且子目錄1下面還自動生成了一個子目錄，假設為1_1

2、對應檔案系統目錄是\\testmachine\MSSQLSERVER\filestream_testdb\table_1，在這個目錄手工新增一個作業系統檔案比如test.txt後，再查詢select * from testdb.dbo.table1就可以看到一條新增的記錄了，執行delete from testdb.dbo.table1後，發現剛剛新增的檔案不存在了

3、\\testmachine\MSSQLSERVER\filestream_testdb\table_1裡面每新增1個檔案，G:\DEFAULT.DATA\filefilestream1\1\1_1下面新增2個檔案

4、\\testmachine\MSSQLSERVER\filestream_testdb\table_1刪除那個新增的檔案後，G:\DEFAULT.DATA\filefilestream1\1\1_1下面的2個檔案還在

將檔案載入到 FileTable 中的方法

1、在 Windows 資源管理器中將檔案從原始檔夾拖放到新的 FileTable 資料夾。

2、從命令提示符下、批處理檔案或指令碼中使用命令列選項(如 MOVE、COPY、XCOPY 或 ROBOCOPY)。

3、用 C# 或 Visual Basic.NET 編寫一個用於移動或複製檔案的自定義應用程式。 從 System.IO 名稱空間呼叫方法。

管理filetable

1、查詢哪些表是filetable檔案表

SELECT * FROM sys.filetables;

或

SELECT * FROM sys.tables WHERE is_filetable = 1;

2、禁用和重新啟用表級別 FileTable 名稱空間

ALTER TABLE filetable_name ENABLE|DISABLE FILETABLE_NAMESPACE;

3、終止與 FileTable 關聯的開啟的檔案控制程式碼(FileTable 中儲存的檔案的開啟控制程式碼可以阻止某些管理任務所需的獨佔訪問。 若要啟用緊急任務，您可能要終止與一個或多個 FileTable 關聯的開啟的檔案控制程式碼)

3.1、與 FileTable 關聯的開啟的檔案控制程式碼的列表

SELECT * FROM sys.dm_filestream_non_transacted_handles;

3.2、終止與 FileTable 關聯的開啟的檔案控制程式碼

USE database_name;

-- Kill all open handles in all the filetables in the database.

EXEC sp_kill_filestream_non_transacted_handles;

-- Kill all open handles in a single filetable.

EXEC sp_kill_filestream_non_transacted_handles @table_name = 'filetable_name';

-- Kill a single handle.

EXEC sp_kill_filestream_non_transacted_handles @handle_id = integer_handle_id;

4、查詢例項的FILESTREAM 使用的DIRECTORY_NAME
SELECT  SERVERPROPERTY('FilestreamShareName')

5、查詢FILETABLE表的資料庫對應的DIRECTORY_NAME

select db_name(database_id),* from sys.database_filestream_options

僅僅使用filestream功能時，資料庫不需要對應的DIRECTORY_NAME

6、查詢FILETABLE表對應的DIRECTORY_NAME

select object_name(object_id),* from sys.filetables

7、查詢filetable表testdb.dbo.table1中的檔案完整路徑名稱

SELECT FileTableRootPath()+[file_stream].GetFileNamespacePath(),name FROM testdb.dbo.table1

8、查詢FILETABLE表對應的檔案系統目錄，右鍵FILETABLE表，選擇Explore FileTable Directory即可

關於表級別的FILETABLE_DIRECTORY的說明

FileTable表的FILETABLE_DIRECTORY在建立表的時候會自動建立，如果建表時沒有指定FILETABLE_DIRECTORY則FILETABLE_DIRECTORY等於表名。不同的FileTable表必須使用不同FILETABLE_DIRECTORY目錄，一個FILETABLE_DIRECTORY目錄不能被兩張不同的表使用

1、

ALTER DATABASE [testdb] SET FILESTREAM(NON_TRANSACTED_ACCESS = FULL,DIRECTORY_NAME = N'testdb');

--資料庫的DIRECTORY_NAME目錄名稱為testdb，見SSMS-->右鍵資料庫-->Properties-->Options-->FILESTREAM-->FILESTREAM Directory Name

絕對路徑：\\testmachine\MSSQLSERVER\testdb\

2、

CREATE TABLE table1 AS FILETABLE FILESTREAM_ON file_stream1 WITH

(

FILETABLE_DIRECTORY = N'table1', FILETABLE_COLLATE_FILENAME = database_default

)

--表table1的FILETABLE_DIRECTORY目錄名稱為table1

絕對路徑：\\testmachine\MSSQLSERVER\testdb\table1

3、

CREATE TABLE table2 AS FILETABLE FILESTREAM_ON file_stream1 WITH

(

FILETABLE_DIRECTORY = N'table1', FILETABLE_COLLATE_FILENAME = database_default

)

--報錯如下，因為FILETABLE_DIRECTORY 目錄名稱'table1'已經被表table1使用了，不能再給表table2使用

FILETABLE_DIRECTORY 'table1' attempting to be set on table 'table2' is not unique in the database 'file_db'. Provide a unique value for the option FILETABLE_DIRECTORY to this operation.

4、

CREATE TABLE table2 AS FILETABLE FILESTREAM_ON file_stream1 WITH

(

FILETABLE_DIRECTORY = N'table99', FILETABLE_COLLATE_FILENAME = database_default

)

--表table2的FILETABLE_DIRECTORY目錄名稱是table99

絕對路徑：\\testmachine\MSSQLSERVER\testdb\table99

5、

CREATE TABLE table3 AS FILETABLE FILESTREAM_ON file_stream1 WITH

(

FILETABLE_COLLATE_FILENAME = database_default

)

--表table3的FILETABLE_DIRECTORY目錄名稱預設是table3

絕對路徑：\\testmachine\MSSQLSERVER\testdb\table3

Filetable遇到過的報錯

1、備份報錯

The operating system returned the error '5(Access is denied.)' while attempting 'CreateFile' on 'T:\DEFAULT.FILESTREAM.DATA\Netapp1_9_FS2\ee50c020-b8df-485b-b7fd-924123a9f8e5\1dafa2ac-3d5e-4b8e-b4c5-711ded06ae19\00001526-000034f6-0002'.

Msg 3013, Level 16, State 1, Line 1

BACKUP DATABASE is terminating abnormally.

2、恢復報錯(備份包是正常備份的)

The operating system returned the error '32(The process cannot access the file because it is being used by another process.)' while attempting 'OpenFile' on 'F:\FS2.FILESTREAM.DATA\Netapp2_1_FS5\0bb73706-bf4a-4d2e-84bb-fa714c2ba93a\d6c88672-71bf-40b3-9229-667528207588\00000352-0003d60d-0015'.

Msg 3013, Level 16, State 1, Line 2

RESTORE DATABASE is terminating abnormally.

3、恢復報錯(備份包是正常備份的)

A previous restore operation was interrupted and did not complete processing on file 'AMPFileStream'. Either restore the backup set that was interrupted or restart the restore sequence.

4、\\SERVERNAME\INSTANCE_DIRECTORY\DB_DIRECTORY\TABLE_DIRECTORY正常開啟，有一次重啟伺服器後，無法開啟了，檢查了資料庫一切正常，在該伺服器內使用\\127.0.0.1\INSTANCE_DIRECTORY\DB_DIRECTORY\TABLE_DIRECTORY可以正常開啟，使用\\SERVERNAME\INSTANCE_DIRECTORY\DB_DIRECTORY\TABLE_DIRECTORY或\\該伺服器IP\INSTANCE_DIRECTORY\DB_DIRECTORY\TABLE_DIRECTORY都無法開啟，想到應該是遠端訪問有問題，重置filestream功能即可，在Sql Server Configuration Manager--Sql Server Services--右鍵對應例項--Properties--FILESTREAM