Infrastructure at your Service

Nathan Courtine

How to store SQL Server data files in Azure Storage

A new functionality, introduced with SQL Server 2014, enables to store data files from a SQL Server database in Windows Azure Storage. In this posting, I will show how it works.

 

General.png

Accessing Azure Storage

The Azure Storage account is named “dbiservices”, and is composed of the “datafiles” container. This container does not contain blobs yet.
If you are not familiar with the Windows Azure Storage terms, you should read this Introduction to Microsoft Azure Storage.
In my example, I want to grant to share an access to my “datafiles” container without having to expose my account key. I teherfore need to generate a Shared Access Signature.

Shared Access Signature Generation

For this part, I will use a Third Party tool called Azure Storage Explorer.
As soon as you have installed and launched this software, you must register you Storage Account:

Register-Azure-Storage-Account.png

You must enter your Storage account name and your Storage account key. The key has been erased voluntary in this example.

As a Best Practice, I advise to enter your Secondary Access Key and not your Primary Access Key. Indeed, the Secondary Access Key is commonly used as a temporary key, and can be regenerated if necessary.

To generate my Shared Access Signature, I have to edit the Security of my container:

Edit-Security.png

I select the permissions and the duration linked to my Shared Access Signature:

Generate-Signature.png

I generate a Shared Access Signature available one week with all permissions. The Shared Access Signature generated is an URI related to the container. I voluntary deleted partially the URI.
You need to copy the URI from “sv=” to the end.

Credential Creation

I need to create a new Credential in SQL Server Management Studio:

New-Credential.png

Create-Credential.png

The name of my Credential is the URI location of my container, and the Password is the Shared Access Signature previously created.

Creating a database with Data Files in Azure Storage

I will perform two examples: first I will create a new database directly in Azure Storage, then I will migrate an existing database to Azure Storage.

Hybrid database creation

I execute a script to create a database with its data files in Azure Storage:

Create-Database-hybrid.png

If we refresh the “datafiles” container in Azure Storage explorer, we can see the datafiles previously created:

Explore-Datafiles.png

On-premise database creation

Now, I execute a script to create an on-premise database:

Create-database-onpremise.png

Then, I take the database offline:

Take-Offline-Database.png

I upload the data files in Azure Storage using Azure Storage Explorer tool:

Upload-Datafiles-to-Azure.png

Then, we need to alter the onpremisedb database to reference the datafiles moved to the Azure Storage:

Alter-Database.png

And now, I bring the database online:

Bring-Online-Database.png

But the following error occurred:

Bring-Online-Database-Error.png

To understand the origin of the problem, let’s see the datafiles in the Azure Storage Explorer:

Blob-Type.png

Conclusion

This new feature offers some advantages such as high availability or easy migration.
But on the other hand, you cannot use it on a existing database, which is a serious drawback.
Furthermore, I do not believe that this feature would be used with on-premsie SQL Server databases, due to the latency. But I think it can be used with a virtual machine running in Azure.

One Comment

  • shahrukh says:

    Hi,

    Thank you for putting out this post.

    I tried the steps exactly as mentioned in your post, I am trying to create a DB On a Azure VM with data files in a Azure COntainer.

    On the create db statements i get an error as below…

    Msg 5105, Level 16, State 2, Line 1
    A file activation error occurred. The physical file name ‘https://sqlblob3.blob.core.windows.net/sqlcontainer/CFUtilitiestest.mdf’ may be incorrect. Diagnose and correct additional errors, and retry the operation.
    Msg 1802, Level 16, State 1, Line 1
    CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

    Please can you advise what needs to be done here.

    Thanks
    Shahrukh

Leave a Reply

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

Nathan Courtine
Nathan Courtine

Microsoft Technology Leader and Senior Consultant