SQL Azure vs Table Storage
29 March 2010
With a new technology like the Azure Platform, it’s hard to make architectural decisions like between putting your data on SQL Azure or Table Storage.
Costing
Looking at the pricing it appears that Azure Storage is better value:
SQL Azure Storage
- Web Edition: Up to 1 GigaBytes relational database = £6.055 / month
- Business Edition: Up to 10 GigaBytes relational database = £60.604 / month
Azure Table Storage
- Compute = £0.0728 / hour
- Storage = £0.091 / GigaBytes stored / month
- Storage transactions = £0.0061 / 10,000 transactions
Data transfers for either SQL Azure or Table Storage
Data transfers = £0.0607 per GB in / £0.091 per GB out (North America and Europe)
Source: Microsoft
Even a million Table Storage transactions would only cost 61 pence, which is very competitive compared to the minimum fee of SQL Azure.
A few of the highlights of Windows Azure Table
- Support for LINQ to Objects, ADO.NET Data Services and a REST API.
- Compile time type checking when using the ADO.NET Data Services client library.
- A rich set of data types for property values.
- Support for unlimited number of tables and entities, with no limit on the table size.
- Strong consistency for single entity transactions.
- Optimistic concurrency for updates and deletes.
- For long queries or queries that timeout, partial results are returned with a continuation token to allow the query to resume where it left off.
Source: Microsoft
Pessimistic (SQL Azure) versus optimistic (Table Storage) concurrency
In the field of relational database management systems, optimistic concurrency control (OCC) is a concurrency control method that assumes that multiple transactions can complete without affecting each other, and that therefore transactions can proceed without locking the data resources that they affect.
Source: Wikipedia
As David Hayden puts succinctly in his blog article, "the burden" is on the developer to ensure the application data changes are not lost and the data read is the latest version.
SQL Azure pessimistic concurrency isn't simply better or worse than the Table Storage optimistic concurency: there is a significant overhead in maintaining locks and locking data preventing other users from accessing it.
For many web applications (forums, content management systems, websites) optimistic concurrency is a faster and more scalable solution. That's why I'm moving the eBabel multilingual website builder from SQL Azure to Table Storage.
If you liked this blog post, you may want to subscribe to my news feed in your RSS reader.
