Cosmos DB vs Redis vs SQL Server: Polyglot Persistence
On this page
Learning Objective
Decide when a second data store earns its cost, use Cosmos DB for partitioned document workloads and Redis for fast, ephemeral structures, keep stores consistent without cross-store transactions, and secure each store with its own identity and network rules.
Concept Explanation
SQL Server can do a great deal: relational data, JSON columns, full-text search, graph tables, temporal history, and columnstore analytics. Polyglot persistence means using more than one kind of data store, each for the workload it suits, and it is easy to adopt for fashion and expensive to live with. Every additional store adds another thing to secure, back up, monitor, migrate, learn, and keep consistent with the rest. The honest default is to stay on one relational database until a measured need appears, such as a workload that needs single-digit millisecond reads at very large scale, a partitioned key-value access pattern, or a data structure the relational model handles badly.
Azure Cosmos DB is a globally distributed document database that scales by partitioning. The most important design decision is the partition key: data with the same key lives together, single-partition queries are cheap, and queries that span partitions are slower and cost more. Capacity is measured in request units, so every operation has a price, and a point read by id and partition key is the cheapest. A key that concentrates traffic, such as the current date or a single large tenant, creates a hot partition that throttles while the rest sits idle. Cosmos DB fits event-like or per-user documents that are read by key, and it fits poorly for ad hoc joins and reporting, which relational databases handle far better. Its change feed is a useful way to react to writes.
Redis is an in-memory data structure server. It is excellent for caching, counters, rate limits, leaderboards in sorted sets, short-lived tokens, and pub/sub, and Day 67 used it as a cache. Treat it as fast and ephemeral, not as the source of truth, unless you have deliberately configured persistence and accepted the trade-offs. Its atomic commands, such as INCR and Lua scripts, are the right tool for shared counters, since a read followed by a write from the application would race, as Day 112 showed.
Once data lives in two stores, there is no transaction across them. The same outbox pattern from Day 66 keeps a secondary store in step with the primary, and each secondary should be rebuildable from the source of truth. Security is per store. Cosmos DB accounts have account keys that grant full access, so prefer Microsoft Entra role-based access with managed identity and disable key-based authentication where you can, and restrict the network with private endpoints. Redis has historically shipped without authentication enabled by default in some setups, and instances exposed to the internet have been abused for years, so require authentication and TLS and never expose it publicly. Encrypt at rest, control who can read backups, and apply data retention rules to every copy of personal data, including caches and secondary stores.
| Workload | Good fit | Notes |
|---|---|---|
| Relational data, joins, reporting, transactions | SQL Server | The default, does more than most teams expect |
| Huge scale, access by key, flexible documents | Cosmos DB | Choose the partition key carefully, budget request units |
| Counters, rate limits, leaderboards, cache | Redis | Ephemeral, use atomic commands |
| Files and large binary objects | Blob Storage | Store references in the database |
| Full-text and relevance search | Search engine | Fed from the database via outbox |
| Sessions in a scaled-out app | Distributed cache or the database | Keep the BFF stateless where possible |
Worked Example: A Partitioned Cosmos Container, Redis Atomics and Safe Configuration
The Cosmos code stores per-user activity items partitioned by user. The Redis code counts and ranks atomically, and the last block shows keyless authentication.
// Cosmos DB: partition key /userId. All of one user's items live together, so the common query stays in one partition.
public sealed record ActivityItem(string id, string userId, string type, DateTimeOffset at, string summary);
var container = cosmos.GetContainer("app", "activity");
// A point read (id + partition key) is the cheapest operation, about one request unit for a small item.
var item = await container.ReadItemAsync<ActivityItem>(id, new PartitionKey(userId), cancellationToken: ct);
// A query scoped to one partition: never accept the partition value from the client, take it from the authenticated user.
var iterator = container.GetItemQueryIterator<ActivityItem>(
new QueryDefinition("SELECT * FROM c WHERE c.userId = @u AND c.at >= @from ORDER BY c.at DESC")
.WithParameter("@u", userId).WithParameter("@from", DateTimeOffset.UtcNow.AddDays(-30)),
requestOptions: new QueryRequestOptions { PartitionKey = new PartitionKey(userId), MaxItemCount = 50 });
// A poor key would be the date: today's writes would all land in one partition and be throttled.// Redis: shared counters and rankings use atomic server commands, never read-modify-write in the app.
var db = redis.GetDatabase();
// A per-user daily action limit, atomic across all server instances.
var key = $"limit:export:{userId}:{DateTime.UtcNow:yyyyMMdd}";
var count = await db.StringIncrementAsync(key);
if (count == 1) await db.KeyExpireAsync(key, TimeSpan.FromHours(25)); // set the expiry when the key is first created
if (count > 20) return Results.StatusCode(StatusCodes.Status429TooManyRequests);
// A leaderboard in a sorted set.
await db.SortedSetIncrementAsync("board:week", userId, points);
var top = await db.SortedSetRangeByRankWithScoresAsync("board:week", 0, 9, Order.Descending);// Keyless access with managed identity. No account key or connection string secret exists to leak.
var cosmos = new CosmosClient(config["Cosmos:Endpoint"], new DefaultAzureCredential());
// Redis with authentication and TLS required, reached over a private network, and the connection from configuration.
var options = ConfigurationOptions.Parse(config["Redis:Connection"]!);
options.Ssl = true;
options.AbortOnConnectFail = false;Decision check before adding a store:
1. What measured problem does SQL Server fail to solve, with numbers?
2. Who owns operating, securing, backing up and monitoring the new store?
3. How is it kept consistent with the source of truth, and can it be rebuilt from it?
4. How does personal data in it get deleted on request?
If any answer is vague, stay on SQL Server.The checklist matters more than the code. Most teams that add a second store find that the operational and consistency costs outweigh the benefit for years, and a few well-chosen indexes would have solved the original problem.
Real-World Examples
- In August 2021 researchers at Wiz disclosed ChaosDB, a vulnerability in a Cosmos DB feature that could have exposed account keys of many customers, and Microsoft notified affected customers and advised them to regenerate keys. The episode is often used to argue for identity-based access, network restriction, and disabling static keys.
- In 2015 the author of Redis, Salvatore Sanfilippo, wrote publicly about attacks where unauthenticated Redis servers exposed to the internet were used to write files such as SSH keys onto the host, and Redis added a protected mode in version 3.2. Exposed Redis instances have continued to be used for cryptocurrency mining, so authentication and network isolation remain essential.
Review Questions & Answers
It determines where data is stored and how much a query costs, since queries within one partition are cheap and cross-partition queries are slow and expensive. A key that concentrates traffic causes a hot partition that is throttled while others sit idle.
With several server instances, two requests can read the same value and both write back the same increment, losing a count. INCR is atomic inside Redis, so every request gets a distinct value regardless of timing.
Write an outbox message in the same transaction as the source change, and let a worker apply idempotent updates to the secondary store. Because it can lag, it should be treated as a derived copy that can be rebuilt from the source of truth.
A key grants full access and must be stored, rotated, and protected, and it can leak through code, logs, or a vendor vulnerability. Identity-based access uses short-lived tokens with scoped roles and leaves no long-lived secret to steal.