Reviewed by: Rajesh Setlem, Xiaochen Wu
Historically, SQL Server supported two backup destinations, disk and tape. In SQL Server 2012, a new destination named URL was added. With this destination, SQL Server backs up a database to an Azure Blob Storage container and blob specified in the URL. In SQL Server 2016, Backup to URL was significantly improved, and added support for block blobs in addition to page blobs supported earlier. As customers migrate their SQL Server workloads to Azure IaaS VMs or to Managed Instance, Backup to URL is becoming a very common (and in the case of Managed Instance, the only) way to back up databases.
At the same time, customers are sometimes confused by the differences between the two types of Backup to URL that are available (page blobs and block blobs). The table below describes these differences as a quick summary reference, to help customers choose the right type of Backup to URL for their scenario.
|
Page blobs (old)
|
Block blobs (new and preferred)
|
|
SQL Server 2012 SP1 CU2 or later
|
SQL Server 2016 or later
|
|
Single blob, up to 1 TB
|
Up to 64 blobs (stripes), up to ~195 GB each
|
|
Target throughput: 60 MB/s
|
Target throughput: (60 MB/s * number of blobs)
|
|
Maximum backup size: 1 TB
|
Maximum backup size: ~12 TB (with 64 stripes)
|
|
Credential holds storage account key
|
Credential holds SAS token
|
|
Credential name is arbitrary
|
Credential name is the container URL
|
|
Syntax: BACKUP/RESTORE … TO/FROM URL = '…' WITH CREDENTIAL = '...';
|
Syntax: BACKUP/RESTORE … TO/FROM URL = '…';
|
Further details about Backup to URL are available in documentation.
Based on our work with customers using Backup to URL, we can also mention several recommendations:
|
Recommendation
|
Explanation
|
|
Use a well-formed SAS token
|
The most common issue is including the leading ‘?’ in the token string. This must be removed. Other common problems are:
· expired or not yet valid token
· insufficient permissions (Read/Write/Delete/List permissions are required for both backup and restore)
· an overly restrictive IP address filter
|
|
Use Standard Storage
|
Premium Storage is not supported in Backup to URL.
|
|
Use backup compression
|
Compression improves backup/restore throughput and reduces backup size. Make sure to apply patches if using TDE with backup compression.
|
|
If using block blobs, stripe the backup over multiple blobs
|
This improves backup/restore throughput, and is required to back up larger databases.
|
|
If using block blobs, use MAXTRANSFERSIZE = 4194304 and BLOCKSIZE = 65536
|
This is required for larger databases to avoid the ~195 GB per blob limit, and achieves better backup compression ratio.
|
|
Use a container-level SAS token vs. account-level SAS token
|
Do this to follow the principle of least privilege. Azure Storage Explorer can be used to create container-level SAS tokens.
|
Have a question about Backup to URL not covered in this article or in documentation? Please post a comment.
