Azure Table Storage - Entity Design Best Practices Question

Question

Im writing a 'proof of concept' application to investigate the possibility of moving a bespoke ASP.NET ecommerce system over to Windows Azure during a necessary re-write of the entire application.

Im tempted to look at using Azure Table Storage as an alternative to SQL Azure as the entities being stored are likely to change their schema (properties) over time as the application matures further, and I wont need to make endless database schema changes. In addition we can build refferential integrity into the applicaiton code - so the case for considering Azure Table Storage is a strong one.

The only potential issue I can see at this time is that we do a small amount of simple reporting - i.e. value of sales between two dates, number of items sold for a particular product etc. I know that Table Storage doesnt support aggregate type functions, and I believe we can achieve what we want with clever use of partitions, multiple entity types to store subsets of the same data and possibly pre-aggregation but Im not 100% sure about how to go about it.

Does anyone know of any in-depth documents about Azure Table Storage design principles so that we make proper and efficient use of Tables, PartitionKeys and entity design etc.

there's a few simplistic documents around, and the current books available tend not to go into this subject in much depth.

FYI - the ecommerce site has about 25,000 customers and takes about 100,000 orders per year.

Answer 1

Have you seen this post ? http://blogs.msdn.com/b/windowsazurestorage/archive/2010/11/06/how-to-get-most-out-of-windows-azure-tables.aspx

Pretty thorough coverage of tables

Answer 2

I think there are three potential issues I think in porting your app to Table Storage.

1. The lack of reporting - including aggregate functions - which you've already identified
2. The limited availability of transaction support - with 100,000 orders per year I think you'll end up missing this support.
3. Some problems with costs - $1 per million operations is only a small cost, but you can need to factor this in if you get a lot of page views.

Honestly, I think a hybrid approach - perhaps EF or NH to SQL Azure for critical data, with large objects stored in Table/Blob?

Enough of my opinion! For "in depth":

• try the storage team's blog http://blogs.msdn.com/b/windowsazurestorage/ - I've found this very good
• try the PDC sessions from Jai Haridas (couldn't spot a link - but I'm sure its there still)
• try articles inside Eric's book - http://geekswithblogs.net/iupdateable/archive/2010/06/23/free-96-page-book---windows-azure-platform-articles-from.aspx
• there's some very good best practice based advice on - http://azurescope.cloudapp.net/ - but this is somewhat performance orientated

Answer 3

If you have start looking at Azure storage such as table, it would do no harm in looking at other NOSQL offerings in the market (especially around document databases). This would give you insight into NOSQL space and how solution around such storages are designed. You can also think about a hybrid approach of SQL DB + NOSQL solution. Parts of the system may lend themselves very well to Azure table storage model. NOSQL solutions such as Azure table have their own challenges such as

• Schema changes for data. Check here and here
• Transactional support
• ACID constraints. Check here

Answer 4

All table design papers I have seen are pretty much exclusively focused on the topics of scalability and search performance. I have not seen anything related to design considerations for reporting or BI.

Now, azure tables are accessible through rest APIs and via the azure SDK. Depending on what reporting you need, you might be able to pull out the information you require with minimal effort. If your reporting requirements are very sophisticated, then perhaps SQL azure together with Windows Azure SQL Reporting services might be a better option to consider?