SQL Server的FileStream和FileTable深入剖析
發(fā)布日期：2022-01-10 11:57 | 文章來源：站長(zhǎng)之家
1。在數(shù)據(jù)庫(kù)中存儲(chǔ)結(jié)構(gòu)化數(shù)據(jù)，在文件系統(tǒng)中存儲(chǔ)非結(jié)構(gòu)化數(shù)據(jù)，然后數(shù)據(jù)庫(kù)里有一個(gè)字段記錄文件系統(tǒng)的路徑，雖然這種方法成本合算，但它引入了額外的復(fù)雜度，因?yàn)槟阈枰謩?dòng)去保證跨關(guān)系和非關(guān)系系統(tǒng)管理事務(wù)的完整性。
2。將結(jié)構(gòu)化數(shù)據(jù)和非結(jié)構(gòu)化數(shù)據(jù)都存儲(chǔ)在數(shù)據(jù)庫(kù)中，多年以來，數(shù)據(jù)庫(kù)一直都支持存儲(chǔ)非關(guān)系數(shù)據(jù)，如二進(jìn)制大對(duì)象，或BLOB，SQL Server稱之為varbinary數(shù)據(jù)類型，這樣的好處是能充分利用數(shù)據(jù)庫(kù)的特性（如事務(wù)支持，備份和恢復(fù)支持，集成安全性的支持，全文搜索支持等），但成本費(fèi)用會(huì)更高，所需的磁盤空間更多，因?yàn)槭谴鎯?chǔ)在同一行數(shù)據(jù)里，存儲(chǔ)和檢索時(shí)間更長(zhǎng)，對(duì)應(yīng)用程序的整體性能也會(huì)有負(fù)面影響。（如果你寫查詢時(shí)用select * from xxx 就很慢了）
3。SQL Server 2008中引入的FILESTREAM數(shù)據(jù)類型來存儲(chǔ)非結(jié)構(gòu)化數(shù)據(jù)，如文檔，演示文稿，視頻，音頻，圖像，數(shù)據(jù)庫(kù)中存儲(chǔ)的是文件系統(tǒng)上的一個(gè)指針。在SQL Server 2008中，新的FILESTREAM（文件流）特性是在現(xiàn)有的varbinary(max)數(shù)據(jù)類型之上實(shí)現(xiàn)的，你可以在服務(wù)器的文件系統(tǒng)上存儲(chǔ)真實(shí)的數(shù)據(jù)，但可以在數(shù)據(jù)庫(kù)上下文內(nèi)管理和訪問。自動(dòng)保證了事務(wù)的完整性。
4。SQL Server 2012的FileTable則進(jìn)一步增強(qiáng)，它可以讓應(yīng)用程序通過引入FileTable整合其存儲(chǔ)和數(shù)據(jù)管理組件，允許非事務(wù)性訪問，提供集成的對(duì)非結(jié)構(gòu)化數(shù)據(jù)和元數(shù)據(jù)的全文搜索和語義搜索。 下面詳細(xì)談?wù)勥@兩項(xiàng)新功能。
了解在SQL Server 2008中的FileStream
FILESTREAM數(shù)據(jù)類型作為varbinary（max）列實(shí)現(xiàn)的，數(shù)據(jù)是存儲(chǔ)在NTFS文件系統(tǒng)，數(shù)據(jù)庫(kù)中存放的是指針。在這種情況下，存儲(chǔ)不再是BLOB的2GB大小的限制，只是受制于NTFS文件系統(tǒng)的文件大小。FileStream是默認(rèn)禁止的，所以你需要對(duì)varbinary（max）列指定FILESTREAM屬性。這樣SQL Server才不會(huì)把BLOB存到SQL Server數(shù)據(jù)庫(kù)，而是存到NTFS文件系統(tǒng)。
將BLOB數(shù)據(jù)存儲(chǔ)在NTFS文件系統(tǒng)上的帶來了一些好處：
和直接操作NTFS文件系統(tǒng)的數(shù)據(jù)流的性能一樣
FILESTREAM數(shù)據(jù)沒有使用SQL Server緩沖池的，因此SQL Server緩沖池的查詢處理，并不會(huì)受到FILESTREAM數(shù)據(jù)的影響。
不再有BLOB的2G大小的限制。
事務(wù)的一致性。
SQLServer集成的安全模型。
備份和恢復(fù)時(shí)，會(huì)包含F(xiàn)ILESTREAM BLOB數(shù)據(jù)。
支持全文搜索。 用SELECT，INSERT，UPDATE和DELETE語句，操作帶FILESTREAM數(shù)據(jù)的表，性能沒有變慢。
為了使用這項(xiàng)新功能，我們首先需要在實(shí)例級(jí)別啟用它（在安裝過程中，或通過修改SQL Server實(shí)例的屬性，或通過使用sp_configure來更改實(shí)例屬性），然后創(chuàng)建或修改數(shù)據(jù)庫(kù)，有一個(gè)文件組有FileStream屬性，然后創(chuàng)建一個(gè)表帶有varbinary（max）數(shù)據(jù)類型列，并指定FileStream屬性。 了解SQL Server 2012的FileTable
FileTable使用FILESTREAM的基礎(chǔ)上進(jìn)一步加強(qiáng)，它既允許直接的，事務(wù)性的，存取FILESTREAM列大型數(shù)據(jù)。FileTable表也可以配置為允許非事務(wù)性訪問文件，而無需事先SQLServer授權(quán)。 FileTable是一種特殊類型的表，它的結(jié)構(gòu)是固定的，不像普通用戶表可以定義自己的字段，就好像它是一個(gè)文件夾中的文件系統(tǒng)。一個(gè)的FileTable包含的FileStream隨著幾個(gè)文件級(jí)屬性（file_id，名稱，路徑，創(chuàng)建日期，修改日期，最后訪問時(shí)間等），文件和目錄層次結(jié)構(gòu)的數(shù)據(jù)。這意味著FileTable中的每一行數(shù)據(jù)代表一個(gè)文件系統(tǒng)上的文件或目錄。 FileTable在文件系統(tǒng)表現(xiàn)為一個(gè)indows共享目錄，里面有文件和目錄數(shù)據(jù)，你可以通過非事務(wù)性的文件訪問（基于Windows API的應(yīng)用程序能夠訪問文件，而無需SQLServer訪問權(quán)限）。對(duì)于Windows應(yīng)用程序，這看起來像一個(gè)正常的網(wǎng)絡(luò)共享位置的文件和目錄。應(yīng)用程序可以使用Windows API來管理這個(gè)網(wǎng)絡(luò)共享位置的文件和目錄。 Windows API操作是非事務(wù)性的，和數(shù)據(jù)庫(kù)是不相關(guān)。然而，F(xiàn)ileTable是基于FileStream實(shí)現(xiàn)的，所以SQL的事務(wù)是支持的。FileTable也可以通過正常的Transact-SQL命令查詢和更新。他們還集成了SQL Server管理工具和功能，如備份和恢復(fù)。 我們需要分開配置FileTable和FILESTREAM。這意味著，我們可以繼續(xù)只使用FileStream的功能，而無需啟用非事務(wù)性訪問或創(chuàng)建FileTable。
開始使用SQL Server 2012的FileTable
啟用FileStream
USE master GO
EXEC sp_configure 'filestream access level', 2
Go
RECONFIGURE GO
--You can use this statement to see current
--config value and running value
EXEC sp_configure filestream_access_level;
GO
按 Ctrl+C 復(fù)制代碼創(chuàng)建LearnFileTable數(shù)據(jù)庫(kù)
USE master
GO
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'LearnFileTable')
DROP DATABASE LearnFileTable
GO
CREATE DATABASE LearnFileTable
--Details of primary file group
ON PRIMARY
( NAME = LearnFileTable_Primary,
FILENAME =N'D:\FileTable\LearnFileTable_Data.mdf',
SIZE = 10MB,
MAXSIZE = 50MB,
FILEGROWTH = 5MB),
--Details of additional filegroup to be used to store data
FILEGROUP DataGroup
( NAME = LearnFileTable_Data,
FILENAME =N'D:\FileTable\LearnFileTable_Data.ndf',
SIZE = 10MB,
MAXSIZE = 50MB,
FILEGROWTH = 5MB),
--Details of special filegroup to be used to store FILESTREAM data
FILEGROUP FSDataGroup CONTAINS FILESTREAM
( NAME = FileStream,
--FILENAME refers to the path and not to the actual file name. It
--creates a folder which contains a filestream.hdr file and
--also a folder $FSLOG folder as depicted in image below
FILENAME =N'D:\FileTable\FSData')
--Details of log file
LOG ON
(Name = LearnFileTable_Log,
FILENAME = 'D:\FileTable\LearnFileTable_Log.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB
)
WITH FILESTREAM (NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'LearnFileTable')
--Other option for NON_TRANSACTED_ACCESS is READ_ONLY or OFF
GO
按 Ctrl+C 復(fù)制代碼檢查FileStream/FileTable
-- Check the Filestream/FileTable Options
SELECT DB_NAME(database_id), non_transacted_access, non_transacted_access_desc, directory_name FROM sys.database_filestream_options
WHERE DB_NAME(database_id) = 'LearnFileTable'
創(chuàng)建FileTable
USE LearnFileTable
GO
CREATE TABLE MyFirstFileTable AS FileTable
WITH
(
FileTable_Directory = 'MyFirstFileTable',
FileTable_Collate_Filename = database_default
);
GO
按 Ctrl+C 復(fù)制代碼如果我們創(chuàng)建FileTable前沒有啟用FileStream，會(huì)報(bào)錯(cuò)
Msg 1969, Level 16, State 1, Line 1 Default FILESTREAM filegroup is not available in database '<database_name>'創(chuàng)建后，我們查詢一下，沒有記錄
USE LearnFileTable SELECT * FROM [dbo].[MyFirstFileTable]
在企業(yè)管理器選擇FileTable，右鍵 "Explorer FileTable Directory" link as shown below:
你會(huì)看到網(wǎng)絡(luò)共享目錄. 手動(dòng)添加幾個(gè)文件到該目錄，我們返回SQLServer企業(yè)管理器看看有什么事情發(fā)生:
再運(yùn)行一次查詢語句
USE LearnFileTable SELECT * FROM [dbo].[MyFirstFileTable]
總結(jié)
在這篇文章中，我談到了利用SQL Server的FileStream和FileTable功能存儲(chǔ)非結(jié)構(gòu)化的數(shù)據(jù)。 FILESTREAM功能在文件系統(tǒng)中存儲(chǔ)非結(jié)構(gòu)化數(shù)據(jù)，并把文件的指針保存在數(shù)據(jù)庫(kù)，而FileTable進(jìn)一步擴(kuò)展了這一功能允許非事務(wù)性訪問（訪問文件，而無需事先授權(quán)，共享位置）。換句話說，有了這個(gè)功能，我們可以通過文件系統(tǒng)來管理非結(jié)構(gòu)化數(shù)據(jù)，而不是在SQL Server管理，卻依然可以在SQL Server中的事務(wù)訪問這些文件。
2。將結(jié)構(gòu)化數(shù)據(jù)和非結(jié)構(gòu)化數(shù)據(jù)都存儲(chǔ)在數(shù)據(jù)庫(kù)中，多年以來，數(shù)據(jù)庫(kù)一直都支持存儲(chǔ)非關(guān)系數(shù)據(jù)，如二進(jìn)制大對(duì)象，或BLOB，SQL Server稱之為varbinary數(shù)據(jù)類型，這樣的好處是能充分利用數(shù)據(jù)庫(kù)的特性（如事務(wù)支持，備份和恢復(fù)支持，集成安全性的支持，全文搜索支持等），但成本費(fèi)用會(huì)更高，所需的磁盤空間更多，因?yàn)槭谴鎯?chǔ)在同一行數(shù)據(jù)里，存儲(chǔ)和檢索時(shí)間更長(zhǎng)，對(duì)應(yīng)用程序的整體性能也會(huì)有負(fù)面影響。（如果你寫查詢時(shí)用select * from xxx 就很慢了）
3。SQL Server 2008中引入的FILESTREAM數(shù)據(jù)類型來存儲(chǔ)非結(jié)構(gòu)化數(shù)據(jù)，如文檔，演示文稿，視頻，音頻，圖像，數(shù)據(jù)庫(kù)中存儲(chǔ)的是文件系統(tǒng)上的一個(gè)指針。在SQL Server 2008中，新的FILESTREAM（文件流）特性是在現(xiàn)有的varbinary(max)數(shù)據(jù)類型之上實(shí)現(xiàn)的，你可以在服務(wù)器的文件系統(tǒng)上存儲(chǔ)真實(shí)的數(shù)據(jù)，但可以在數(shù)據(jù)庫(kù)上下文內(nèi)管理和訪問。自動(dòng)保證了事務(wù)的完整性。
4。SQL Server 2012的FileTable則進(jìn)一步增強(qiáng)，它可以讓應(yīng)用程序通過引入FileTable整合其存儲(chǔ)和數(shù)據(jù)管理組件，允許非事務(wù)性訪問，提供集成的對(duì)非結(jié)構(gòu)化數(shù)據(jù)和元數(shù)據(jù)的全文搜索和語義搜索。 下面詳細(xì)談?wù)勥@兩項(xiàng)新功能。
了解在SQL Server 2008中的FileStream
FILESTREAM數(shù)據(jù)類型作為varbinary（max）列實(shí)現(xiàn)的，數(shù)據(jù)是存儲(chǔ)在NTFS文件系統(tǒng)，數(shù)據(jù)庫(kù)中存放的是指針。在這種情況下，存儲(chǔ)不再是BLOB的2GB大小的限制，只是受制于NTFS文件系統(tǒng)的文件大小。FileStream是默認(rèn)禁止的，所以你需要對(duì)varbinary（max）列指定FILESTREAM屬性。這樣SQL Server才不會(huì)把BLOB存到SQL Server數(shù)據(jù)庫(kù)，而是存到NTFS文件系統(tǒng)。
將BLOB數(shù)據(jù)存儲(chǔ)在NTFS文件系統(tǒng)上的帶來了一些好處：
和直接操作NTFS文件系統(tǒng)的數(shù)據(jù)流的性能一樣
FILESTREAM數(shù)據(jù)沒有使用SQL Server緩沖池的，因此SQL Server緩沖池的查詢處理，并不會(huì)受到FILESTREAM數(shù)據(jù)的影響。
不再有BLOB的2G大小的限制。
事務(wù)的一致性。
SQLServer集成的安全模型。
備份和恢復(fù)時(shí)，會(huì)包含F(xiàn)ILESTREAM BLOB數(shù)據(jù)。
支持全文搜索。 用SELECT，INSERT，UPDATE和DELETE語句，操作帶FILESTREAM數(shù)據(jù)的表，性能沒有變慢。
為了使用這項(xiàng)新功能，我們首先需要在實(shí)例級(jí)別啟用它（在安裝過程中，或通過修改SQL Server實(shí)例的屬性，或通過使用sp_configure來更改實(shí)例屬性），然后創(chuàng)建或修改數(shù)據(jù)庫(kù)，有一個(gè)文件組有FileStream屬性，然后創(chuàng)建一個(gè)表帶有varbinary（max）數(shù)據(jù)類型列，并指定FileStream屬性。 了解SQL Server 2012的FileTable
FileTable使用FILESTREAM的基礎(chǔ)上進(jìn)一步加強(qiáng)，它既允許直接的，事務(wù)性的，存取FILESTREAM列大型數(shù)據(jù)。FileTable表也可以配置為允許非事務(wù)性訪問文件，而無需事先SQLServer授權(quán)。 FileTable是一種特殊類型的表，它的結(jié)構(gòu)是固定的，不像普通用戶表可以定義自己的字段，就好像它是一個(gè)文件夾中的文件系統(tǒng)。一個(gè)的FileTable包含的FileStream隨著幾個(gè)文件級(jí)屬性（file_id，名稱，路徑，創(chuàng)建日期，修改日期，最后訪問時(shí)間等），文件和目錄層次結(jié)構(gòu)的數(shù)據(jù)。這意味著FileTable中的每一行數(shù)據(jù)代表一個(gè)文件系統(tǒng)上的文件或目錄。 FileTable在文件系統(tǒng)表現(xiàn)為一個(gè)indows共享目錄，里面有文件和目錄數(shù)據(jù)，你可以通過非事務(wù)性的文件訪問（基于Windows API的應(yīng)用程序能夠訪問文件，而無需SQLServer訪問權(quán)限）。對(duì)于Windows應(yīng)用程序，這看起來像一個(gè)正常的網(wǎng)絡(luò)共享位置的文件和目錄。應(yīng)用程序可以使用Windows API來管理這個(gè)網(wǎng)絡(luò)共享位置的文件和目錄。 Windows API操作是非事務(wù)性的，和數(shù)據(jù)庫(kù)是不相關(guān)。然而，F(xiàn)ileTable是基于FileStream實(shí)現(xiàn)的，所以SQL的事務(wù)是支持的。FileTable也可以通過正常的Transact-SQL命令查詢和更新。他們還集成了SQL Server管理工具和功能，如備份和恢復(fù)。 我們需要分開配置FileTable和FILESTREAM。這意味著，我們可以繼續(xù)只使用FileStream的功能，而無需啟用非事務(wù)性訪問或創(chuàng)建FileTable。
開始使用SQL Server 2012的FileTable
啟用FileStream
復(fù)制代碼 代碼如下:
USE master GO
EXEC sp_configure 'filestream access level', 2
Go
RECONFIGURE GO
--You can use this statement to see current
--config value and running value
EXEC sp_configure filestream_access_level;
GO
按 Ctrl+C 復(fù)制代碼創(chuàng)建LearnFileTable數(shù)據(jù)庫(kù)
復(fù)制代碼 代碼如下:
USE master
GO
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'LearnFileTable')
DROP DATABASE LearnFileTable
GO
CREATE DATABASE LearnFileTable
--Details of primary file group
ON PRIMARY
( NAME = LearnFileTable_Primary,
FILENAME =N'D:\FileTable\LearnFileTable_Data.mdf',
SIZE = 10MB,
MAXSIZE = 50MB,
FILEGROWTH = 5MB),
--Details of additional filegroup to be used to store data
FILEGROUP DataGroup
( NAME = LearnFileTable_Data,
FILENAME =N'D:\FileTable\LearnFileTable_Data.ndf',
SIZE = 10MB,
MAXSIZE = 50MB,
FILEGROWTH = 5MB),
--Details of special filegroup to be used to store FILESTREAM data
FILEGROUP FSDataGroup CONTAINS FILESTREAM
( NAME = FileStream,
--FILENAME refers to the path and not to the actual file name. It
--creates a folder which contains a filestream.hdr file and
--also a folder $FSLOG folder as depicted in image below
FILENAME =N'D:\FileTable\FSData')
--Details of log file
LOG ON
(Name = LearnFileTable_Log,
FILENAME = 'D:\FileTable\LearnFileTable_Log.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB
)
WITH FILESTREAM (NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'LearnFileTable')
--Other option for NON_TRANSACTED_ACCESS is READ_ONLY or OFF
GO
按 Ctrl+C 復(fù)制代碼檢查FileStream/FileTable
復(fù)制代碼 代碼如下:
-- Check the Filestream/FileTable Options
SELECT DB_NAME(database_id), non_transacted_access, non_transacted_access_desc, directory_name FROM sys.database_filestream_options
WHERE DB_NAME(database_id) = 'LearnFileTable'
創(chuàng)建FileTable
復(fù)制代碼 代碼如下:
USE LearnFileTable
GO
CREATE TABLE MyFirstFileTable AS FileTable
WITH
(
FileTable_Directory = 'MyFirstFileTable',
FileTable_Collate_Filename = database_default
);
GO
按 Ctrl+C 復(fù)制代碼如果我們創(chuàng)建FileTable前沒有啟用FileStream，會(huì)報(bào)錯(cuò)
Msg 1969, Level 16, State 1, Line 1 Default FILESTREAM filegroup is not available in database '<database_name>'創(chuàng)建后，我們查詢一下，沒有記錄
USE LearnFileTable SELECT * FROM [dbo].[MyFirstFileTable]
在企業(yè)管理器選擇FileTable，右鍵 "Explorer FileTable Directory" link as shown below:
你會(huì)看到網(wǎng)絡(luò)共享目錄. 手動(dòng)添加幾個(gè)文件到該目錄，我們返回SQLServer企業(yè)管理器看看有什么事情發(fā)生:
再運(yùn)行一次查詢語句
USE LearnFileTable SELECT * FROM [dbo].[MyFirstFileTable]
總結(jié)
在這篇文章中，我談到了利用SQL Server的FileStream和FileTable功能存儲(chǔ)非結(jié)構(gòu)化的數(shù)據(jù)。 FILESTREAM功能在文件系統(tǒng)中存儲(chǔ)非結(jié)構(gòu)化數(shù)據(jù)，并把文件的指針保存在數(shù)據(jù)庫(kù)，而FileTable進(jìn)一步擴(kuò)展了這一功能允許非事務(wù)性訪問（訪問文件，而無需事先授權(quán)，共享位置）。換句話說，有了這個(gè)功能，我們可以通過文件系統(tǒng)來管理非結(jié)構(gòu)化數(shù)據(jù)，而不是在SQL Server管理，卻依然可以在SQL Server中的事務(wù)訪問這些文件。
版權(quán)聲明：本站文章來源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請(qǐng)保持原文完整并注明來源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非www.sddonglingsh.com所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來，僅供學(xué)習(xí)參考，不代表本站立場(chǎng)，如有內(nèi)容涉嫌侵權(quán)，請(qǐng)聯(lián)系alex-e#qq.com處理。
相關(guān)文章
關(guān)注官方微信