Windows Azure Storage or SQL Azure?

When deploying applications to The Cloud (on Windows Azure), we have a choice in storing our data in Windows Azure Storage or SQL Azure. For some applications, the decision can be straight-forward but for some business applications, it may not be that simple.

Windows Azure Storage, at the time of this writing, offers Tables, Blobs, Queues and Drives storage. Personally, I think Queues and Drives will be used for specialized cases, therefore, I will just focus on Tables and Blobs. It is quite common that when we have media or graphics (unstructured) data, we can leverage on Blob storage. We can even store an entire XML document inside Blob storage.

Table storage on the other hand offers us some what like a structured storage but with some caveats. Table storage in actual fact is more flexible than a SQL table because it doesn't require to have a fixed schema. That means, we can have objects of varying fields being stored in it. Of course, being able to support it doesn't mean we will do it because it will require some additional logic in our code to check which type of Entity are we dealing with. Table storage is also less "searchable". It will be quite a challenge to extract a set of data with varying filter conditions.

SQL Azure is similar to the SQL database that we all love (with only some features missing here and there). If we are not doing any overly complex T-SQL acrobatics, our application can be easily transitioned to SQL Azure with minimal or no changes. SQL Azure can also support binary storage for unstructured storage.

So, up to now, everything is pretty straight-forward. Highly unstructured data, low searchability requirements - Azure Storage. Structured data, high searchability requirements, RDBMS-feel - SQL Azure.

Next comes the cost factor. At a glance, Azure Storage is cheap costing about USD 0.15/GB. A 1GB SQL Azure cost USD9.99 a month. You will need to study the data access frequency of your applications. If your application accesses your data frequently (i.e. photo album), then you will be in for a surprise because there are storage transaction cost for Azure storage. That means you will be billed for accessing your data. SQL Azure does not have charges when you access your data.

So make sure you consider your applications' data access frequency before deciding.

At times when you are torn to choose either one, try to think of a hybrid solution instead. For example, if I am writing an Expense application that stores scanned-in images of receipts, I can quickly bloat up the size of my SQL Azure database (and quickly increasing cost). But those images aren't going to be accessed frequently, most likely will be accessed only a few times by the Approver and HR. Therefore, it makes sense to store the images on Blob storage but maintains a key back to the Expense records in my SQL Azure database.

That way, we can have the full RDBMS-like feature for our data, high searchability and better cost management.