Choosing the Right Data Store in Azure: SQL DB, SQL IaaS, DocumentDB, or Azure DW?

Shannon Lowder

Shannon Lowder

— 3 min read

By mid-2012, the Azure data platform had expanded past "SQL Azure or a VM." DocumentDB had launched (Microsoft's NoSQL offering, later renamed Cosmos DB). Azure SQL Data Warehouse was on the roadmap. The question was no longer just PaaS vs IaaS — it was which data store fit the workload. I was having this conversation with clients regularly enough that I built a decision framework to run through it consistently.

The Workload Taxonomy

Before picking a store, classify the workload. Four categories that covered most of what I saw in practice:

  • Transactional (OLTP) — high-frequency reads and writes, small row counts per operation, ACID requirements, relational data model with foreign key integrity. Application databases, CRM systems, order processing.
  • Analytical (OLAP) — complex aggregations over large row sets, low write frequency, columnar access patterns, tolerance for eventual consistency on some operations. Reporting databases, data marts, historical analytics.
  • Document / semi-structured — variable schema, hierarchical or nested data, document retrieval by key or property queries, schema evolution over time. Product catalogs, user preferences, event logs.
  • Mixed / staging — intermediate processing stages that don't fit cleanly into any category. Usually temporary and highly volatile.

Azure SQL Database (SQL Azure) — Transactional Workloads Under ~150GB

Best fit: OLTP workloads where the application is written for SQL Server, the data model is relational, and the team wants managed HA without operating a VM. The 2012 limits that still mattered: 150 GB max per database, no cross-database queries, no SQL Server Agent, no CLR by default.

If your workload fit within those constraints: use SQL Azure. The operational simplicity was real and the cost was competitive with equivalent IaaS once you factored in OS and SQL Server license costs.

SQL Server on Azure IaaS VMs — Full Feature Surface or Scale Requirements

Best fit: workloads that needed the full SQL Server feature set (Agent, CLR, linked servers, full-text search), databases over 150 GB, or workloads where the team needed to tune at the OS level. Also the right answer for workloads where you were bringing an existing on-prem SQL Server and the migration cost of adapting to SQL Azure's constraints wasn't justified.

The trade: you own the operational surface. Patching, storage configuration, HA setup (AlwaysOn AGs, FCI), monitoring. For a team with DBA expertise, this was fine. For a team that wanted to focus on application development and not infrastructure operations, it was a burden.

DocumentDB — Document Workloads Requiring Schema Flexibility

DocumentDB was new in 2012 and positioned for workloads where the data model was document-centric rather than relational. The cases where it genuinely made sense:

  • Product catalogs where different product types had different attribute sets — a relational model required either a wide nullable table or an EAV anti-pattern; a document store let each product document carry its own attributes natively
  • User preference and configuration storage — highly variable, rarely joined, read by key
  • Event logs and telemetry — high write throughput, schema evolution over time, queries primarily by time range or event type

Where DocumentDB was the wrong answer: anything requiring cross-document transactions, complex multi-entity joins, or strong relational integrity. The consistency and query model was different enough from SQL that teams used to relational thinking needed a genuine mental model shift, not just a syntax change.

Azure SQL Data Warehouse — Large-Scale Analytics (Roadmap in 2012)

Azure SQL Data Warehouse wasn't generally available in mid-2012, but it was clearly coming and worth understanding conceptually. The pitch: massively parallel processing for analytics workloads at scales where SQL Azure and SQL Server on VMs hit their ceilings. Columnar storage, distribution keys, replicated dimension tables — the analytical SQL Server patterns scaled up to hundreds of terabytes.

In 2012, most clients weren't at this scale. The interesting evaluation question was whether data that would eventually need a DW should start in SQL Azure (with a migration path later) or in a DW-oriented schema from the beginning. For clients with known growth trajectories, thinking about the migration path early was worth the conversation.

The Decision in Practice

Most decisions in 2012 came down to SQL Azure vs SQL Server IaaS for the transactional layer, with DocumentDB as a consideration for specific components (usually one or two per architecture, not the whole thing). The "use the right tool for the job" framing was technically correct but practically exhausting — every new store added operational surface area and team learning curve.

My default recommendation: start with SQL Azure for new transactional workloads unless you have a specific blocker (feature gap, size constraint, compliance requirement). Defer the exotic stores until you have a clear problem that the default store genuinely can't solve. Premature polyglot persistence is its own form of technical debt.

If you're evaluating data stores for a specific workload and want to think through the decision, I'd love to hear what you're trying to build. As always, I'm here to help.

Read more