How often does this happen – your teammate has just started developing a new service and they need to store some data in the cloud. “I need a database, therefore I’ll use SQL Server”. All too often relational databases are introduced into an architecture without any thought being given to type of data that is being stored or the way it will be accessed.
When building on Widows Azure you’ve got access to a number of database technologies including SQL Database, Table Storage and DocumentDB. Changing your storage technology can be a very costly exercise so its wise to spend the time upfront to consider where you data should really live.
SQL Database a relational database-as-a-service offering. Its great for storing relational data and supports a rich complex query operations. A single SQL Database can store up to 500GB of data. All of the database concepts are very familiar to anyone that has worked with SQL Server. SQL Database also offers features that enable migration, export, and ongoing synchronization of on-premises SQL Server databases with Azure SQL databases (through SQL Data Sync). If you need transnational support for batch operations then SQL Azure is the way to go.
But…. all of this it comes at a price! when compare to table storage SQL Database is far more expensive.
Table storage is a fault-tolerant NoSQL key-value store database.It comes in handy when you need to store very large amounts of non relational data. Where SQL Database has an upper limit of 500GB, Table storage maxes out at 200TB per table. The querying ability of table storage is much less rich. You store data by partition key and row key. Its not possible to join tables so you always need to know the partition key to work with.
Check out this great blog post by Troy Hunt where he explains how he works with 150 million rows of data using table storage.
Technology Selection Considerations
you should first consider using table storage when..
- you need to store terrabytes of data and cost if a factor
- there are no complex relationships in the data that require server side joins and secondary indexes
- you need to store unstructured data or the structure of every object may be different
- you plan to scale out without sharding
you should first consider using SQL database when…
- you are storing highly structured, relational data
- you need to store less than 500GB of data
- you need to access and query the data in many different ways
- you want to be able to run your application on premise and in the cloud
Key Feature Comparison
|Criteria||SQL Database||Table Storage|
|Transaction Support||yes full ACID transactions||very limited|
|Table Schema||fixed||each row can have different properties|
|Maximum Storage||500GB per database||200TB per table|
|REST client support||yes||yes|
|Availability SLA||99.9% per month||99.9% per month|