Azure Cosmos DB for a SQL DBA: vocabulary, APIs and when to use it
Learning notes: account, database, container, item, partition key, RU, the six APIs and choosing between Cosmos DB and Azure SQL.
I'm taking a training on Azure Cosmos DB and, coming from SQL Server and Azure SQL, my first hurdle was vocabulary: familiar words (database, index, transaction) that don't quite mean the same thing here. These are my learning notes, based on Microsoft documentation, with a "SQL Server side" mapping table.
What I take away: the resource hierarchy
flowchart TD
A["Cosmos DB account (cosmos-contoso-dev)"] --> B["Database (AppDb)"]
B --> C["Container (orders)"]
C --> D["Item (JSON document)"]
C --> E["Partition key (/customerId)"]
C --> F["Throughput: RU/s"]
C --> G["Indexing policy"]
- Account: the Azure resource (with a unique DNS name) that carries global distribution, the default consistency level and the backup mode. You can create up to 250 accounts per subscription by default (the limit can be raised through support).
- Database: per the docs it is "similar to a namespace", just a group of containers. Nothing like a SQL Server database with its files and log.
- Container: where data is stored. It carries the partition key, throughput, indexing policy and TTL.
- Item: the record (a JSON document in the NoSQL API). Each item has an
idthat is unique within its logical partition. - Partition key: the property that decides where the item lives. A key design decision you can't change in place (see the dedicated article).
- Request Unit (RU): the "currency" that measures the cost of every operation (read, write, query). Reading a roughly 1 KB item by its
idand partition key costs 1 RU.
Mapping to SQL Server
| Cosmos DB | Roughly the SQL equivalent | Watch out |
|---|---|---|
| Account | Logical server / instance | Also carries multi-region replication |
| Database | Schema / namespace | No files or log to manage |
| Container | Table | Schema-agnostic: several item shapes allowed |
| Item | Row | JSON document, no typed columns |
| Partition key | Sharding key (not an index) | Immutable, drives scale-out |
| RU/s | vCores / DTU | A normalized unit of CPU + IO + memory |
| Indexing policy | Indexes | Everything is indexed by default |
The mental trap: a container is not a normalized table. The documentation instead suggests colocating entities that are read together (for example a customer and their orders) in one container, as long as they share the same partition key.
The APIs
A Cosmos DB account is created for one API, and the docs recommend using a single API per account to access the data.
| API | Who it is for | Container vocabulary |
|---|---|---|
| NoSQL | New applications, SELECT ... FROM c queries |
Container / Item |
| MongoDB | Migrating a MongoDB app (wire protocol compatible) | Collection / Document |
| Apache Cassandra | CQL applications | Table / Row (database = keyspace) |
| Apache Gremlin | Graphs | Graph / node or edge |
| Table | Modernizing Azure Table Storage | Table / Item |
| PostgreSQL | Distributed PostgreSQL | see below |
Heads-up: the docs state that Azure Cosmos DB for PostgreSQL is on a retirement path and is no longer recommended for new projects. For distributed PostgreSQL they point to the Elastic Clusters feature of Azure Database for PostgreSQL.
What I retain: not every feature exists in every API. For example, hierarchical partition keys are only supported in the NoSQL API, so I always check the docs for the API I target.
Cosmos DB or Azure SQL?
My beginner's summary (these are my notes, not an official rule):
- Cosmos DB makes sense for: data mostly read by a known key, volume or throughput that must scale out, multi-region distribution with low latency, JSON documents of varying shape.
- Azure SQL remains the right choice for: a relational model with many joins, ad hoc reporting, multi-table transactions, a constrained schema, T-SQL and the existing ecosystem.
The question to ask: "are my accesses almost always through the same key?". If yes, Cosmos DB is a candidate. If not, I stay relational.
Lessons learned
- Don't migrate a relational database table by table into containers: you lose joins and you pay for it in RUs.
- Cost is a throughput budget (RU/s), not vCores: you have to learn to read it (see the RU article).
- The partition key is design decision number one.
- Keep personal or confidential information out of database, container and tag names: the docs say these fields aren't treated as customer data.