Store documents in Azure Blob storage using SQL

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/