Currently I have a prototype that runs in IIS on my local machine and uses SQL Server Express 2005 for storing data in three SQL tables. I run queries with transactions that employ up to two of those three tables.
Now I need to move my prototype to Windows Azure and can't decide which to choose - SQL Azure or Azure Table Storage.
How do they compare? How do I decide which to choose?
Windows Azure's SQL Database is a relational database, with all the things you'd expect from a relational database (multiple indexes, stored procedures, powerful queries, etc.). Azure Table Storage is a non-relational, massively scalable (up to 100TB per account) storage facility, where entities are located by partition key (a colocation of entities) and row key.
If you want to have a very simple storage mechanism that doesn't require sophisticated relational operations, Azure Table Storage will work quite nicely.
EDIT June 7, 2012: Updated with Spring Release pricing There are cost differences too. SQL Database starts at $4.99 for 100MB, scaling up on a tiered scale (about $26 for 5GB, $125 for 50GB, $225 FOR 150GB) but has no transactional costs. Azure Table Storage runs $0.125 / GB (or 0.09 / GB without geo-replication), dropping in per-GB price as quantity goes up, but has a $0.01 / 100,000 transaction cost (nominal for low volume systems, but could be significant with very high volume systems). Full pricing details are here.
There's a fairly recent article in MSDN Magazine that goes into greater detail regarding use cases, differences, etc.
If you're going for a straightforward migration of what you have in place today, SQL Database will closely match what you have in SQL Server Express 2005. However, since it's only a prototype at this point, it's worth re-evaluating your needs.
"How do I decide which to choose?"
Good question.
You have to work out what you care about, and evaluate the options against each other.
The key difference in my view is that SQL Azure is less scalable than table storage. if you're expecting huge numbers of visitors, your database may not be able to keep up, and become the bottleneck that stops you from scaling any further - in the Cloud, you can keep adding front-end servers until your credit card bleeds, but once you've gone to a "big" database server, you have nowhere else to go.
(Except that's not really true - you can also find a way of partitioning your application across multiple database servers).
So, if you care about scalability, you may want to go to table storage - it doesn't have the same scalability limits as SQL Azure.
However, the cost of that scalability is stuff you might also care about - you basically have to architect your application from scratch to work with table storage, and you have to recreate a lot of stuff you get for free from a relational database. Transactions don't really work the way you might expect, for instance.
So, if it's only a prototype, and you're not explicitly intending to become the next Facebook, I'd stay with SQL Azure until the scalability pain becomes real.
