DocumentDB vs Azure SQL vs Azure Table

Microsoft Azure is now offering a third storage option for developers: DocumentDB.  DocumentDB is a no-sql storage service that stores JSON documents natively and provides indexing capabilities along with other interesting features. Microsoft Azure also offers Azure Tables, another no-sql storage option, and Azure SQL, which is a relational database service. This blog post provides a very high level comparison of the three storage options.

Storage

Purely from a storage perspective, the only relational storage offering is Azure SQL. This means that only Azure SQL will provide a schema and a strongly typed data store. This gives Azure SQL some interesting advantages related to search performance on complex indexes. Both Azure Table and DocumentDB are no-sql storage types, which means they do not enforce schema on their records (a record is called an Entity in Azure Table, and a Document in DocumentDB). However DocumentDB stores its data as a native JSON object, and allows attachments to a document, while Azure Table stores its data in XML (although you can retrieve Azure Table entities as JSON objects). The advantage of storing native JSON objects is that the data maps directly into JSON objects in the client application code. It is also worth noting that Azure SQL comes with storage capacity limitations; it can only store up to 500GB of data in a single database.

Indexing

All three options provide a form of indexing. Azure SQL offers the most advanced indexing capability allowing you to define indexes with multiple columns and other advanced options (such as a WHERE clause as part of the index filter). DocumentDB offers automatically generated indexes allowing queries to efficiently retrieve information. Azure Tables offer only a single index on its PartitionKey; this means that querying on properties other than the PartitionKey can take a significant amount of time if you have a lot of data.

Programmability

Azure SQL provides advanced server-side programming capabilities, through triggers, column-level integrity checks, views and functions (UDF). The programming language for Azure SQL is T-SQL. DocumentDB offers a similar programmability surface using JavaScript, which provides a more uniform experience for developers. Azure Tables do not offer server-side programmability.

Referential Integrity and Transactions

All three options provide some form of data consistency, although limited for Azure Tables. Azure SQL offers full-blown RDBMS referential integrity and transactional support; with T-SQL developers can nest transactions server-side and perform commit or rollback operations. And Azure SQL offers strong Referential Integrity through foreign keys, unique constraints, NOT NULL constraints and more. DocumentDB does not offer strong Referential Integrity (there is no concept for foreign keys for example) but supports transactions through JavaScript. As a result, developers can use JavaScript to enforce server-side referential integrity programmatically. Azure Tables offers basic transaction support through the use of its BATCH operation, although there are a few stringent requirements: up to 100 entities can be batched together, and all must share the same PartitionKey.

Service Cost

Azure SQL pricing recently changed and is now driven by the level of service you desire, which makes it difficult to compare with other storage options. Assuming a Basic level of service and a relatively small database (up to 2GB), you can expect to pay roughly $5.00 per month starting Nov 1st 2014. Pricing of Azure SQL goes up significantly with higher levels of service, and can reach over $3,000 per month for the more demanding databases (the price point impacts the maximum size of the database and the throughput available). DocumentDB offers a starting price point at $45.00 per month per Capacity Unit, which represents a storage quantity and specific throughput objectives (note: the price for DocumentDB is estimated since it is currently in preview). Azure Tables only have a storage price point which makes this storage option very affordable. A 1GB storage requirement with Azure Tables will cost about $0.12 per month with high availability and geo-redundancy.

All the storage options incur additional charges for transactions, which is not included in this analysis to simplify the comparison.

Summary

The three storage options provided by Microsoft Azure (Azure SQL, DocumentDB and Azure Tables) provide an array of implementation options at various price points depending on the needs of your application. Azure SQL provides higher data integrity but is limited in storage capacity, DocumentDB offers a no-sql implementation with configurable consistency levels and JavaScript server-side logic, and Azure Table offers simple no-sql storage capacity.

The following table is my interpretation of the Microsoft Azure storage options used for storing objects or tables.

 

Storage

Indexing

Server-Side Programmability

Ref. Integrity and Transaction Support

Service Cost

Azure SQL

Database

Up to 500GB

Yes

Multi-column

Yes

T-SQL

Yes for R.I.

Yes for Tx Support

Starts at $5 / DB / Month

(DB = Database)

DocumentDB

JSON

Petabytes

Yes

Automatically created/maintained

Yes

JavaScript

Through JavaScript

Supports Tx

starts at $45 / CU / mo

(CU = Capacity Unit)

Azure Table

XML

200TB

Primary Key only

No secondary index

No

Limited

(uses BATCH operation)

$0.12 per GB

 

NOTE 1: This blog post was updated on 9/11/14 at 9:48pm ET to correct an inaccuracy on DocumentDB indexing.
NOTE 2: This blog post was updated on 9/14/14 at 6:37pm ET to correct a typo.

About Herve Roggero

Herve Roggero, Microsoft Azure MVP, @hroggero, is the founder of Blue Syntax Consulting (http://www.bluesyntaxconsulting.com). Herve's experience includes software development, architecture, database administration and senior management with both global corporations and startup companies. Herve holds multiple certifications, including an MCDBA, MCSE, MCSD. He also holds a Master's degree in Business Administration from Indiana University. Herve is the co-author of "PRO SQL Azure" and “PRO SQL Server 2012 Practices” from Apress, a PluralSight author, and runs the Azure Florida Association.

Print | posted @ Thursday, September 11, 2014 5:15 PM

Comments on this entry:

Gravatar # re: DocumentDB vs Azure SQL vs Azure Table
by Scott Prokopetz at 9/12/2014 2:22 PM

Good article. However, I believe you have an error in the first sentence of your second paragraph. You indicate that "the only no-sql storage offering is Azure SQL" ... should that be "the only SQL storage offering"??? since Azure SQL is SQL and not no-sql?
Gravatar # re: DocumentDB vs Azure SQL vs Azure Table
by Herve Roggero at 9/14/2014 6:39 PM

Thank you for raising this typo. It is now fixed.
Gravatar # re: DocumentDB vs Azure SQL vs Azure Table
by Aa at 9/20/2014 1:31 AM

You can now use sql to query documentdb!
Comments have been closed on this topic.