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/
