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.

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)…

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

 

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.

 

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.

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

 

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.