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.

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:

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:

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

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:

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:

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

On-premise database creation

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

Then, I take the database offline:

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

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

And now, I bring the database online:

But the following error occurred:

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

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.