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?
Answer
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.