MSSQL data and log files on Azure blob storage

I discovered lately one killer feature of SQL Server – keeping data and log files on Azure blob storage. There are scenarios where we may want to go with blob storage instead of buying and building up our own stable and reliable storage. This blog post introduces how MSSQL data and log files work on Azure blob storage.

Why MSSQL data and log files on Azure blob storage?

There are multiple reasons why one may want to keep MSSQL data and log files on Azure blob storage:

• Ownership and maintenance costs – Azure blob storage is stable and reliable cloud storage service and those who don’t want to build up their own storage solution may decide to benefit from Azure blob storage as MSSQL data files location.
• High-availability – it’s possible to configure multiple MSSQL virtual machines to use same data files and when one machine crashes then the other takes over withing few minutes. There’s no need to restore data as the latest state is already available on cloud.
• SaaS solution – SaaS providers who host MSSQL on virtual machines may find it more convenient for maintenance if data and log files are on Azure blob storage.

Now let’s try to set up a database to see how data and log files on blob storage work.

Generating shared access signature (SAS) key

First there must Azure storage account with at least one blob storage container where data and log files are held. We need shared access signature (SAS) key for MSSQL to be able to communicate with blob storage. SAS key can be generated from Azure portal.

SAS key can also be generated using Azure Storage Explorer – the convenient tool for managing Azure storage.
It’s free and I’m using it practically every day. Of course, those who are strong on PowerShell can also use PowerShell.

Creating cloud credential

Before creating databases and data files we need to create credential for MSSQL. It’s done with the following simple command.

CREATE CREDENTIAL [https://example.blob.core.windows.net/data]
WITH IDENTITY='SHARED ACCESS SIGNATURE',
SECRET = '<your SAS key>'

Make sure you also use correct URL to blob storage container where data files will be held.

Creating database to Azure blob storage

Creating database on Azure blob storage is simple. We use familiar CREATE DATABASE command with file locations.

CREATE DATABASE test
ON  
( 
    NAME = test_data,  
    FILENAME = 'https://sample.blob.core.windows.net/data/TestData.mdf' 
)  
LOG ON  
( 
    NAME = test_log,  
    FILENAME =  'https://testdb.blob.core.windows.net/data/TestLog.ldf'
)

It’s also possible to add new database file to existing database for tables we want to be available on cloud. Here are files of my demo database for my first linked servers blog post Querying MySQL from SQL Server using linked server. Database file OnBlobStorage1 is held on Azure blog storage.

Let’s see now how things work out.

My experience

There are not many concerns when MSSQL is hosted in same data center. There are fast cable connections between servers and services are maintained by well trained personnel. I wanted to try out a hybrid scenario – in-prem MSSQL and data file on cloud. My main concern was performance. Considering that MSSQL is located in Estonia and data file is located in Amsterdam – it’s approximately 1500 km over the air or 2000 km on the land – then what are the penalties?

Experiments with small databases went well. I didn’t noticed any delays that should affect web application consuming the database. I also tried things out with database of little bit over 2GB. I hosted data files and logs to cloud just to see how things works out. After getting data files to cloud I started application on my dev box to see what happens.

Few notes about those charts:

1. Incoming data (ingress) made high peak when I uploaded data files to blob storage.
2. Outgoing data (egress) made high peak when I took database online and started web application.
3. When browsing around in web application there were some small peaks on egress chart – usually around 20MB.

If MSSQL is configured correctly then there should not be much traffic between MSSQL and Azure blob storage. Of course, if data is added, updated or deleted then there will be additional traffic. Anyway I didn’t noticed any lags or other references to possible bottlenecks with data files on Azure.

Wrapping up

Although in my mind I was ready for surprises like huge latency and unexpected traffic between MSSQL and Azure blob storage, my experiment went very smooth and well. My local MSSQL has enough resources to run 2GB database and probably this is why there was not much traffic. Also I didn’t faced any bad delays although I made experiments on regular home connection that is not in perfect shape all the time. When planning to use Azure blob storage for MSSQL make some homework on validate if service works well for you and if costs doesn’t grow too high.