Azure : Setting up MSSQL to integrate with Azure Storage

Introduction

From SQL2014 (and upwards) you are able to integrate your Microsoft SQL Database with Azure Storage Accounts. You can do this for your backups, but also for your data files.

IC851848.jpeg

And to be honest, seting it up isn’t that hard!

Prepare our storage account

The first thing we need to do is create a new container for our SQL backup (or data files)…

2016-04-22 12_35_52-sqlbackup - Microsoft Azure

And offcourse grab our storage account key, as we’ll be needing that one in a bit!

2016-04-22 12_45_23-Access keys - Microsoft Azure

 

Setting up the SQL credential

Once we have those things already setup / grabbed. Let’s create a credential in MSSQL. You can use the following code for that ;

CREATE CREDENTIAL [storageaccountname]
WITH IDENTITY = 'storageaccountname',
SECRET = 'ix3uBHPX+...my-secret-key...zxzwE+NeqgUYO3pGmQ=='

Be sure to change the “storageaccountname” and enter your storage account key as the secret. Then exectute your query, and you’ll see a credential being added.

2016-04-22 12_37_02-kvaessql22-3 - 40.68.123.167_3389 - Remote Desktop Connection

 

Doing a test run

Now that we have our credential in place and we’ve added a container. Let’s try the backup… with again a code sniplet. Be sure to change the storage account name here.

BACKUP DATABASE [Master]
TO URL ='https://storageaccountname.blob.core.windows.net/containername/master.bak'
WITH CREDENTIAL = 'storageaccountname'

And we’ll let it rip… Immediately we see that our container gets provisioned with the backup file.

2016-04-22 12_35_45-sqlbackup - Microsoft Azure

And less than a second later it finished… (small db size) 😉

2016-04-22 12_40_08-kvaessql22-3 - 40.68.123.167_3389 - Remote Desktop Connection

 

TL;DR

  • You can use Azure storage as a backup/restore destination.
  • You can use Azure storage to directly store/access your data files.

 

UPDATE

I would advise to also check the follow-up post… as this unveils how to setup the integration with the data files.

Advertisements

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