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:
Case purpose:
Case start situation:
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
);
SELECT *
FROM [SQL_ApplicationServer].[application].[PrintedInvoices]
WHERE [name]='ClientX_Invoice_Y.pdf';
Case solution:
https://docs.microsoft.com/en-us/azure/storage/common/storage-use-azcopy
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 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
exec [Application].[up_LoadInvoiceToAzureBlob]
@OutputFileName='ClientX_Invoice_Y.pdf';
Download here https://azure.microsoft.com/en-us/features/storage-explorer/
© 2022 Kohera
Crafted by
© 2022 Kohera
Crafted by