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.