How To: Backup SQL Server Databases to Azure - The SQL Nomad

Have Database, Will Travel

How To: Backup SQL Server Databases to Azure

Adding “the cloud” to your data center’s high availability/disaster recovery scheme is a bewitchingly tempting proposition. An infinitely scalable product that is always being replicated to datacenters across the globe for your protection? Who wouldn’t want that? Answer: folks on a budget. Today I’m going to show you the simplest — possibly most inexpensive — way to protect your SQL Server databases using Microsoft’s cloud, Azure, in a little tutorial I call (for SEO purposes, natch): “How to Backup SQL Server Databases to Azure.” I will probably regret having used the word “cloud” so much in the 2030s as much as I regret having ever uttered “information super-highway” in the 90s.

First things first: get thee to Microsoft and create an Azure account (if you don’t have one already). Microsoft desperately wants more people to use its cloud-based services, and so it’s pretty generous with free samples: MSDN subscribers get monthly Azure credits, Visual Studio purchasers get monthly Azure credits, and Microsoft will give anyone who wants to try it $200 in credits just for signing up. In fact, if you wanted to, you could activate all of those subscriptions at once. Or so I’ve heard.

Azure Subscriptions

 

Ok, so here’s the pseudo-code: we’re going to create a Resource Group. Consider that a collection of Azure services. It’s basically there to help ease the management of multiple Azure products. Then we’ll create a Storage Account, and add a Container to it. Finally, we’ll go to SQL Server and create a Credential, and backup a SQL database to our container. We’ll finish with a quick analysis of our results.

Resource Group

Once you have created your Azure account, you’ll be taken to the account portal, which normally starts out with a list of Azure products you already have. Along the left border there will be icons representing various Azure goodies. At the top of that list will be a little blue cube. This is the Resource Group icon. Clicking it will filter the dashboard page down to just Resource Groups. Click the “+ Add” button to create a new one.

Resource Groups are pretty easy to configure: give it a name, tell Microsoft what subscription to tie it to, and choose a geographical location for the group to reside. Click create and give it a minute or two to configure.

nomad20161013_1

Storage Account

Once your Resource Group has been provisioned, you’ll want to have a Storage Account. Storage Accounts are used for exactly what it sounds like: to hold files. To add one, look for the little green and white box icon on the far left and click it. The list of existing storage accounts will come up, and from there you can click “+ Add” to create a new one.

nomad20161013_2

Give your storage account a good, descriptive name. This is important because you’re sharing a URL with other Azure users, and if the name isn’t globally unique, it won’t pass muster. Oh, and lowercase letters and numbers, please. For some reason, camel-case scares Azure.

There are two kinds of Storage Accounts: General Purpose and Blob Storage. Choose general purpose, because SQL will not be able to backup to a blob storage account. For performance, I picked Standard — pricing wise, there’s no point in spending extra money on faster/bigger systems when all I want is a file store. I did set the encryption setting to enabled: any extra protection you can get for your data is good. Choose which Subscription you want the storage account set to, attach it to your new Resource Group, and then pick which Azure data center it should be hosted at. Typically, the closer the better.

nomad20161013_3

Container

Now, think of a storage account as an unallocated physical hard drive. A Container, then, is the logical drive you build from there. Just like with a physical drive, you need to have at least one Container in your Storage Account, but you can have any number of Containers there too. Once you’ve created your Storage Account (and be patient here, it usually takes a minute or two for the account to be provisioned and show up in your Azure dashboard), you can click on it, and see all the containers within it, set permissions, and perform basic maintenance.

When you click on your Storage Account, you’ll see a list of menu options you can pick from, grouped by Settings, Blob Service, etc. Click on the Containers link in the Blob Service group, and it will show you a list of all your Containers. Just like before, click “+ Container” at the top of that section to add a new one.

nomad20161013_4

When you click on + Container, you simply have to give it a name, and choose the access level for the file. Your options are:

  • Blob – public read access to files
  • Container – public read and list access to files
  • Private – no public access to files

I can’t think of any reason in the world you’d want a Container holding data to not be set to Private.

nomad20161013_5

Access Key

Now, speaking of not letting anybody get to that Container, we need to tell SQL how it can connect to save backups there. In your Storage Account menu, the top entry in Settings is Access keys. Click there, and you’ll find some auto-generated strong passwords for the account. You can regenerate them at any time. Azure keeps two keys so you can set your connections to use one, while you’re regenerating the other. That helps keep you from breaking connections when passwords change.

Copy the key you plan on using and let’s jump into SSMS for the actual SQL part of today’s fun. Also, never cut and paste keys like this and put them on the internet if you’re not working with a sandbox project. It’s not safe.

nomad20161013_6

SQL Credential

Creating a Credential to authenticate SQL with Azure is very straight forward. You just have to give it the Storage Account name, and the Access Key you want to use. The syntax looks like this:

 

Backup Syntax

Now, all that’s left is start backing up. From SQL 2012 and on, you can backup to a URL, and that’s what we’re going to do. Over on your Azure dashboard, you can find the URL for your Container. Click on the Properties link for the container, and you’ll find the URL there. Grab that, and we’re ready to roll.

nomad20161013_8

Execute a BACKUP DATABASE command, with the modifier TO URL and insert the URL you just got. Then be sure to use the WITH CREDENTIAL clause to attach the credential you created.

 

 

And that’s it. Or is it?

Benchmarks

There are a lot of factors you need to consider when deciding to use cloud storage or not. Cost is one fact. Time is another. Clearly, once you do a little bit of configuration and setup work, there’s no more time or challenge to backing up to Azure than there would be backing up to your local SAN. And relatively speaking, the costs are pretty negligible to save a backup to Azure. The biggest factor (for me) here is time, because backing up a large file online could be problematic if you don’t have a big-enough pipe.

My database, WidgetDB, compressed to a 6.27 GB backup file. When I backed that up to my SAN, it completed in 1.77 minutes. But when I performed the BACKUP TO URL, that same backup took 19.28 minutes. That meant we went from about a 3.5 Mb/Sec transfer rate all the way down to 0.3 Mb/Sec. If you’re talking about a bigger database, say in the couple-hundreds of gigabytes? That can get problematic.

So there you go. Fast, simple, easy, and (dare I say it?) fun!  You now know how to backup SQL server databases to Azure. Yay! The cloud is here. Hovering right over the, uh, information super-highway. Embrace it!

 

 


Leave a Reply

  • Subscribe!

    Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 348 other subscribers

  • Topics