Microsoft Azure Storage vs. Azure SQL Database
Windows Azure storage is just like as any NoSQL storage. It works in high-scale scenarios for us (pumping millions of records per user). However, classical CRUD approach is a bit hard to scale or to adapt to this.
I would recommend to start looking along the CQRS style of architectures. Here are some references that might get you started:
- Open Source Lokad.CQRS project for Windows Azure (patterns and guidance included)
- Efficient architectures for Azure discussion at MSDN
Depends on what kind of data you are talking about - generally there is a tendency to overestimate transactional data requirements. A lot of data can really be fit into a 1 GB SQL Azure (we are a SAAS provider and transactional data of almost 20 clients can fit into that much space). Also, for some strange reason, I have seen that SQL Azure space consumption seems to be somewhat lesser than the size of the database I see on-premises (might have to do with how they handle logs, not sure). And now 50 GB is the limit, which is quite frankly, HUGE.
However for this, you also need to consider what increases the space usage - storing images, videos or other large objects in the database can create considerable increase in space consumption. It is better to keep these kind of objects in Windows Azure.
So short answer - keep transactional data in SQL Azure and non-relational data in Windows Azure. Working with SQL Azure will also keep your developers more productive, since it is quite familiar in terms of programming. Treat Windows Azure similar to how you would treat windows local file storage with some added benefits (basic table structures supported).
Another thing to consider is the number of transactions that you will be sending/receiving from the store. The nice part about SQL Azure is that it is a fixed cost / month and if you are doing the queries within the same data center (i.e. from a Windows Azure Web role located in the same data center as your SQL Azure database) then there are no extra charges.
Even though the cost of the transactions to the Windows Azure stores are pretty low, it is something that has the potential of adding up if you do enough of them.