SQL Azure or Table Services - which to choose?

Question

Hi All

I've been reading about the differences between SQL Azure and Table Services.  It's easy to get bogged down in performance specs and API nuances.  A simple high-level discussion of pros and cons would help me choose between the two.  These questions come to mind:

1. Which is easier to get up and running with?  I'm assuming the majority of us are already familiar with SQL Server and thus SQL Azure will be an easier transition.  Although Table Services appears to be a simpler system in general, I'm concerned there may be more requirements at the application level.  For instance I read that it doesn't enforce schema, so this kind of thing is another layer of code required in my application that previously I can manage with SQL Mngt Studio.   
    
2. Does Table Services really measure up to SQL Azure for real-world apps?  Obviously the two are completely different technologies and feature sets, but ultimately they store data for consumption.  In the real-world am I better off sticking with SQL Azure for tried-and-true data storage?  Surely the answer to this will differ depending on the complexity or flatness of the schema.  In my case I will consume a relatively simple schema with asp.net and Silverlight (I will also store large image files in BLOB storage)   
    
3. Which scales better?  Here the discussion tends to get very technical, but on a simple level the answer I look for is in which system will I hit the capacity ceiling first, and when I do, how painful is it to grow?  For some reason I have taken from context that Table Services spreads more easily over different storage nodes while a SQL Azure db does not.  Is that incorrect?  I don't want to run into a situation where my db is maxed out but do to a SQL Azure ceiling the db now has to be partitioned or all the data moved into some other system altogether.  
    
4. Which is more expsenive?  Of course I can study the individual pricing plans, but in general is it considered less expensive to develop against Table Services?  It will be a judgement call because to go through the learning curve to develop against Table Services might mean a cheaper product for my customers (or more profit for me) but if the savings are not significant enough I may prefer the SQL Azure environment I'm already familiar with.  
    
5. What happens when I want to move my from SQL Azure (or SQL Server in-house) to/from Table Services?  I'm used to detaching and attaching dbs to move them around bewteen servers but how is this handled in Table Services?  Things change and if my business model changes I want to know I can get my data off of Table Services and ready for re-use in another system (SQL Server in-house, etc) with a minimum of headache.   
    
6. Which is better for interacting with BLOB Services? Lacking filestream support in SQL Azure I gather I'll need to store large files in BLOB Services.  This much will apply to Table Services as well.  Is it a more complicated setup in terms of code and initial service provisioning to use SQL Azure with BLOB services?

Thanks for your thoughts.
Mark

Answer 1

Hi

1. Sql works like you are used to SQL. Table are a bit harder to start with and is a different kind of storage.  That implies a different kind of architecture.  But the stoarge itself is plain easy.  Store your object in a container.  That's it.
2. Table storage is "more" real world than SQL.  When azure was launched there where no plans to put SQL Storage online too.  If you have a small application or website, SQL Storage might be enough.  If you want to store terabytes of data, you are obligated to move to blob & tables (and queues).
   Using Blobs is a MUST have, don't use blob's in azure SQL.   Blobs are fast AND cheaper!
3. Which scales better: tables.  You have hard limits for Azure SQL (up to 100 GB).  So, if you know upfront you have more data to consume don't choose SQL or start sharding (dividing your data into multiple databases). Plus, you have SQL Throttling. If multiple instances are hammering Azure SQL, your statements will be throttled. 
4. Most expensive: SQL.  You pay about $1 per GB of SQL db.  For table storage you pay only $0.15 per gigabyte.  But you need to include transactions in your calculations. You pay also a $0.03 per 25000 transactions on table storage.  Just to keep you away from retrieving all data you don't need (or force you to dig into some sort of caching mechanism).
   If designed well tables are much cheaper and faster
5. Moving data between sql and table...  You will have to migrate data yourself into (or from) a new table structure.  As far as i know: don't count on a simple migration path.
6. Tables and blobs is both storage.  Blob is just a chunk of binary data while tables are structured data.  Setting up blob storage is pretty easy + you have the advantage that your blob can be accessed by using a direct URL or even CDN (if you want to).

Hope this helps

Kind regards

Damiaan

---

twitter: @dampeebe

Answer 2

A couple corrections:

• SQL Azure's limit is 50GB per database, not 100GB. (But you can have many databases.)
• A 1GB database in SQL Azure costs $10, not $1.
• Transactions in Windows Azure storage (blobs, tables, and queues) are $0.01 per 10,000 transactions, so 25,000 transactions would actually cost $0.025.

Answer 3

Ah, what was i writing?  Thanks Steve!

Yet ... the calculator: http://www.microsoft.com/windowsazure/pricing-calculator/ (already) advertises 100 GB of relational database for $ 999/month.

 

---

twitter: @dampeebe

Answer 4

That's great information!  Thanks for the replies. 

So it sounds like from a cost and scalability perspective it's well worth figuring out how Table Services works.  From this discussion it appears the only possible drawback is no simple migration between tables and a SQL db so once a developer commits to tables, get used to it, though I'm sure a third-party will devise a migration sooner or later.  Who knows, maybe SSIS will get some Azure datasource controls one day. 

What about LINQ, Entity Framework, etc with Table Services?  Do these technologies use Table Services as a data source just as they do with an SQL database or am I looking at a whole new mechanism for querying data? 

Can anyone suggest a good "Table Services for the SQL Server developer" learning resource? 

Thanks!
Mark

Answer 5

There are a couple of SQL Azure benefits that got short changed in this thead:

- queries

- transactions

The only index on a Windows Azure Table is on PartitionKey & RowKey. There are no secondary indexes. Consequently, there can be a bit of messing around with table content to take account of that. There is only limited transaction support. Entities in an entity-group transaction must be in the same table and have the same PartitionKey.

I did a post a while back on queries with Windows Azure Tables.

Answer 6

Hi,

I will mark the reply as answer. If you find it no help, please feel free to unmark it and follow up.

Thanks.

---

Wenchao Zeng
Please mark the replies as answers if they help or unmark if not.
If you have any feedback about my replies, please contact [e-mail].
Microsoft One Code Framework