使用 CLR 辅助在 T-SQL 中复制文件

引言

在我的一生中，我曾多次为如何从本地机器将 SQL 备份从 Server-A 复制到 Server-B 的问题而苦恼。  

由于我与 SQL 服务器位于不同的地点（城市、国家、大陆），这使得情况更加复杂。理想情况下，我可以远程连接到其中一个服务器，然后直接在它们之间复制文件（这是一种不方便的解决方案，但无疑是最有效的）。有时我无法远程连接，但我可以访问共享文件夹，通过这种方式复制文件会导致高网络负载，并且复制方法很慢。当服务器内存设置的最小值和最大值不同，并且服务器上没有可分配的空闲内存时，文件复制还可能导致内存压力问题。  

由于管理工具 (Management Studio) 始终在我面前，对我来说，最方便的解决方案是能够从这里发出文件复制命令，还可以指定单次传输的字节数，以防止内存压力问题。   

上述问题的解决方案是 FileRelay CLR。   您将能够通过 T-SQL 命令在 SQL 服务器之间复制文件。  

 

背景

公共语言运行时 (CLR) 是 Microsoft .NET Framework 的核心，它为所有 .NET Framework 代码提供执行环境。在 CLR 中运行的代码称为托管代码。CLR 提供了程序执行所需的各种函数和服务，包括即时 (JIT) 编译、内存分配和管理、类型安全强制执行、异常处理、线程管理和安全性。

通过 Microsoft SQL Server 中托管的 CLR（称为 CLR 集成），您可以使用托管代码编写存储过程、触发器、用户定义函数、用户定义类型和用户定义聚合。由于托管代码在执行前会被编译为本机代码，因此在某些场景下可以获得显著的性能提升。   

有三个权限集：SAFE、EXTERNAL_ACCESS 和 UNSAFE。  

SQL Server 在托管 CLR 时提供了一个主机级别的安全策略。此策略是始终生效的两个策略级别之下的一个附加策略级别。此策略针对 SQL Server 创建的每个应用程序域进行设置。此策略不适用于 SQL Server 创建 CLR 实例时生效的默认应用程序域。  

• SAFE：只允许内部计算和本地数据访问。SAFE 是最严格的权限集。具有 SAFE 权限的程序集执行的代码无法访问外部系统资源，例如文件、网络、环境变量或注册表。

• EXTERNAL_ACCESS：与 SAFE 权限集类似，但增加了访问外部系统资源（如文件、网络、环境变量和注册表）的能力。  

• UNSAFE：UNSAFE 允许程序集不受限制地访问 SQL Server 内部和外部的资源。从 UNSAFE 程序集内部执行的代码可以调用非托管代码。   

部署 CLR    

您可以通过运行 fast_install_clr.sql.txt 脚本或遵循以下步骤来部署 CLR。  
首先启用 CLR，然后创建程序集即可：    

use [master]
GO
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE; 
GO  
 
CREATE ASSEMBLY [kladna.sql.filerelay]  
FROM 'D:\CLR\kladna.sql.filerelay.dll'
WITH PERMISSION_SET = UNSAFE
GO   

其次创建 CLR 函数

-- Copy File -------------------------
CREATE FUNCTION dbo.clr_filerelay_copy(@instance_name nvarchar(128), @source_file_stream nvarchar(4000), 
   @target_file_stream nvarchar(4000), @target_server_conn_string nvarchar(4000), @stream_copy_batch_mb int)
RETURNS bit
AS
EXTERNAL NAME [kladna.sql.filerelay].[FileRelay].[Copy]
GO
 
-- Write Bytes ------------------------------
CREATE FUNCTION dbo.clr_filerelay_write_bytes(@target_file_stream nvarchar(4000), 
  @position bigint, @bytes varbinary(max), @retry int)
RETURNS bit
AS
EXTERNAL NAME [kladna.sql.filerelay].[FileRelay].[WriteBytes]
GO
 
-- Get Size of a File -------------------------
CREATE FUNCTION dbo.clr_filerelay_get_file_size(@filename nvarchar(4000))
RETURNS bigint
AS
EXTERNAL NAME [kladna.sql.filerelay].[FileRelay].[GetFileSize]
GO
 
-- Get Stat of a copy-instance --------------------
CREATE FUNCTION dbo.clr_filerelay_get_instance_stat(@instance_name nvarchar(128))
RETURNS table (current_step nvarchar(1000), source_file_stream nvarchar(4000), 
  target_file_stream nvarchar(4000), stream_copy_batch_bytes int, 
  source_size bigint, target_start_size bigint, target_size bigint, 
  ellapsed_sec_total int,  ellapsed_sec_reader_total int, ellapsed_sec_reader_last int, 
  ellapsed_sec_writer_total int, ellapsed_sec_writer_last int, estimated_sec_remaining int, 
  percent_completed int, avg_kb_reads_per_sec int, avg_kb_writes_per_sec int, total_bytes_copied bigint)
AS
EXTERNAL NAME [kladna.sql.filerelay].[FileRelay].[GetInstanceStats]
GO
 
-- Get list of Errors -----------------------
CREATE FUNCTION dbo.clr_filerelay_list_errors()
RETURNS table (instance nvarchar(1000), error_msg nvarchar(max))
AS
EXTERNAL NAME [kladna.sql.filerelay].[FileRelay].[ListErrors]
GO
 
-- Clear Errors ------------------------------
CREATE FUNCTION dbo.clr_filerelay_clear_errors()
RETURNS bit
AS
EXTERNAL NAME [kladna.sql.filerelay].[FileRelay].[ClearErrors]
GO 

CLR 函数   

• clr_filerelay_copy  
  此方法执行 Server A 和 Server B 之间的复制本身  
• @instance_name - 复制过程的唯一名称
• @source_file_stream - Server A 上的要复制的文件
• @target_file_stream - Server B 上的复制文件
• @target_server_conn_string - Server B 的 SQL 连接字符串（.NET 格式） @stream_copy_batch_mb - 单个周期传输的字节数（以兆字节为单位）

复制完成时返回 1，复制过程中发生错误时返回 0   

• clr_filerelay_list_errors    
  复制失败时，您可以检查错误原因  
• clr_filerelay_clear_errors
  您可以清除错误以释放一些内存    
•  clr_filerelay_get_instance_stat
  在复制过程中，您可以检查与复制过程相关的一些信息：  
• 剩余估算秒数
• 完成百分比
• 平均每秒读取千字节数
• 平均每秒写入千字节数
• 复制的总字节数    

使用代码

只有当上述 CLR 已部署到两个 SQL 服务器上时，才允许在 SQL 服务器之间进行复制。让我们看看如何复制文件：  

select dbo.clr_filerelay_copy('my_first_test'
			     ,'c:\my_backup_20130121.bak'
			     ,'c:\my_backup_20130121.bak' 
			     ,'Data Source=SQL2\Instance2;Initial Catalog=master;User Id=me;Password=passw;'
			     ,10) 

在不同的查询窗口中，您可以检查统计信息

 select estimated_sec_remaining
	  ,percent_completed
	  ,avg_kb_reads_per_sec
	  ,avg_kb_writes_per_sec
	  ,total_bytes_copied
 from dbo.clr_filerelay_get_instance_stat('my_first_test')   

检查并删除错误：  

select * from dbo.clr_filerelay_list_errors()	
select dbo.clr_filerelay_clear_errors()  

值得关注的方面   

有趣的是，SQL 引擎可以通过文件复制进行操作。我能够以 80MB/秒的速度将文件从 A 移动到 B。