Backup On-Prem SQL Databases to Azure

Azure storage, as a backup destination for SQL backups, is a great option for organizations that are contemplating replacing older on-prem NAS appliances or improve their Disaster Recovery functionality. The tiered storage pricing, along with local and global redundancy options, can be much more cost-effective than many traditional backup options.

In this post, we’re going to look at some of the key concepts and restrictions, along with how to back up an SQL database to an Azure storage location.

Key Concepts

Azure Portal
The Azure Portal (https://portal.azure.com) is a unified interface for managing all of the Azure-based services (authentication, storage, virtual servers, etc.).

Azure Storage Account
An Azure Portal can contain one, or many, storage accounts. Each storage account can contain many data objects (files, blobs, tables, disks, etc.). John Morehouse has a good blog post on Creating A Storage Account, that explains how to create a new Azure storage location, along with details about what options are available (location, performance & replication).

Storage Container
Containers are logical structures within an Azure storage account that are used to organize the different types of data objects. (Essentially, they can be thought of as similar to folders on a disk)

Blobs
SQL database backups are stored as unstructured data (blobs) within a storage container.

Restrictions

Multiple Backup Devices
(ie. splitting the backup into multiple files)
SQL Server 2012 – 2014 (11.x to 12.x) only support a single device when backing up to a URL.
SQL Server 2016 – 2017 (13.x to 14.x) support multiple backup devices using Shared Access Signature (SAS) tokens.

Appending Backups
When backing up a database to a URL, the backup cannot be appended to an existing backup set (blob). The backup can only be written to a new blob, or overwrite an existing blob using the WITH FORMAT option.

Backup Expiration
The Backup statement option to expire a backup set on a specific date (EXPIREDATE), or after a specified number of days (RETAINDAYS), is not supported when backing up to a URL.

Blocksize and MaxTransferSize
Currently, SQL Server 2012-2017 do not support using the BLOCKSIZE and MAXTRANSFERSIZE arguments when backing up a database to a URL.

How to backup to Azure

There are a few pieces of information (for each storage location) that are required to be able to integrate Azure storage into a SQL backup process: the storage account name, the container name, the access key, the URL, and a SQL Credential.

Access key
The access key can be found in the Access keys section of the Settings for each Storage Account in the Azure portal.

Container
A container can be created in the Blobs menu of the Blob service section (below the Access keys section) of each storage account.

URL
The syntax for an Azure backup URL is:
https://<storage account name>.blob.core.windows.net/<container name>/<blob name>

SQL Credential
The storage account name and Access key must be incorporated into a SQL Credential (on each SQL Server instance), which is then included in the SQL backup statement. A SQL Credential can be created using T-SQL or through the SQL Server Management Studio (SSMS) interface.
T-SQL

IF NOT EXISTS  
   (SELECT * 
    FROM sys.credentials   
    WHERE credential_identity = ‘credential name’)  
CREATE CREDENTIAL [credential name] WITH IDENTITY = ‘storage account name’, SECRET = ‘access key’ ; 

SSMS
To create a SQL credential using SSMS, expand the database engine instance, then right click on Security, select New, and then select Credential. Use the Azure storage account name for Identity and the Access key in the Password field.

SQL Backup Statement
Once the SQL Credential has been created, and the URL determined, they can be incorporated into a SQL backup statement.

BACKUP DATABASE [model]
TO URL = 'https://jwsqltest.blob.core.windows.net/sqltest/model_2019-04-15_Full.bak'
WITH CREDENTIAL = 'JW-AzureSQLTest',
COMPRESSION, STATS = 5;

When the backup has completed, the backup file will appear in the Blobs container of the storage account.