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.
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:
This service, which was called Backup to URL in previous versions, has been enhanced with SQL Server 2016/2017/2019.
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.
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:
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.
Comments are closed.