How to migrate data to blob storage from local server using PowerShell

How to migrate data to blob storage from local server using PowerShell

There are so many tools to migrate the data from your server to Azure blob storage. The tools may not work as you want. Here is the way to minimize your third-party cost and process your data in an effective way. Now, we are uploading CSV files from the folder recursively i.e. file inside the sub-folder too.

To optimize the number of parallel operations for a load, Snowflake recommends producing data files roughly 10 MB to 100 MB in size compressed. Aggregate smaller files to minimize the processing overhead for each file.

Preparing your Data Files

If you wish to send the file to blob storage using PowerShell, here are the easy simple steps to do so.

Create a storage Account

If you want to create a new Resource Group in Azure you can do that by going to the Azure Portal.

In the newly created Resource Group (anil-test-resource-group), click new and select storage account as per the below image.

Under the resource group, click new and select storage account as per the below image.

 

Storage Container

Go inside the storage account that you have recently created click on the container and give the appropriate name.

Go inside the storage account that you have recently created click on the container and give the appropriate name.

To get access to the currently created container, I am using Azure Storage Explorer. Right-click on blob container and click on "Get Shared Access Signature". Select expiry time and permissions. 

Install the Azure module in PowerShell

Copy the following code and run on PowerShell. You can change the currentUser to AllUser.

Install-Module -Name Az -AllowClobber -Scope CurrentUser

Here is the simple code to upload CSV file in Azure blob storage which can be run in poweshell.

$path="D:\Temp\test"
$include = "*.CSV"
$exclude = ""
$BlobURL="https://anillteststorageaccount.blob.core.windows.net/importfile/"
$AccessKey="?sv=2019-02-02&st=2020-08-19T23%3A42%3A01Z&se=2020-09-XXXXXXXXXX&sr=c&sp=rcwl&sig=XXXXXXXXXXXXXXXXXXXXXXXXXX"
Get-ChildItem -re -in $include -ex $exclude $path |
Foreach-Object {
  $content = Get-Content $_.FullName
  $file = $_;
  #get file name only
  $name = (Get-Item $file).Name
  $uri = $BlobURL+$blobFolder+($name)+$AccessKey
  $uri = $BlobURL+($name)+$AccessKey  
  #Required headers for blob
  $headers = @{ 'x-ms-blob-type' = 'BlockBlob' }
  #Upload File to blob storage...
  Invoke-RestMethod -Uri $uri -Method Put -Headers $headers -InFile $file
}

Once executes, you can see the results in the blob container by browsing or refresh the container

If you compress the file, the compression ratio on file is very good. I have tested 430MB of a CSV file and the file is compressed to 14MB.

You can find a good description on the Microsoft website

https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.archive/compress-archive?view=powershell-7

You can delete or move the file to the archive folder using the following simple code.

Delete:

Remove-Item -Path $directoryWithName  -Force

Move:

Move-Item -Path $directoryWithName -Destination  $Success_DirectorywithName -Force

Without Force, you cannot delete or move read-only or hidden files.

Automate your job

Here I am forcing executable as PowerShell 7, you can execute your PowerShell script directly in the SQL Server Agent job.

 

Schedule the PowerShell script on SQL Server Agent Job and schedule your work to automate