Store documents in Azure Blob storage using SQL

One little step in a migration track from on-premise to Azure is related to binary files like images, pdf and others. These files are often stored on many different places: SharePoint, file shares, local folders, SQL file tables, SQL tables as binary objects, etc. Using the last 2 options – files stored in SQL – you probably have already created a variety of SQL procedures to maintain and using these binary files. Scenario’s where you dynamically generate PDF or other client related files, are using these techniques. When using Azure blob-storage as well in your platform, you can move these SQL stored files easily to Azure with a little change in your SQL procedures.

In the next example, I show you how to move a PDF file stored in an SQL file-table to an Azure blob-container. There are multiple ways of doing this, this is just one of them.

Case description:

• On-premise SQL server 2017 containing file-table, configured for non-transactional access
• Azure blob-storage-container
• PDF file generated by business-application and stored in the file-table

Case purpose:

• Store PDF file in Azure blob-storage in a HYBRID-cloud infrastructure solution
• Use T-SQL to make it happen

Case start situation:

• Existing file-table:

CREATE TABLE [application].[PrintedInvoices] 
    AS FILETABLE ON [PRIMARY] FILESTREAM_ON [SQL_ApplicationServer_FG_FS]
WITH
(
  FILETABLE_DIRECTORY = N'Application_PrintedInvoices'
, FILETABLE_COLLATE_FILENAME = Latin1_General_100_CI_AS_KS_WS
);

• File accessible in OS:

• Or Query using T-SQL:

SELECT *
FROM [SQL_ApplicationServer].[application].[PrintedInvoices]
WHERE [name]='ClientX_Invoice_Y.pdf';

• Define your blob-storage in Azure (hot or cold storage)

• Check your access keys:

Case solution:

• Install AZcopy toolbox on your server. This is a command line tool to interact with your Azure blob-storage.

https://docs.microsoft.com/en-us/azure/storage/common/storage-use-azcopy

• At you SQL server database, configure xp_cmdshell access:

EXEC sp_configure 'show advanced options', 1; 
GO 
RECONFIGURE; 
GO
EXEC sp_configure 'xp_cmdshell', 1; 
GO 
RECONFIGURE; 
GO 
--give access:
sp_xp_cmdshell_proxy_account [ NULL | { 'account_name' , 'password' } ] 

• Create a new stored procedure for creating the AZcopy command line statement and executes it.

create procedure [Application].[up_LoadInvoiceToAzureBlob]
@OutputFileName varchar(512)
as
begin

declare @cmd nvarchar(4000);

select @cmd= '"C:\Program Files (x86)\Microsoft SDKs\Azure\AzCopy\azcopy.exe" '
      +' /Source:'+FileTableRootPath(N'[Application].[PrintedInvoices]')
	  +' /Dest:https://storageaccount.blob.core.windows.net/virtualfolder/'
	  +' /DestKey: '
	  +' /Pattern:'+@OutputFileName + ' /Y'
;
DECLARE @result int;  
EXEC @result = master..xp_cmdshell @cmd;  

IF (@result = 0)  
begin
    PRINT 'Success'  
	-- or do something else
end
ELSE
begin  
   PRINT 'Failure';  
   -- or do something else
end

end

• Execute procedure like this:

exec [Application].[up_LoadInvoiceToAzureBlob] 
@OutputFileName='ClientX_Invoice_Y.pdf'; 

• Visual verifying the result, can be done using for example Azure storage explorer

Download here https://azure.microsoft.com/en-us/features/storage-explorer/