SQL Server Backup to URL – a cheat sheet

Reviewed by: Rajesh Setlem, Xiaochen Wu


 


Historically, SQL Server supported two backup destinations, disk and tape. In SQL Server 2014, 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.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.