SQL Backup using Azure File Share
Published Aug 10 2022 12:40 PM 5,105 Views
Microsoft

SQL Server limits the maximum backup size supported using a page blob to 1 TB. The maximum backup size supported using block blobs is limited to approximately 200 GB (50,000 blocks * 4 MB MAXTRANSFERSIZE).

In order to get over the above limitation, we can opt for Azure File Share.


SQL Backup is more than 12TiB, you can choose to backup using Azure File Share

 

Standard file shares can span up to 100 TiB, however this feature is not enabled by default. If you need a file share that is larger than 5 TiB, you will need to enable the large file share feature for your storage account.

 

Premium file shares can span up to 100 TiB without any special setting, however premium file shares are provisioned, rather than pay as you go like standard file shares. This means that provisioning file share much larger than what you need will increase the total cost of storage.

 

In local and zone redundant storage accounts, Azure file shares can span up to 100 TiB, however in geo- and geo-zone redundant storage accounts, Azure file shares can span only up to 5 TiB.

Prerequisites

Steps

1. Storage Account with Premium FileShare

 

SaniyaSamreen_0-1660158593869.png

Enable Large File Share

 

SaniyaSamreen_1-1660158593879.png

 

2. Register for the SMB Multichannel preview with the following commands.

Connect-AzAccount

# Setting your active subscription to the one you want to register for the preview.

# Replace the <subscription-id> placeholder with your subscription id.

$context = Get-AzSubscription -SubscriptionId <your-subscription-id>

Set-AzContext $context

 

Register-AzProviderFeature -FeatureName AllowSMBMultichannel -ProviderNamespace Microsoft.Storage

Register-AzResourceProvider -ProviderNamespace Microsoft.Storage

 

You can also verify if the feature registration is complete

Get-AzProviderFeature -FeatureName AllowSMBMultichannel -ProviderNamespace       Microsoft.Storage

 

SaniyaSamreen_2-1660158593880.png

 

3. Enable SMB Multichannel

    Once you have created a File Storage account, you can follow the instructions to update SMB Multichannel settings for your storage          account

     SaniyaSamreen_3-1660158593894.png

 

 

Note: If the SMB Multichannel option is not visible under File share settings or you get a failed to update setting error while updating the configuration, please make sure that your subscription is registered, and your account is in one of the supported regions with supported account type and replication.

 

4. Create a file share

 

SaniyaSamreen_4-1660158593907.png

 

You can set max capacity up to 100TB

SaniyaSamreen_5-1660158593909.png

 

5. Connect to FileShare from Window either using Active Directory or Storage Account

Connecting to a share using the storage account key is only appropriate for admin access. But mounting the Azure file share with the Active Directory identity of the user is preferred.

SaniyaSamreen_6-1660158593922.png

 

$connectTestResult = Test-NetConnection -ComputerName testsmbfileshare.file.core.windows.net -Port 445

if ($connectTestResult.TcpTestSucceeded) {

    # Save the password so the drive will persist on reboot

    cmd.exe /C "cmdkey /add:`"testsmbfileshare.file.core.windows.net`" /user:`"localhost\testsmbfileshare`" /pass:`"SxbRsNuwc1*******/8lk1TyUkqC+2+************==`""

    # Mount the drive

    New-PSDrive -Name Z -PSProvider FileSystem -Root "\\testsmbfileshare.file.core.windows.net\sqlbackup" -Persist

} else {

    Write-Error -Message "Unable to reach the Azure storage account via port 445. Check to make sure your organization or ISP is not blocking port 445, or use Azure P2S VPN, Azure S2S VPN, or Express Route to tunnel SMB traffic over a different port."

}

 

Copy this script and run this in PowerShell to map this as a network drive locally.

This script will check to see if this storage account is accessible via TCP port 445, which is the port SMB uses. If port 445 is available, your Azure file share will be persistently mounted.

 

Note: The script will only work on Windows Server 2012 and above

SaniyaSamreen_7-1660158593924.png

 

Once we had the above script is executed, we could see the Z drive as network Drive in My computer / This PC

SaniyaSamreen_8-1660158593941.png

 

6. On the SQL Server you need to first enable XP_cmdshell so we can configure backups to this file share.

        Enable the Advance SQL Configuration

        EXECUTE sp_configure 'show advanced options', 1; 

        GO 

        -- To update the currently configured value for advanced options. 

        RECONFIGURE; 

        GO 

        -- To enable the feature. 

        EXECUTE sp_configure 'xp_cmdshell', 1; 

        GO 

        -- To update the currently configured value for this feature. 

        RECONFIGURE; 

        GO

 

We must mount the Z Drive in SQL server and opt for it to be available for backups. Therefore, we map it using the script below

 

xp_cmdshell 'net use Z: \\testsmbfileshare.file.core.windows.net\sqlbackup /u:localhost\testsmbfileshare SxbRsNuwc1*******/8lk1TyUkqC+2+************==`'

 

Get the storage account, username and access key from Step 5

 

SaniyaSamreen_3-1660159787463.png

 

SaniyaSamreen_10-1660158593948.png

 

7. Backup the database now to the file share subdirectory using the below command

    BACKUP DATABASE [AdventureWorks2019] TO DISK = 'Z:\AdventureWorks2019.bak' with stats = 5

    

SaniyaSamreen_0-1660159398187.png

 

 

Reference (optional)

SMB file shares in Azure Files | Microsoft Docs

Create an Azure file share - Azure Files | Microsoft Docs

https://technet.microsoft.com/en-us/library/dn435916(v=sql.120).aspx#limitations


 

 

 

 

 

2 Comments
Version history
Last update:
‎Aug 15 2022 07:15 AM
Updated by: