Choosing the right storage technology on Window’s Azure

How do you choose the right storage technology when building on Window’s Azure? You have got several native storage abstractions at your disposal on the platform and with the newly released IaaS offerings things just got even more confusing. Firstly you need to take a step back and ask a few questions?

What type of application are you building?

Martin Fowler has a great blog post on polygot persistence –  its becoming more common for companies to  have a variety of different data storage technologies for different kinds of data. If you are designing a financial application with complex reports then the relational Windows Azure SQL Database is a good place to start but just for the transactional stuff. Off loading auditing and logging data to table storage could save you some cost and this data is likely to grow over time.Product catalogs and data that changes infrequently are good candidates for table storage.

How deep are you pockets?

2TB of relational data in an azure SQL database is likely to cost you approx $50000 a year  and that’s just for the storage alone.The same amount of data in blob storage / table storage will come in around $7000.

How much data are you dealing with?

When looking at sheer volume, table storage is far more scalable than Windows Azure SQL Database.  A single storage account (storage accounts hold blobs, queues and tables) can grow to 100TB in size, in theory your table could consume all 100TB. Azure databases have a hard limit of 150GB.

Will the data only live in the cloud ?

SQL Server 2012 and Windows Azure SQL database are very close in structure and the gap will continue to close. There are plenty of migration tools that allow you to move data from an on premise SQL database to one in the cloud. SQL Data Sync takes it a step further and allows you to synchronise changes between an on premise database and the cloud. Table storage ties you to the cloud.

description Uses size transaction support
Blob storage Unstructured WORM (write once read many) data images, binaries, files, installers, back ups 100 TB maximum per storage account
Windows Azure Drives For exposing a volume accessible to code running in your Windows Azure service Use NTFS APIs to access a durable drive 100 TB maximum per storage account
Table storage NoSQL data store –  a Table is a set of entities; an entity is a set of properties product catalogue, logs, audit trails 100 TB maximum per storage account Supports transactions for entities in the same table and table partition, but not across tables or partitions.
Queue Storage Durable message Queue but the  order is not guaranteed passing messages in a distributed system messages up to 64K Not transactional, messages can get picked up more than once
Windows Azure SQL Database relational database as a service reports, financial apps 150GB per database full ACID support