傳統 `varbinary(max)` 存資料庫：備份膨脹、Buffer Pool 污染、無法直接檔案存取。純檔案系統：交易不一致、備份分離、權限管理分離。SQL Server 2008 **FILESTREAM** 整合 NTFS 流 API，實現 資料庫交易一致性 + 檔案系統流存取效能 + 整合備份/還原。本文從啟用、建立、存取、遷移、效能調優全流程實戰。
EXEC sp_configure filestream_access_level, 2; -- 0=關閉, 1=T-SQLのみ, 2=T-SQL+Win32, 3=含遠端
RECONFIGURE;
CREATE DATABASE FS_Demo
ON PRIMARY
( NAME = FS_Demo_Data, FILENAME = 'D:\SQLData\FS_Demo.mdf' ),
FILEGROUP FS_FG CONTAINS FILESTREAM
( NAME = FS_Demo_FS, FILENAME = 'E:\FSData\FS_Demo_FS' ) -- 此目錄必須存在、空、NTFS
LOG ON
( NAME = FS_Demo_Log, FILENAME = 'F:\SQLLog\FS_Demo.ldf' );
CREATE TABLE dbo.Documents (
DocID UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE DEFAULT NEWSEQUENTIALID(),
FileName NVARCHAR(255) NOT NULL,
ContentType NVARCHAR(100) NOT NULL,
FileSize BIGINT NOT NULL,
Content VARBINARY(MAX) FILESTREAM NULL, -- 關鍵：FILESTREAM 屬性
UploadedAt DATETIME2(3) NOT NULL DEFAULT SYSUTCDATETIME(),
UploadedBy INT NOT NULL,
CONSTRAINT PK_Documents PRIMARY KEY CLUSTERED (DocID)
) ON PRIMARY FILESTREAM_ON FS_FG; -- 指定 FILESTREAM 檔案群組
-- 從應用層串流插入 (避免大物件進記憶體)
INSERT INTO dbo.Documents (FileName, ContentType, FileSize, Content, UploadedBy)
SELECT @FileName, @ContentType, @FileSize, @ContentStream, @UserID;
-- @ContentStream 為 varbinary(max) 參數，驅動程式自動串流
UPDATE dbo.Documents
SET Content = @NewContent, FileSize = @NewSize, FileName = @NewName
WHERE DocID = @DocID;
DELETE FROM dbo.Documents WHERE DocID = @DocID;
-- FILESTREAM 垃圾收集器 (背景執行緒) 非同步清理實體檔案
BACKUP DATABASE FS_Demo TO DISK = 'Backup.bak' WITH COMPRESSION;
-- FILESTREAM 資料包含在備份集中，還原時自動重建檔案結構
RESTORE DATABASE FS_Demo FROM DISK = 'Backup.bak' WITH REPLACE, MOVE ...;
DECLARE @Path NVARCHAR(MAX), @TxnContext VARBINARY(MAX);
SELECT
@Path = FileTableRootPath() + '\' + FileTableDirectoryName() + '\' + FileName, -- FileTable 專用
-- 或標準 FILESTREAM:
@Path = GET_FILESTREAM_TRANSACTION_CONTEXT(), -- 取得交易語境
@TxnContext = GET_FILESTREAM_TRANSACTION_CONTEXT()
FROM dbo.Documents
WHERE DocID = @DocID;
using (var txn = new TransactionScope(TransactionScopeOption.RequiresNew))
using (var conn = new SqlConnection(connStr))
{
conn.Open();
// 1. 取得路徑與語境
string path; byte[] ctx;
using (var cmd = new SqlCommand(@"
SELECT GET_FILESTREAM_TRANSACTION_CONTEXT() AS Ctx,
FileTableRootPath() + '\' + FileTableDirectoryName() + '\' + FileName AS Path
FROM dbo.Documents WHERE DocID = @Id", conn))
{
cmd.Parameters.AddWithValue("@Id", docId);
using (var rdr = cmd.ExecuteReader())
{
rdr.Read();
ctx = (byte[])rdr["Ctx"];
path = (string)rdr["Path"];
}
}
// 2. 開啟 SqlFileStream (Win32 CreateFile 透過 NTFS 流)
using (var fs = new SqlFileStream(path, ctx, FileAccess.ReadWrite, FileOptions.SequentialScan, 0))
{
// 讀取
await fs.CopyToAsync(outputStream);
// 或寫入
await inputStream.CopyToAsync(fs);
}
txn.Complete(); // 交易提交，FILESTREAM 檔案生效
}
-- 啟用非交易存取
ALTER DATABASE FS_Demo SET FILESTREAM (NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = 'FS_Demo');
-- 建立 FileTable (自動包含檔案系統中繼資料欄位)
CREATE TABLE dbo.Documents_FT AS FILETABLE
WITH (FILETABLE_DIRECTORY = 'Documents', FILETABLE_COLLATE_FILENAME = database_default);
| 維度 | 建議 | 理由 |
|---|---|---|
| 儲存磁碟 | 獨立 SSD/RAID 10、NTFS 64KB 配置單元 | 大檔案連續 I/O、減少碎片 |
| 資料庫檔案群組 | FILESTREAM 專用檔案群組、隔離磁碟 | 避免與資料/記錄 I/O 爭用 |
| 插入批次 | 應用層串流參數、避免 `OPENROWSET(BULK...)` 大物件入記憶體 | 記憶體穩定、可預測 |
| 垃圾收集 | 監控 `sys.dm_filestream_garbage_collection`、必要時手動 `EXEC sp_filestream_force_garbage_collection` | 避免刪除檔案堆積佔用空間 |
| 備份策略 | 完整備份含 FILESTREAM、記錄備份不含 (僅中繼資料) | 備份大小可控、還原一致性 |
| 防毒軟體排除 | 排除 FILESTREAM 目錄 (`E:\FSData\...`) | 防毒掃描導致 NTFS 流存取衝突、效能驟降 |
-- 1. 新增 FILESTREAM 欄位
ALTER TABLE Docs ADD Content_FS VARBINARY(MAX) FILESTREAM NULL;
-- 2. 分批回填 (每批 1000 筆)
WHILE 1=1
BEGIN
UPDATE TOP (1000) Docs
SET Content_FS = Content_Old
WHERE Content_FS IS NULL AND Content_Old IS NOT NULL;
IF @@ROWCOUNT = 0 BREAK;
END
-- 3. 切換應用、刪除舊欄位 (維護窗口)
FILESTREAM 為 SQL Server 帶來 「資料庫一致性 + 檔案系統效能」 的最佳平衡。關鍵成功因子：三層啟用配置正確、獨立儲存磁碟、應用層串流存取、防毒排除、垃圾收集監控。SQL 2012+ 專案請優先評估 FileTable，獲得更豐富的檔案系統語意與非交易存取能力。
