Azure storage tables vs SQL

Question

I'm just starting to learn Azure and I can't see too many scenarios where you would want to put something into an Azure storage table as opposed to SQL. I guess maybe I'm used to working in environments where I need related data?

When is it better to put something into an Azure storage table and not in a table in a Azure SQL database?

Solution 1

SQL Azure is great when you want to work with structured data using relations, indexes, constraints, etc.

Azure storage table is great when you need to work with centralized structured data without relations and usually with large volumes.

The Price: SQL Azure: $25.98/mo for 5GB

Storage table: $21.88/m for 175 GB (without transactions or bandwidth) So when you store large volumes of data, storage table is a lot cheaper... think of logging for example.

The centralization: Windows Azure is a distributed environment for servers so if you save something on one machine, others won't know about it, therefore storage table is a good solution for centralized loggins, session handling, settings or whatever (keep on mind that there is latency even in one datacenter)

The speed: If you design it right, in many cases storage table should be faster than sql azure but it probably depends on use cases and I haven't really tested this.

Solution 2

While I am adding late to this post, I did want to mention one thing that appears to have not been covered. Azure SQL has data limits (soon to go up I suspect). 5 GB for Web Edition and 150 GB for Business Edition. For some solutions this is perfectly adequate. But for others, it may not be and I have personally encountered that.

Azure tables upper disk limits are much higher. I believe its 100 TB.

Also, while NoSQL is non-relational by its very nature, this is not necessarily a bad thing. We often think we need relationships when in fact if we think of our problems differently we may not, as much. You can enforce relationships within code itself--which I suspect most developers do a lot more than they really think.

Solution 3

I would use allways use azure tables as MUCH cheaper solution if:

• I perform table selects ONLY by PK (select on property is slow due to the of the whole deserialization)
• I can live with limited Linq set (Query Operators (Table Service Support)
• I don't need to join tables and perform complex query on the server
• I need horizonatal partitioning "sharding" of my data (Sql Azure Federations is step in that direction by Tables have PartionKey from day 0

Comments

[RickNZ]

Azure tables are only cheaper than SQL Azure if the data access pattern is relatively light, since tables have a per-transaction fee and SQL Azure doesn't.

[Ohad Schneider]

@RickNZ It's worth noting that at the time of writing that fee is very low: $0.005 per 100,000 transactions.

[Prem]

Regarding the Centralization, Table storage as well as Azure SQL, both provide Geo-replication. Link.

[Prem]

Regarding pricing, which is bit tricky, Azure SQL charges approx $1400 per month, even if there's no data or transaction. While the Table storage is not on duration basis, it's on data basis. For 10 million operations of any kind, the cost should be somewhere near $100.