Dan

Dan Evans' Blog

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

Outbound data transfers

Zone 1*

Zone 2*

Zone 3*

First 5 GB/Month 1

Free

Free

Free

5 GB – 10.0 TB 2/month

£0.0532 per GB

£0.0844 per GB

£0.1106 per GB

Next 40 TB
(10-50 TB)/month

£0.0508 per GB

£0.0825 per GB

£0.107 per GB

Next 100 TB
(50-150 TB)/month

£0.0428 per GB

£0.0795 per GB

£0.1039 per GB

Next 350 TB
(150-500 TB)/month

£0.0306 per GB

£0.0734 per GB

£0.0978 per GB

Over 500 TB/month

Contact Microsoft

Contact Microsoft

Contact Microsoft

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

There is a charge of £0.0022 per 100,000 transactions for Block Blobs. Transactions include both read and write operations to storage.

Storage Capacity

LRS

ZRS

GRS

RA-GRS

First 1 TB/month

£0.0147 per GB

£0.0184 per GB

£0.0294 per GB

£0.0373 per GB

Next 49 TB (1 to 50 TB)/month

£0.0145 per GB

£0.0181 per GB

£0.0289 per GB

£0.0366 per GB

Next 450 TB (50 to 500 TB)/month

£0.0142 per GB

£0.0178 per GB

£0.0284 per GB

£0.036 per GB

Next 500 TB (500 to 1,000 TB)/month

£0.014 per GB

£0.0175 per GB

£0.0279 per GB

£0.0354 per GB

Next 4,000 TB (1,000 to 5,000 TB)/month

£0.0137 per GB

£0.0172 per GB

£0.0274 per GB

£0.0348 per GB

Over 5,000 TB/month

Contact Microsoft

Contact Microsoft

Contact Microsoft

Contact Microsoft


Page Blobs and Disks

Storage Capacity

LRS

GRS

RA-GRS

First 1 TB/month

£0.0306 per GB

£0.0581 per GB

£0.0734 per GB

Next 49 TB (1 to 50 TB)/month

£0.0306 per GB

£0.0489 per GB

£0.0611 per GB

Next 450 TB (50 to 500 TB)/month

£0.0306 per GB

£0.0428 per GB

£0.055 per GB

Next 500 TB (500 to 1,000 TB)/month

£0.0306 per GB

£0.0398 per GB

£0.0489 per GB

Next 4,000 TB (1,000 to 5,000 TB)/month

£0.0275 per GB

£0.0367 per GB

£0.0459 per GB

Over 5,000 TB/month

Contact Microsoft

Contact Microsoft

Contact Microsoft

 

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.

Loading