How to Backup SQL Server Directly to Azure Storage

From SQL 2012 SP1 CU2 upwards we have the ability to backup directly to Windows Azure Storage.  So what are the main benefits of doing this and why bother ?

Here are some reasons why it is an important feature, and one that should be considered whether your SQL Server is hosted in Windows Azure or anywhere else.

  • Out of the box functionality to store your SQL Data offsite for DR Purposes with no additional software or agents required.
  • Very simple to get up and running.
  • A good low cost strategy for offsite data backups for DR. Data is replicated in triplicate for high availability within the data centre in which the storage account resides.  Storage accounts can also be created to be geo replicated across data centre regions also.
  • Microsoft provide a guaranteed 99.9% SLA on access to the data to ensure it is available when you need it.
  • Only pay for what is used.
  • Leverage the vast data storage capacity of Windows Azure Storage.

Create a Storage account for SQL Server to backup to

image

I have chosen West Europe Here as North Europe is my primary Datacenter where my production SQL Servers reside. I have selected to use locally redundant which means data in this storage account will remain in this data center and will be protected in triplicate by Microsoft.

Once the account is provisioned create a container for the backups.

image

image

Container is created here.

image

Click Dashboard and then the Manage Access Keys button at the bottom of the screen.

image

 

Click the clipboard icon (I used the secondary access key).

image

Once copied into the buffer, open a new query windows in SQL Server Management Studio

image

In the new Query Window Enter Create a New credential and run the backup.

Here we see I have pasted the key copied from Azure to my SQL Query. Entered the name of my storage account “mitsqlbackups” Then specified the database to be backed up and the full path of the backup file wchih includes the container we created earlier (mysqlbackups).  I have also enabled compression here to help reduce the size of the backup file at rest.

image

Code pasted here, feel free to add your own comments.  I have highlighted in red the text that you would need to change for your environment.

DROP CREDENTIAL mycredential

IF NOT EXISTS(SELECT * FROM sys.credentials
WHERE credential_identity = ‘mycredential‘)
CREATE CREDENTIAL mycredential WITH IDENTITY = ‘mitsqlbackups
,SECRET = ‘wORKivaVUFo8HxJ…..copythiskeyfrom azure, storage account dashboard‘;

BACKUP DATABASE [master]
TO URL = ‘
http://mitsqlbackups.blob.core.windows.net/mysqlbackups/master.bak’
WITH CREDENTIAL =’mycredential’,COMPRESSION,STATS=5;
GO

We can see in the Azure Portal that the backup has indeed been created successfully.

image

The backup to Azure feature is only supported through script at this time and not through the GUI.  Maybe this will change in SQL 2014.  I’ll be checking when it RTMs next week.  So you can script your SQL backups using the credential that is already created.  For example running the following code will run the backup from script without needing the Azure Storage Access Key which is now stored and encrypted within SQL Server.

BACKUP DATABASE [master]
TO URL = ‘
http://mitsqlbackups.blob.core.windows.net/mysqlbackups/master2.bak’
WITH CREDENTIAL =’mycredential’,COMPRESSION,STATS=5;
GO

A second Backup has also been created now from the script above.

image

 

The above tasks can be done via T-SQL, PowerShell or C#. Please see http://technet.microsoft.com/en-us/library/jj919148.aspx#credential for full syntax and examples on these options as well as difference backup options such as differential, log backups Etc.

 

Restore a Database Directly from Azure Storage

Here I have restored the database we backed up earlier.  Yes Master was a bad example ! Smile.  And so I have restored it with a different name in a different file location as an example.

RESTORE DATABASE Master2 FROM URL = http://mitsqlbackups.blob.core.windows.net/mysqlbackups/master2.bak’
WITH CREDENTIAL = ‘mycredential’
,MOVE ‘master’ TO ‘F:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Master2.mdf’
,MOVE ‘mastlog’ TO ‘F:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Master2.ldf’
,STATS = 5

image

You could also click the download button of course if you wanted to download the backup file quickly and manually.

image

 

Well I hope someone finds this useful and shows that it is in fact very easy to point SQL Backups at Azure storage which is highly available and can be used for DR purposes without additional software or hardware.

Cost Illustration

Using the Azure Cost Calculator (March 2014) we can see the cost of storage 1 Terabyte of locally redundant data in Azure costs £25.28 with a 12 month commitment plan.  Pay as you go price is £32.62.  A Windows Azure Enterprise Agreement should reduce the £25.28 further.  Check with your license reseller for exact pricing, these prices are given as an illustration from the guide prices provided on the Microsoft www.Windowsazure.com website using the full calculator available here, http://www.windowsazure.com/en-us/pricing/calculator/?scenario=full

image

 

Some Best Practice Guidance for using SQL Backup to Azure

  • Use Backup Compression to reduce cross data center data costs and speed up backup times.
  • Use a unique file name for every backup to reduce risk of overwriting backups accidentally.
  • Set access level on the storage container as private so only authorised access is granted !
  • Use same region as SQL VM if already in Azure and you do not need to backup to a different data center to avoid charges and for best performance of backup and restore times.

Troubleshooting

Error Message 3271 Returned “A nonrecoverable I/O Error occurred on file HTTPS://…..

Ensure the backup credential Identity is the same as the storage account name.

Syntax is here:-

IF NOT EXISTS (SELECT * FROM sys.credentials WHERE credential_identity = ‘mycredential’) CREATE CREDENTIAL mycredential WITH IDENTITY = ‘mystorageaccount’ ,SECRET = ‘<storage access key>’ ;

Here the mystorageaccount should be mitsqlbackups as this is the name of my storage account.

image

 

Reference

SQL Server Backup and Restore with Windows Azure Blob Storage Service

http://technet.microsoft.com/en-us/library/jj919148.aspx#credential

More Best Practices and Troubleshooting

http://technet.microsoft.com/en-us/library/jj919149.aspx

Advertisements

About Mitesh Chauhan
Mitesh Chauhan, Azure Cloud Solutions Architect. This is my blog where I share articles and thoughts on IT Infrastructure and architecture. The topics I am most passionate about are Implementation and architecture of rock solid Cloud Infrastructure based around SQL Server and Windows Server mainly using Microsoft Azure. MCTS - Azure Architecture MCTS - Azure Implementation MCSE Server Infrastructure (Windows Server 2012) , MCITP SQL Server 2008, Togaf Certified, Prince 2 Practitioner.

3 Responses to How to Backup SQL Server Directly to Azure Storage

  1. Pingback: Backup SQL Server directly to Azure - IT Consulting Solutions Blog

  2. Emile says:

    Great post, the post is two years old and Azure’s new portal looks different, but I was able to follow your steps with no problems. Thank you.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: