Microsoft Azure Storage vs. Azure SQL Database

Question

I saw that there was a similar question asked several months back, but it really didn't address my situation well. Here it goes...

I'm in the process of building from scratch a web-based, .NET application that has the potential to become a high-volume site (several hundred thousand page views a month to start) and am strongly considering using Microsoft Azure to host it. I have not built anything yet and am still researching my different options.

The application itself is, at its core, a standard CRUD application that acts upon a number of different types of entities (e.g. user, order, item, etc.). There are probably some background processes that may be running and some queuing of data (for non-realtime updates -- like getting a SO badge, for example), but most of the interactions with the user will be your typical CRUD type of actions.

Regarding Azure, I have read a number of articles about using Microsoft Azure Storage to store transactional data and am strongly considering doing that instead of using Azure SQL DB. However, I haven't seen or read a number of success stories of real people and/or real companies doing that. So I thought I'd reach out to the SO community to see if anyone has had any experience with using Microsoft Azure Storage, what kind of luck have you had, any gotchas I should look out for, and any best practices that you've come up with.

I've read through a lot of the Microsoft Azure MSDN section and the programming Microsoft Azure Table API document from Microsoft. I'm looking for practical advice, lessons learned, best practices, etc. Thanks in advance!

Karlmarxstadt

Answer 1

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

Aftereffect

Thanks for the info. I took a quick scan over some of the information you provided (I'll look more in depth later this weekend). One question - with the projects/products you've worked on, did you use SQL Azure in conjunction with Azure Storage, or did you just use Azure Storage? It looks like some of the docs you linked to showed SQL Azure being used with Azure Storage. If you used SQL Azure, was that used for the command side of CQRS and then moved to Azure storage? Thanks again. – Karlmarxstadt

This is some good information and gets me pointed in the right direction. Thanks! – Karlmarxstadt

We are using SQL Azure with Azure Storage on the command side. SQL Azure for the relational data, Azure storage for the blobs, queues and view data. Theoretically SQL Azure could be replaced with event sourcing, but we didn't get there, yet. – Aftereffect

Good answer! I would also add that part of the decision making is psychological...it's harder to step out of established patterns, especially when there's so much community material on standard SQL dependent 3 tier architectures. When going the Azure Tables route I felt like I was taking a risk, but in retrospect and with the performance benefits of CQRS approaches, and with the pricing, now I feel totally comfortable with it. – Phototypography

Answer 2

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).

Sapheaded

thanks for the perspective. When you use Windows Azure for storing non-transactional data, do you use a combination of table storage and blobs? Do you use drives at all? Thanks! – Karlmarxstadt

We use the first two - drives is pretty new, not yet found a great place to use them. Missed this above, but azure tables are great for writing logs, especially usage logs. Blobs are great for images, videos and other large files Drive is when you need to have something similar to local hard drives. However these are instance specific (means only one instance can write to a drive at a time) and not like azure storage which can be accessed by multiple instances at the same time. – Sapheaded

Answer 3

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.

Gleaning

Answer 4

You should also check your data usage pattern before deciding to use Azure Storage or SQL storage. Since Azure Storage is providing NoSQL solutions they are more geared towards non reporting base requirements. Here reporting does not mean reports but it implies that querying capabilities of the Azure storage is limited\ non optimize for various querying scenarios. With CQRS architecture the CRUD and Reporting operations are decoupled and hence a mix n match of both Azure Storage and Azure SQL can be done.

Sanskritic

Thanks. This helps, too. I was considering using a mix & match between table storage and sql azure. I'm not quite sure how much to keep in sql azure -- has your experience basically had a duplication of data between sql azure and azure storage? – Karlmarxstadt

It's not about how much to keep where. For example if you are implementing a User Authentication\Authorization scenario all data access pattern would revolve around a particular user always and NoSQL works best here. But within the same if we want a Admin interface to manage users, which involves showing user list,filters etc that is best suited for relation format of data. In this case you would keep duplicate copies of user data in both NoSQL and SQL stores but only trust the NoSQL db for performing any business validation. We would need some mechanism to sync the user data store (One way) – Sanskritic

Hence you need to think whether this complexity is warranted for your application. – Sanskritic

Answer 5

Another place to find information is to step outside of the Windows Azure options and look at AWS's. The S3 & SimpleDB options have been vetted over a longer period of time and have many additional success stories out there on the web. However, S3 & SimpleDB are very similar in functionality to Windows Azure Storage Table and Blob Storage. If you're thinking about truly big data, which these structures are for, definitely check out the AWS options. If only for a reference point on existing solutions built around big data.

As for SQL Azure, it is great for lots of transactions, keeping transaction costs low, and maintaining relationships and general integrity based on relational data. However, if you are going to have massive volumes of data, just go ahead and aim for the big data structures, such as Windows Azure Table or Amazon's SimpleDB.

Meredeth