The SQL Server hybrid backup to URL feature offers flexibility to customers. It provides an additional layer of fault tolerance for your valuable backup files and stores them on the cloud.
The Azure storage is a cost-effective option to store your on-premises SQL backups securely & natively out of the box. In addition to that, you also can directly save the data & log files on Azure storage blobs. To take this a step further, you can provision your database replicas on Azure using the Always-On availability groups. See the image below:
The second and third options are out of scope for today’s discussion. We are only going to talk about taking SQL Server database backups on an Azure Storage account. So, if you are interested in a step by step explanation, read along to know more.
SQL Server Hybrid Backup to URL Steps:
Backup and restore is one of the most basic administrator tasks for DBA’s managing databases. With SQL Server 2012 SP1, CU2 onwards, you can safely back up your databases (*.back) in the Azure cloud.
You can also optionally manage your backup scheduling tasks by specifying a data retention plan. Also, SQL Server managed backup to Azure fully supports custom scheduled backup jobs. See the T-SQL command below:
BACKUP database TO URL = ‘https://<storageaccount>.blob.core.windows.net/<container>/<blob>’ WITH CREDENTIAL = ‘credential_name’
Thankfully, it is not 2012 anymore, and the SQL Server Management Studio GUI now fully supports this process without using any T-SQL code.
With the release of SQL Server 2012 SP1 – CU2, Microsoft had added the ability to backup to Azure using the TO URL command. This enabled us to back up to Azure Blob Storage, which is nearly limitless at a meager cost. Backup to the cloud offers benefits such as availability, almost unlimited geo-replicated offsite storage, no device management needed, and ease of data migration to and from the cloud.
Even for instances of SQL Server running in Microsoft Azure VM’s, backing up to Azure Blob Storage cloud services can be done by creating attached disks. However, there is a hard limit to the number of drives that you can attach to an Azure virtual machine.
This limit is 16 disks for an extra large instance and fewer for smaller instances. Also, there are other minor limitations with this implementation:
- The max backup size supported is only 1 TB (till SQL Server 2014)
- Restore speed over slow WAN networks can be a problem.
- Specifying backup set options (RETAINDAYS and EXPIREDATE) is not supported.
- Backup to Premium storage is not supported
Benefits are as follows:
- Near “bottomless” storage
- Off-site, geo-redundant
- No provisioning, decay-free
- No device management
- Remote accessibility
Backup to URL Improvements post SQL Server 2016 release:
This service, which was called Backup to URL in previous versions, has been enhanced with SQL Server 2016/2017/2019.
- The process has been fully incorporated into the SQL Server database engine, and the type of blob storage used has been altered from 3-copy page blobs to considerably less expensive parity-based ‘block blobs.’
- Block blobs are less costly per month than page blobs.
- Block blobs can support up to 200 GB, and the backups can be striped across multiple blobs. As a result, this increases the size limit from 1 TB (page) to 12.8 TB(blocks)!
- Block blobs provide more granular access and a unified credential story through the use of SAS (Shared Access Signatures).
- You have more granular control of the backup schedule for both full & log file backups.
- Block blobs support all existing backup and restore features except the ability to append.
- Appending to existing backup blobs is currently not supported.
The T-SQL syntax for this backup to URL is also relatively simple. You need to create a Shared Access Signature, backup the database, and log to the destination Azure block blobs.
So, with this in mind, let us look at some of the steps needed to backup your on-premises databases on the cloud.
SQL Server Hybrid Backup to URL – Step by Step
The backup steps are similar to how you would do this locally on a storage array. Only in the second step, you will have the ability to choose a cloud vs. local backup option. See below:
- Select the SQL DB which you want to backup. Right click, Select Tasks > Back Up
- Under “Backup Type” option, ensure “Full” backup for the initial step
- Under “Destination,” Back Up to, click on the dropdown menu item and choose “URL.”
- Next, click on “Add” and a new pop-up opens “Create Credential” to create an access token.
- If you already have a SAS token generated, type in the blob storage URL and select the SAS key. If this is your first time, click on the “Container” button, and SAS expiration date. Then click on “Create.”
- To this, click on “Sign in” and type in your “Azure Subscription Login” credentials.
- Select the target storage account and a container under it for security reasons. Do note that you will have to provision your storage account before this.
- Once ready, type in a backup file name, in case if you do not like the default naming system.
Here are the screenshots for the steps mentioned above:
We hope this article helped you to set up the SQL Server Hybrid Backup to URL feature on your environment. We welcome your questions and suggestions in the comments section below. Thanks for taking the time to visit us, have a wonderful day ahead.