SQL Server Backup and Restore with Microsoft Azure Blob Storage Service

This Blog is part 1 of a 3 blog series concerning backing up your SQL Server databases to Azure Cloud Storage. This first blog will provide a summary of the options currently available to you.

The reason to leverage Azure for your SQL Server backups is obvious. It is a far better alternative to tape backups. It eliminates the need for offsite transportation and makes high availability of the backups easier to obtain. In addition to this there is no hardware management overhead. Other things that should be factored are the cost (only pay for what you use) and the increased accessibility of the backups themselves.

When 2016 is released, there will be 3 supported methods of storing SQL Server backups in Azure Blob storage. The first two below can be done from on premise SQL Server instances but the third requires the instance to be running in an Azure VM:

Backup to URL

Done in the same way as backups have been done before to disk (or tape). This became available in SQL Server 2012 SP1 CU2. From 2016 onwards you will be able to use block blobs instead of page blobs. This method can be leveraged using the SSIS backup task.

SQL Server Managed Backups

A way to let SQL Server manage backups, negating the need to form your own strategies. This feature also allows you to set retention periods and works at a database level or at instance level.

Snapshot Backups for Database Files

Coming with SQL Server 2016 and provides near instantaneous backups and restores with point in time restore. The database files themselves need to be directly in Azure Storage as well as the instance being in an Azure VM.

Here are a few positives and negatives about each option:

· Backup to URL

o + Can be done during an SSIS load

o + Can be executed whenever you wish

o – Currently no way to maintain the backups from within SSIS natively

· SQL Server Managed Backups

o + Fully managed by SQL Server

o + Can have custom schedules

o + Maintains backups (retention period)

o – Always invoked as part of a schedule or under certain conditions

· Snapshot Backups

o + Very fast

o – Requires database files to be situated in Azure Blobs

o – Requires instance to be on an Azure VM – not an on-prem solution

The first two options I will explore in parts 2 and 3. However I will not go into more detail about database file snapshot backups as I will stick to on premise solutions.

Costs

Costs in this section are correct at the time of writing (2015/10/28). For updated costs check the links.

Data Transfer

The following information has been lifted directly with minor alterations from: https://azure.microsoft.com/en-gb/pricing/details/data-transfers/

Inbound data transfers

There is no cost for transferring data into Azure.

Outbound data transfers

A sub-region is the lowest level geo-location that you may select to deploy your applications and associated data. For data transfers (except CDN), the following regions correspond to Zone 1, Zone 2 and Zone 3.

Zone 1: US West, US East, US North Central, US South Central, US East 2, US Central, Europe West, Europe North

Zone 2: Asia Pacific East, Asia Pacific Southeast, Japan East, Japan West, Australia East, Australia Southeast

Zone 3: Brazil South

As an example you have a Blob Storage account in West Europe (Zone 1). In this you have a backup file for a database that is approximately 9GB in size. You begin a restore of that data and pull it from azure to a local disk.

The first 5GB of this file are free. The remaining 4 GB falls into the £0.0508 per GB tariff. The total cost of transferring the file out of Azure would amount to £0.2032, assuming you only ever did this once per month.

Standard Storage

Block blobs

Page Blobs and Disks

This concludes the first overview blog. Hopefully this has given you a rough idea of the options available to you for backing up to Azure Storage. The next two blogs coming shortly will focus on Managed backups and SSIS backups to URL in a more detail, showing how to configure and use them.