On choosing between Azure Database and Table Storage

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

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

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
Relational data yes no
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
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s