Binary Copy files in T-SQL with helps of CLR

Introduction

Many times in my life I struggled with the problem how to copy a SQL backup from Server-A to Server-B from my local machine.  

The situation is complicated by the fact that I'm placed in different location (city, country, continent) like the SQL Servers. Ideally I can remote to one of the servers and copy the files directly between them (it's a comfortless solution, but without a doubt the most efficient). Sometimes I can't remote but I can access the shared folders, copying the files in this way cause high network load and the copy method is slow. Also can be problem the memory pressure caused by the file copy when the min. and max. server memory settings are different and there is no free memory allocable on the server. 

Since the Management Studio is always in front of me, for me, is the most convenient solution would be if I  could request a file copy command from here also specifying the size of bytes transferred in a single cycle to prevent the memory pressure issue.  

The solution to the above problems is FileRelay CLR.  You will able to copy files between SQL servers by T-SQL command. 

Background

The common language runtime (CLR) is the heart of the Microsoft .NET Framework and provides the execution environment for all .NET Framework code. Code that runs within the CLR is referred to as managed code. The CLR provides various functions and services required for program execution, including just-in-time (JIT) compilation, allocating and managing memory, enforcing type safety, exception handling, thread management, and security.

With the CLR hosted in Microsoft SQL Server (called CLR integration), you can author stored procedures, triggers, user-defined functions, user-defined types, and user-defined aggregates in managed code. Because managed code compiles to native code prior to execution, you can achieve significant performance increases in some scenarios.  

There are three permission sets: SAFE, EXTERNAL_ACCESS and UNSAFE. 

SQL Server supplies a host-level security policy level to the CLR while hosting it; this policy is an additional policy level below the two policy levels that are always in effect. This policy is set for every application domain that SQL Server creates. This policy is not meant for the default application domain that would be in effect when SQL Server creates an instance of the CLR. 

• SAFE: Only internal computation and local data access are allowed. SAFE is the most restrictive permission set. Code executed by an assembly with SAFE permissions cannot access external system resources such as files, the network, environment variables, or the registry.

• EXTERNAL_ACCESS: Like the SAFE permission set, but with the additional ability to access external system resources such as files, networks, environmental variables, and the registry. 

• UNSAFE: UNSAFE allows assemblies unrestricted access to resources, both within and outside SQL Server. Code executing from within an UNSAFE assembly can call unmanaged code.  

Deploying the CLR   

You can deploy the CLR by running the fast_install_clr.sql.txt script or following the below steps. 
First enable the CLR, then simply create the assembly:    

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   

Second create CLR functions:

-- 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 functions  

• clr_filerelay_copy 
  This method perform the copy itself between Server A and B  
• @instance_name - unique name of a copy process
• @source_file_stream - file to copy on Server A
• @target_file_stream - copied file on Server B
• @target_server_conn_string - SQL connection string (.NET formatted) of Server B @stream_copy_batch_mb - Bytes to transfer in Mega Bytes in a singe cycle

Return value 1 when the copy is done, 0 when an error happens during the copy   

• clr_filerelay_list_errors   
  You may check the reason of the error when the copy has failed  
• clr_filerelay_clear_errors
  You can clear the errors to free up some memory   
•  clr_filerelay_get_instance_stat
  During the copy you are able to check some information related to the copy process: 
• estimated seconds remaining
• percent completed
• average kilobytes reads per second
• average kilobytes writes per second
• total bytes copied   

Using the code    

Copy between SQL servers are allowed just if the above CLR was deployed on both of SQL servers. Let's see how we can copy files: 

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) 

In a different query window you may check the stats:

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')

Check and delete the errors: 

SQL

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

Points of Interest   

Interesting to see that SQL engine may operate through a file copy. I was able to move a file from A to B with 80.MB/sec.