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操作。是可讀寫的表

一些實驗結論

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

官方文檔https://docs.microsoft.com/zh-cn/sql/relational-databases/blob/filestream-sql-server?view=sql-server-2017

在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

官方文檔https://docs.microsoft.com/zh-cn/sql/relational-databases/blob/filetables-sql-server?view=sql-server-2017

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