Azure : Integrating MSSQL data files with Azure Storage

Introduction

In the previous post I showed you how you can setup a backup to Azure storage and also mentioned that you can add your data/logfiles to Azure storage. An important note there is that this feature does not work with the storage account key, but you’ll need to set it up with SAS (Shared Access Signature) tokens.

Generating the SAS token

The easiest way to generate the token is to use the following code sniplet, which has the following result ;

2016-04-22 19_20_10-Windows PowerShell ISE

 

Create the credential

Use the generated T-SQL code (output from the above sniplet)…

2016-04-22 19_37_25-kvaessql21 - 104.40.158.231_3389 - Remote Desktop Connection

And the credential has been set up!

 

Setting up a new database

Let’s use a typical create database statement. Though instead of a local drive, we’ll be using the link to the container on our storage account.

-- Create database with data and log files in Windows Azure container.
CREATE DATABASE testdb 
ON
( NAME = testdb_dat,
    FILENAME = 'https://testdb.blob.core.windows.net/data/TestData.mdf' )
 LOG ON
( NAME = testdb_log,
    FILENAME =  'https://testdb.blob.core.windows.net/data/TestLog.ldf')

And here is how it went for me…

2016-04-22 19_37_14-kvaessql21 - 104.40.158.231_3389 - Remote Desktop Connection

 

Extending an existing database

You can also do the same on an existing database / via the gui (sql management studio.

2016-04-22 19_41_40-kvaessql21 - 104.40.158.231_3389 - Remote Desktop Connection

Add the files and press okay. An important note here, if you are using the auto extend, be aware that the max “file” (blob) size is 1023GB on Azure!

 

Validating

And just to be sure… Yes, the data files are added to our azure storage account!

2016-04-22 19_42_28-sqldatafiles - Microsoft Azure

 

TL;DR

  • Working with data files that are directly stored on an Azure Storage Account is possible
  • This system works with SAS tokens and not with a storage account key.

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.