When should I use Sql Azure and when should I use table Storage?

When should i use Sql Azure and when should I use table Storage? I was thinking , use table storage for transaction processing scenarios e.g. debit credit accounts kind of scenario and use Sql Azure when data will not be used for transactional purposes e.g reporting. What do you think?

This is an excellent question and one of the tougher and harder to reverse decisions that solution architects have to make when designing for Azure.

There are mutliple dimensions to consider: On the negative side, SQL Azure is relatively expensive for gigabyte of storage, does not scale super well and is limited to 150gigs/database, however, and this is very important, there are no transaction fees against SQL Azure and your developers already know how to code against it.

ATS is a different animal all together. Capable of mega-scalability, it is dirt cheap to store, but gets expensive to frequently access. It also requires significant amount of CPU power from your nodes to manipulate. It basically forces your compute nodes to become mini-db servers as the delegation of all relational activity is turned over to them.

So, in my opinion, frequently accessed data that does not need huge scalability and is not super large in size should be destined for SQL Azure, otherwise Azure Table Services.

Your specific example, transactional data from financial transactions is a perfect place for ATS, while meta information (account profiles, names, addresses, etc.) is perfect for SQL Azure.

Igor and Mark gave great answers. Let me add just a bit more...

With SQL Database (formerly named SQL Azure), you can now have databases up to 500GB. To go beyond that, you'd need to partition your data. Note: Originally I suggested shards with SQL Federations, but this feature has since been retired.

ATS does offer transactions at the partition level (entity group transactions). See this MSDN article for more information. This is not as robust as SQL Azure transactions, but it does allow for batch operations in a single transaction.

EDIT It's been over a year since this question was asked (and answered). One comparison point was on pricing. While SQL Azure is still more expensive than ATS, the cost of SQL Azure has dropped significantly in the past year. Databases now have tiered pricing, starting at $4.99 for 100MB, increasing to $225 for 150GB (a big drop from the $9.99 / GB pricing from last year. Full pricing details are here.

EDIT Aug 2014 Another year later, another update. While web/business tiers continue to exist, they are being sunsetted (and SQL Federations is no longer available). The new Basic, Standard, and Premium tiers are now available (see here for details).

Some of these answers don't seem complete, so I'll add my 2 cents.

Azure Table's Good points:

• Strong point is its ability to store lots of little data; Azure table is based on Azure Blob, but is geared towards smaller data
• Much cheaper than Azure SQL Server
• Anytime you know both the partition key and the row key, the data access is very fast.
• Entity transactions are possible if you place two different "schemas" in the same partition key.
• Where the total row size is LESS THAN 980K (SQL Row)
• Where each property is LESS THAN 64K (SQL Column)
• It can act as a poor man's SQL.

Azure table's bad points:

• SQL is the weak point. Don't expect to use this on any large SQL table or you will suffer performance issues.
• Limited SQL is available, don't expect joins of any type, besides what you implement in Linq
• Azure Table SQL doesn't scale as well as its ability to store an infinite amount of data
• Anytime you don't specify both a partition key and row key in a WHERE clause, expect a slow table scan to occur
• Expect table scan performance to degrade as you add more rows
• Don't expect Azue Table queries to be fast as you add more rows
• Bottom line, if you're using Azure Table to act like SQL don't add lots of data. If you have lots of data (gigabytes), just don't plan on getting high-performance SQL queries against it. You will be saving money if you don't need those regular SQL features.

When it comes to transactions, it's just the other way around: SQL Azure supports transactions; table storage doesn't.

SQL Azure is basically SQL Server running inside Windows Azure, so if you have an existing application that uses SQL Server, SQL Azure provides a good migration path. However, there are limits to how big a database you can have on SQL Azure (currently 150 GB), so there are limits to how much it can scale.

Table storage, on the other hand, is extremely scalable, but requires a different way of thinking. It's not a relational database. See e.g. this article for a nice introduction: http://msdn.microsoft.com/en-us/magazine/ff796231.aspx

The real answer is, "Try really hard not to use Azure Table Storage". Whenever you move from a relational DB to a no-sql DB, you are of course going to have to change how you think about your storage architecture. But the problems with ATS go way, way beyond just needing to "think differently". As other folks have pointed out, it's not just a "No-SQL" data-store, it's a particularly stunted, handicapped, and very-low-featured instance of a No-SQL store. It's not a matter of needing to "think differently" about ATS; it's a matter of ATS not giving you the tools you need to do your job - tools that other no-sql data stores do give you.

About the only thing good about ATS is that you can put lots and lots of data into it very quickly, and with minimal storage fees. However, you basically can't hope to get that data back out again unless you're lucky enough to have a use-case that magically matches its Partition-Key/Row-Key storage model. If you don't - and I suspect very few people do - you're going to be doing a lot of partition scans, and processing the data yourself.

Beyond that, Azure Table Storage seems to be at a dead-end in terms of development. If you look at the "Support Secondary Indexes" request on the Azure feedback forums (http://feedback.windowsazure.com/forums/217298-storage/suggestions/396314-support-secondary-indexes), you can see that support for Secondary Indexes was promised as far back as 2011, but no progress has been made. Nor has any progress been made on any of the other top requests for Table Storage.

Now, I know that Scott Guthrie is a quality guy, so my hope is that all this stagnation on the Table Storage front is a preface to Azure fixing it and coming up with something really cool. That's my hope (though I have zero evidence that's the case). But for right now, unless you don't have a choice, I'd strongly recommend against Azure Table Storage. Use Azure SQL; use your own instance of MongoDB or some other No-SQL DB; or use Amazon DynamoDB. But don't use Azure Table Storage.