如何以最小性能影响将SQL Server中BLOB数据迁移至FILESTREAM列？

现有存储BLOB数据的表bigData，结构如下：

CREATE TABLE bigData
(
    Id INT NOT NULL PRIMARY KEY,
    data VARBINARY(MAX) NOT NULL,
    dataName NVARCHAR(MAX) NOT NULL
);

已添加FILESTREAM特性所需列：

ALTER TABLE bigData
    ADD Guid UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE DEFAULT NEWSEQUENTIALID();

ALTER TABLE bigData
    ADD dataFileStream VARBINARY(MAX) FILESTREAM NULL;

表包含15万+行（约180GB数据），直接执行UPDATE会导致过高数据库负载，新建表迁移方案不可行。目标：

• 以最小性能影响将data列数据迁移至dataFileStream列
• 迁移完成后删除data列

曾考虑BCP工具，但因需针对整表/视图操作，流程复杂，寻求资源占用更低的BLOB迁移方法。

1. 分批次批量更新 #

通过循环分批处理数据，控制每次更新的行数，避免一次性锁定大量资源：

DECLARE @BatchSize INT = 1000;
DECLARE @LastId INT = 0;

WHILE EXISTS (SELECT 1 FROM bigData WHERE Id > @LastId AND dataFileStream IS NULL)
BEGIN
    UPDATE TOP (@BatchSize) bigData
    SET dataFileStream = data
    WHERE Id > @LastId AND dataFileStream IS NULL;

    SET @LastId = (SELECT MAX(Id) FROM bigData WHERE dataFileStream IS NOT NULL);
    
    -- 可选：添加延迟降低瞬时负载，根据业务负载调整时长
    WAITFOR DELAY '00:00:01';
END

• 优势：无需额外工具，逻辑简单，可通过调整@BatchSize和延迟时间灵活控制负载
• 注意：若主键Id非连续递增，可改用ROW_NUMBER()进行分批处理

2. OPENROWSET+BULK逐行导出导入 #

利用OPENROWSET(BULK...)将data列的BLOB导出到临时文件，再导入到dataFileStream列，每次仅处理一行数据：

-- 先创建临时存储目录，需确保SQL Server服务账户拥有该目录的读写权限
DECLARE @Id INT, @FilePath NVARCHAR(500);
DECLARE cur CURSOR FAST_FORWARD FOR
SELECT Id FROM bigData WHERE dataFileStream IS NULL;

OPEN cur;
FETCH NEXT FROM cur INTO @Id;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @FilePath = N'C:\BlobTemp\data_' + CAST(@Id AS NVARCHAR(10)) + '.bin';
    
    -- 导出data列数据到临时文件
    INSERT INTO OPENROWSET(BULK @FilePath, SINGLE_BLOB)
    SELECT data FROM bigData WHERE Id = @Id;
    
    -- 从临时文件导入数据到dataFileStream列
    UPDATE bigData
    SET dataFileStream = (SELECT * FROM OPENROWSET(BULK @FilePath, SINGLE_BLOB) AS B)
    WHERE Id = @Id;
    
    -- 删除临时文件
    EXEC xp_cmdshell 'DEL "' + @FilePath + '"', NO_OUTPUT;
    
    FETCH NEXT FROM cur INTO @Id;
END

CLOSE cur;
DEALLOCATE cur;

• 优势：单条数据处理模式，资源占用极低，适合超大规模BLOB迁移
• 注意：需提前开启xp_cmdshell权限，临时目录权限配置需正确

3. SSIS包分批迁移 #

通过SQL Server Integration Services（SSIS）创建低负载迁移任务：

• 配置数据源为bigData表，设置分批读取规则（如每次读取1000行）
• 添加数据转换组件，将data列值映射到dataFileStream列
• 配置目标表为bigData，启用批量更新模式
• 可设置任务在业务低峰时段执行，进一步降低对业务的影响

1. 数据一致性验证：对比两列的哈希值，确保数据无丢失或损坏：

SELECT Id, 
       HASHBYTES('SHA2_256', data) AS DataHash,
       HASHBYTES('SHA2_256', dataFileStream) AS StreamHash
FROM bigData
WHERE HASHBYTES('SHA2_256', data) <> HASHBYTES('SHA2_256', dataFileStream);

若返回空结果，说明数据完全一致。

2. 删除原data列：

ALTER TABLE bigData
DROP COLUMN data;

内容的提问来源于stack exchange，提问作者cvika7