Azure Table Storage Vs. SQL Azure
At the time of application design decision on Azure, most of us get into this discussion at some point in time, “Table Storage OR SQL Azure”.
I thought of listing down the pros and cons for both of these storage options. Following comparison might be helpful to drive the decision.
List down the expectations from the desired storage from application point of view and compare those requirements with the help of following table.
Table Storage | SQL Azure | ||
Pros | Cons | Pros | Cons |
Provides scalability out of the box and supports approximate ~ 100TB storage | Every transaction (CRUD operation) is charged within the platform which might accumulate higher charges for high volume transactional systems | Transactions within the platform are not charged | Doesn’t provide scalability out of the box and there is a hard limit of 50 GB per database instance. Application need to handle the partition logic |
More suited for developing highly scalable, high volume applications | Doesn’t support ACID characteristics of transaction across multiple entities/tables | Supports ACID characteristics of transaction across multiple entities/tables (but not across databases) | More suited for departmental applications |
| Single query can serve upto 1000 entities only, beyond that we need to use continuation logic in application | Doesn’t impose any limitation like this |
|
Since application doesn’t have to build custom partition logic, effort to build such functionality in application is reduced | Inability to support seamless portability of an application between on-premise and Azure. | More suited to support seamless application portability between on-premise and Azure | Since application has to build custom partition logic might incur sizable efforts to build such functionality |
| Doesn’t support relational data store & stored procedures etc. | Supports relational data & stored procedures etc. |
|
| No support for Spatial data type | Going to support spatial data type |
|
Hope you find this information useful, any other points/suggestions/comments are most welcome.
Comments
Here is an interesting video session on this topic from Microsoft:
http://sqlbits.com/Agenda/event4/Comparing_Azure_storage_and_SQL_Data_Services/default.aspx
http://download.microsoft.com/download/6/8/7/687C09AC-0D0A-481E-AD69-7E5F9A566EE5/Comparing_Azure_storage_and_SQL_Data_Services.wmv
Apart from technical aspects, one should also look at the cost (rate) aspects on cloud - that gives direct monetary benefits if we use table storage - in some cases it would be million dollar project for just conversion.
Posted by: Bhavin Raichura | April 1, 2010 4:48 AM
"Doesn’t support ACID characteristics of transaction across multiple entities/tables"
That's not entirely correct. Azure Tables have Entity Group Transactions.
Posted by: Christoph | June 3, 2010 8:08 AM
Christoph, Thanks for your comment.
In Azue Table Storage the ACID transaction is supported across only a single table. "Doesn’t support ACID characteristics of transaction across multiple entities/tables" from http://msdn.microsoft.com/en-us/library/dd894038.aspx.
The point which I am driving is "Doesn’t support ACID characteristics of transaction across multiple entities/tables" which is possible in SQL Azure where you can update mutiple tables in a single transactions.
Hope it clarifies.
Posted by: Sachin Prakash Sancheti
|
June 4, 2010 7:55 AM