Skip to content

SQL Server and Cosmos DB, side by side

SQL Server and Cosmos DB, side by side

One page puts the two stores this application runs on next to each other, Azure SQL Database on the left and Azure Cosmos DB on the right, row by row: the same bid at rest in both, the same write in both adapters as live code, the numbers the records measured, the record that decided each row, and the reading behind it. Read ADR: Cosmos DB, explained for someone who knows SQL Server first; it teaches the vocabulary, and this page is the comparison a reader makes after learning it.

In plain words

The site runs on two kinds of database at once: a relational one (Azure SQL Database) and a document one (Azure Cosmos DB). This page sets them side by side, from a bid at rest to the monthly bill. Measured here, the gap between them is distance to the server; with that taken out, they answer in the same time.

What that is worth: a developer who knows one of these databases well can learn the other from one page, and the organization can choose between them on numbers measured on its own application.

Why this page exists

TheYard runs on two stores at once, Azure SQL Database and Azure Cosmos DB, each serving one of its two sites, and the question a reader of the records asks first is how the two compare on the same work. That comparison deserves a page of its own, at the top of the sidebar beside Hosting, rather than a place inside the decision records: nothing here was decided, it lays out decisions already made, side by side, with the code and the numbers behind them.

The material existed and was spread across eleven records. The SQL Server side was decided over four days (ADR: The SQL Server backend; ADR: Data first, and the database in source control; ADR: Two providers and a SQL project, explained; ADR: What the database is actually doing), the Cosmos DB side over two (ADR: The partition key; ADR: A second store on Cosmos DB, and what it costs; ADR: The ports learn to wait; ADR: Accounts on a document store; ADR: What the store is actually doing), and the comparison itself over the night between (ADR: Backends, side by side; ADR: Measuring both stores; ADR: Same performance, proven). A reader who wants the two stores next to each other had to hold all of them open. This page is that reading, done once, with the code pulled in live so it cannot drift from what runs, and the diagram on its own page for a screen or an interview.

Who it is for: a developer who knows one of these stores well and is meeting the other, which describes most of the people who will read it. Every row is a question that developer asks first.

The picture

SQL Server and Cosmos DB, side by side: ten rows, Azure SQL Database on the left and Azure Cosmos DB on the right, each box naming the file in this repository that answers it and each number one a record measured

A preview. Open the side-by-side diagram in a new page to zoom in and read across. It is drawn by docs/images/sql-vs-cosmos.mjs from the same facts this page cites, and redrawn when a store, a file or a number changes; a picture that disagrees with the records is a bug in the picture (ADR: Docs and testing).

The same application, both stores

Nothing above the adapters knows which store it is on. The auction in the Application layer speaks to three ports, a vehicle source, a photo manifest source and a bid store, and Identity speaks to a user store; each side implements all four (ADR: The ports learn to wait). One container image runs both adapters in one process, brought up side by side, and each site serves one of them by default: the live site, https://theyard.stevenstout.biz, serves Azure SQL Database, and the second site, https://theyard-cosmos.stevenstout.biz, serves Azure Cosmos DB, with the Store bar linking each to the other at the same page (ADR: One container, both stores; ADR: A permanent address for the second site). So every comparison below is the same code path, the same requests and the same measuring instrument, with only the store changed. That is what makes the numbers comparable, and it is the reason the second store was built as a complete backend rather than a demo (ADR: A second store on Cosmos DB, and what it costs).

Row by row

Each row names the records that decided it. Open one from the sidebar, or follow its address.

The question Azure SQL Database Azure Cosmos DB for NoSQL Decided in
What it is A relational database: tables, rows, foreign keys, T-SQL. The schema is the contract and the engine enforces it. General Purpose serverless on the free limit when this was written, a Basic database on the same server since 14 September; one region from the compute until 20 September and in its region since. A document database: JSON documents in containers, each container split by a partition key. The document owns its shape; the engine enforces the key, the id and the etag. Free tier, in the compute's own region until 20 September and one region from it since. ADR-039, ADR-059, ADR-065
How the application reaches it Entity Framework Core over SqlClient: a DbContext per request, LINQ turned into statements, a managed identity token instead of a password. An interceptor records every statement. The Cosmos DB SDK directly: one CosmosClient for the process, point reads and queries written by hand so the request charge stays visible. Managed identity with a data-plane role; local authentication disabled, so no key exists. ADR-039, ADR-043, ADR-062
A bid, at rest One row in dbo.Bids: PRIMARY KEY (UserId, VehicleId), a foreign key to AspNetUsers with ON DELETE CASCADE, a rowversion the engine maintains. One document in bids: id is the vehicle, the partition key /user_id is the buyer, the etag is the concurrency token. No foreign key exists; the indexing policy excludes every path. ADR-040, ADR-058
Writing a bid Find, then add or update, then SaveChanges: two statements. A DbUpdateConcurrencyException means another writer moved the row; start again from what is there, three tries. Point read, then CreateItem or ReplaceItem with If-Match: two operations, 1 RU plus 5.52 or 10.29. A 412 or a 409 is the same race; the same three tries. ADR-039, ADR-059
Keeping an address unique A unique index on NormalizedUserName refuses the second account. Nothing in the application has to remember to check. No unique index spans partitions. A claim document whose id is the address is created first; a 409 on that create is the refusal, and a failed account write removes the claim again. ADR-061
Indexes, and what a query costs Indexes chosen per query in the DACPAC, a plan the engine picks, and a cost that shows up as compute time against the free limit. An indexing policy per container as JSON, every path excluded here because the site reads by id, and a cost in request units per call. The experiment: 8.84 RU per seeded document under the tuned policy, 16.07 under the default, and nothing bought on any of seven queries. ADR-040, ADR-058
Consistency and transactions ACID across any number of tables in one transaction; row versioning keeps readers off writers; the database is the arbiter. Session consistency; a transactional batch is atomic inside one partition, which is how a reset deletes one buyer's bids as one call. Nothing spans partitions atomically, and the application is arranged so nothing needs to. ADR-060, ADR-065
What the Admin tab shows Every statement with its milliseconds and its parameters by name, on the SQL card and in the console log under Microsoft.EntityFrameworkCore.Database.Command. Every operation with its request units, its milliseconds and whether it named a partition, on the operations card and, since 1.0.0.103, as one console line each under TheYard.Infrastructure.Cosmos.Shared.Stores.CosmosStore. ADR-043, ADR-062, ADR-063
Measured on this application From Missouri, p50, on 8 September before the move: bid write 219 ms, sign in 249 ms, register 319 ms. From the container, in West US 2 until 20 September: bid write 84 ms, and 39 ms of every operation is the round trip to a server one region away. From the App Service plan in West US 3 since: bid write 28 ms, a 1 to 2 ms round trip. From Missouri, p50, on 8 September before the move: bid write 135 ms, sign in 221 ms, register 216 ms. From the container, in West US 2 until 20 September: bid write 10 ms, 6.52 RU, a 2 ms round trip, and every sign-in exactly 2 RU. From the plan in West US 3 since: bid write 89 ms, the same 6.52 RU, a 38 to 40 ms round trip. ADR-064, ADR-067
What it costs a month $4.90 a month at list price since 14 September: the sites run on a Basic database, priced by the day whatever it does. Before that, $0.00 a month on the serverless free limit, 100,000 vCore-seconds and 32 GB a month, which paused when nobody visited and made the first request after a quiet stretch wait for it; the allowance ran out on 14 September. $0.00 a month on the free tier: 1000 RU/s and 25 GB for the life of the account, never paused. Since 19 October both sites are one web app on the one App Service plan, B2 at $24.82 a month since 8 October; from 20 September the second site shared the first site's plan, and before that it had a container group of its own at $34.44 a month. ADR-039, ADR-059

The proof's verdict, from the container itself rather than from a client (ADR: Same performance, proven): on three of eight paths the two stores answer in the same time, and on the other five the difference is the round trip to the store, 39 ms to Azure SQL Database and 2 ms to Azure Cosmos DB; taking one round trip per operation off each side leaves them the same. Those two numbers are a fact about where the compute was. On 20 September it moved to the relational server's region, and the same proof read 1 to 2 ms to Azure SQL Database and 38 to 40 ms to Azure Cosmos DB, with every store-bound row flipped and every request unit unchanged (ADR: One plan, two sites).

The same bid, as it is stored

The row and the document, read from this build. The left declares what the engine will refuse; the right declares which path the engine will index, which here is none, and says why in the file itself.

-- One buyer's standing on one vehicle, and the only table here that changes
-- after startup. One row per buyer per vehicle: a later bid from the same person
-- replaces their earlier one, because what the application asks is "where do I
-- stand", never "what did I bid an hour ago" (ADR: Accounts and per-user bids).
CREATE TABLE [dbo].[Bids] (
    [UserId]     nvarchar(128)  NOT NULL,  -- matches AspNetUsers.Id, which a foreign key has to
    [VehicleId]  nvarchar(64)   NOT NULL,  -- the synthetic id a visitor actually bid on, so the same width as Vehicles.Id
    [Amount]     int            NOT NULL,
    [BidCount]   int            NOT NULL,
    [WonBuyNow]  bit            NOT NULL,
    [AtMs]       bigint         NOT NULL,  -- when it was placed, in the milliseconds every other timestamp on this wire uses
    -- The optimistic concurrency token. Two containers, or two requests that get
    -- past one container's lock, can both read this row and both decide to write
    -- it. rowversion is maintained by the database, so nothing in the
    -- application can forget to move it, and the second writer fails and reads
    -- the row again before it writes, rather than writing over a value it never
    -- saw. It guards one buyer's row; two buyers on one vehicle are two rows,
    -- and the standing they race for lives in each container's memory until the
    -- compare-and-set standing the review record decides (ADR: Three readers
    -- with no memory of the project). A lost update on an auction is somebody's
    -- money.
    [RowVersion] rowversion     NULL,
    CONSTRAINT [PK_Bids] PRIMARY KEY ([UserId], [VehicleId]),
    -- Deleting an account takes its bids with it. Without the cascade a bid whose
    -- account was deleted would stay here forever, be loaded into BidService at
    -- every startup, and count toward a vehicle's standing price on behalf of
    -- nobody.
    CONSTRAINT [FK_Bids_AspNetUsers_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[AspNetUsers] ([Id]) ON DELETE CASCADE
);
GO

-- There is deliberately no foreign key from VehicleId to Vehicles, and no index
-- on UserId alone.
--
-- The foreign key would be a lie: Vehicles holds the 200-row seed, a visitor
-- bids on the 100,000 rows expanded from it in memory, and the constraint would
-- reject 99.8 per cent of legitimate bids. It becomes correct the day the
-- expansion is persisted.
--
-- The index would be a second copy of the first half of the primary key, whose
-- leading column is already UserId: a write on every bid, and nothing earned.

Live from api/TheYard.Database/Tables/Bids.sql, region *, read from this build at 8003063.

{
  "container": "bids",
  "partitionKeyPath": "/user_id",
  "defaultTtlSeconds": null,
  "why": "One document per buyer per vehicle: id is the vehicle id, the partition is the buyer. Every write is a point write and a reset is one query inside the buyer's partition, so no path is indexed.",
  "indexingPolicy": {
    "indexingMode": "consistent",
    "automatic": true,
    "includedPaths": [],
    "excludedPaths": [
      {
        "path": "/*"
      }
    ]
  }
}

Live from infra/cosmos/bids.json, region *, read from this build at 8003063.

The same write, in both adapters

The relational bid store, then the document bid store. Read the two SaveAsync methods against each other: the loop is the same, the exception it catches is the store's way of saying the same thing, and the document side's comment names the relational rule it follows.

/// <summary>Every stored bid, for BidBook to replay into its indexes at startup.</summary>
/// <param name="cancellation">The caller's; the replay's starter lends it, and in a container that is the host.</param>
public async Task<IReadOnlyList<StoredBid>> LoadAsync(CancellationToken cancellation)
{
    using var db = await factory.CreateDbContextAsync(cancellation);
    return await db.Bids.AsNoTracking().Select(row => new StoredBid(
            row.UserId,
            row.VehicleId,
            new BidState(row.Amount, row.BidCount, row.WonBuyNow, row.AtMs)))
        .ToListAsync(cancellation);
}

/// <summary>
/// Every buyer's row on one vehicle. There is no index on VehicleId alone,
/// so this reads the table; it runs only when a vehicle has no standing yet,
/// once per vehicle, on a table of visitor bids.
/// </summary>
/// <param name="vehicleId">The vehicle.</param>
/// <param name="cancellation">The caller's. A table read is the most expensive thing this adapter does, so it is the one most worth stopping.</param>
public async Task<IReadOnlyList<StoredBid>> BidsOnAsync(string vehicleId, CancellationToken cancellation)
{
    using var db = await factory.CreateDbContextAsync(cancellation);
    return await db.Bids.AsNoTracking().Where(row => row.VehicleId == vehicleId).Select(row => new StoredBid(
            row.UserId,
            row.VehicleId,
            new BidState(row.Amount, row.BidCount, row.WonBuyNow, row.AtMs)))
        .ToListAsync(cancellation);
}

/// <summary>
/// The buyer's row and the vehicle's standing in one transaction, the
/// standing first with a compare-and-set on its version. An update that
/// names a version no longer there changes no row, and an insert where
/// another writer has just inserted breaks the key; either way the
/// transaction is rolled back and the answer is false, so BidBook reads
/// again and runs the rules again. The buyer's row keeps its own token,
/// which catches one account writing from two sites at once, and that too
/// answers false rather than writing over what it never saw.
///
/// <para>The token this takes is never handed to the transaction, and that is the whole of
/// what the ports record promised: a write abandoned between the standing and the buyer's
/// row would leave the store holding a standing nothing has a row for. The caller above
/// has already stopped honouring cancellation by the time it gets here, so the parameter
/// exists to keep one shape across the port and not to be used (ADR: The ports learn to
/// wait, the addendum on the token).</para>
/// </summary>
public async Task<bool> TrySaveAsync(string userId, string vehicleId, BidState state, VehicleStanding standing, string? expectedVersion, CancellationToken cancellation)
{
    using var db = await factory.CreateDbContextAsync();
    await using var transaction = await db.Database.BeginTransactionAsync();
    if (expectedVersion is not null && !await TryMoveStandingAsync(db, vehicleId, standing, long.Parse(expectedVersion, CultureInfo.InvariantCulture), CancellationToken.None))
    {
        return false;
    }

    if (expectedVersion is null)
    {
        db.Standings.Add(RowOf(vehicleId, standing, version: 1));
    }

    await StageBuyerRowAsync(db, userId, vehicleId, state, cancellation);
    try
    {
        await db.SaveChangesAsync();
    }
    catch (DbUpdateConcurrencyException)
    {
        return false;
    }
    catch (DbUpdateException) when (expectedVersion is null)
    {
        // An insert that broke a key lost to another writer's insert of the
        // same standing. Anything else that broke is a real failure and
        // travels.
        await transaction.RollbackAsync();
        if (await StandingExistsAsync(vehicleId, CancellationToken.None))
        {
            return false;
        }
        throw;
    }

    await transaction.CommitAsync();
    return true;
}

/// <summary>
/// The buyer's row for this vehicle, added or changed in place on the
/// context, with the concurrency token moved where the provider does not
/// move it itself. Nothing is written until the caller saves.
/// </summary>
private static async Task StageBuyerRowAsync(YardDbContext db, string userId, string vehicleId, BidState state, CancellationToken cancellation)
{
    var existing = await db.Bids.FindAsync(userId, vehicleId);
    if (existing is null)
    {
        existing = new BidRow
        {
            UserId = userId,
            VehicleId = vehicleId,
            Amount = state.Amount,
            BidCount = state.BidCount,
            WonBuyNow = state.WonBuyNow,
            AtMs = state.AtMs,
        };
        db.Bids.Add(existing);
    }
    else
    {
        existing.Amount = state.Amount;
        existing.BidCount = state.BidCount;
        existing.WonBuyNow = state.WonBuyNow;
        existing.AtMs = state.AtMs;
    }

    // SQL Server keeps the token itself; SQLite has no rowversion type, so
    // the store is what moves it. Setting it here rather than in a trigger
    // keeps the difference between the two providers in one readable place
    // (ADR: The SQL Server backend).
    if (db.Database.IsSqlite())
    {
        existing.RowVersion = Guid.NewGuid().ToByteArray();
    }
}

/// <summary>
/// Deletes one buyer's rows, every vehicle, and the standing of each of
/// those vehicles, in one transaction. The standings go so the next bid on
/// each vehicle works its standing out again from whoever is left.
///
/// <para>Like the bid write, the token is not given to the transaction: the standings and
/// the rows go together or not at all, and a reset stopped between them would leave
/// vehicles with no standing and rows still claiming one.</para>
/// </summary>
public async Task ClearAsync(string userId, CancellationToken cancellation)
{
    using var db = await factory.CreateDbContextAsync();
    await using var transaction = await db.Database.BeginTransactionAsync();
    // One person's rows and nobody else's. A delete over the whole table,
    // on a site two strangers can be looking at, would let either of them
    // delete the other's bids (ADR: Reset is one person's start-over).
    var touched = await db.Bids.Where(bid => bid.UserId == userId).Select(bid => bid.VehicleId).ToListAsync();
    await db.Standings.Where(row => touched.Contains(row.VehicleId)).ExecuteDeleteAsync();
    await db.Bids.Where(bid => bid.UserId == userId).ExecuteDeleteAsync();
    await transaction.CommitAsync();
}

Live from api/TheYard.Infrastructure/Shared/Auction/EfSources.cs, region bid-store, read from this build at 8003063.

/// <summary>Every buyer's document, and none of the standings that share the container.</summary>
/// <param name="cancellation">The caller's; the replay's starter lends it, which in a container is the host.</param>
public async Task<IReadOnlyList<StoredBid>> LoadAsync(CancellationToken cancellation)
{
    var documents = await store.QueryAsync<BidDocument>(
        store.Bids,
        new QueryDefinition("SELECT * FROM c WHERE c.user_id != @standing").WithParameter("@standing", StandingDocument.Partition),
        partitionKey: null,
        "cross-partition", cancellation: cancellation);
    return documents.Select(d => d.ToStoredBid()).ToList();
}

/// <summary>
/// Every buyer's document on one vehicle: the id is the vehicle, so this is
/// one id across every buyer's partition. It runs only when the vehicle has
/// no standing yet.
/// </summary>
public async Task<IReadOnlyList<StoredBid>> BidsOnAsync(string vehicleId, CancellationToken cancellation)
{
    var documents = await store.QueryAsync<BidDocument>(
        store.Bids,
        new QueryDefinition("SELECT * FROM c WHERE c.id = @vehicle AND c.user_id != @standing")
            .WithParameter("@vehicle", vehicleId)
            .WithParameter("@standing", StandingDocument.Partition),
        partitionKey: null,
        "cross-partition", cancellation: cancellation);
    return documents.Select(d => d.ToStoredBid()).ToList();
}

/// <summary>
/// The standing first, with a compare-and-set on its etag, then the buyer's
/// document. They live in different partitions, so this is two writes and
/// not a transaction: the standing goes first because it is what the rules
/// read, and a buyer's document missing after a standing that names them is
/// repaired from the standing when the cache is next read from the store.
/// A 412 or 409 on the standing answers false, nothing written.
///
/// <para>The token is taken and not used, which is this rung's one deliberate exception:
/// the standing goes first and the buyer's document second, and a write stopped between
/// them leaves the store holding a standing whose holder has no row. The caller above has
/// already stopped honouring cancellation by the time it reaches here (ADR: The ports
/// learn to wait, the addendum on the token).</para>
/// </summary>
public async Task<bool> TrySaveAsync(string userId, string vehicleId, BidState state, VehicleStanding standing, string? expectedVersion, CancellationToken cancellation)
{
    if (!await TryWriteStandingAsync(vehicleId, standing, expectedVersion, CancellationToken.None))
    {
        return false;
    }

    await SaveBuyerAsync(userId, vehicleId, state, CancellationToken.None);
    return true;
}

/// <summary>
/// One document per buyer per vehicle, replaced rather than appended. A
/// point read, then a create if there was nothing or a replace carrying the
/// etag the read returned. Three tries, then the exception travels: a 412
/// means another writer moved this buyer's document between the read and
/// the write, a 409 means another writer created it first, and both take
/// the one account bidding from two sites at once. The standing has
/// already been decided by then, so the retry writes this bid over what it
/// finds.
/// </summary>
private async Task SaveBuyerAsync(string userId, string vehicleId, BidState state, CancellationToken cancellation)
{
    for (int attempt = 1; ; attempt++)
    {
        try
        {
            await WriteAsync(userId, vehicleId, state, cancellation);
            return;
        }
        catch (CosmosException ex) when (attempt < 3
            && ex.StatusCode is HttpStatusCode.PreconditionFailed or HttpStatusCode.Conflict)
        {
            // Deliberately empty. The retry is the handling.
        }
    }
}

private async Task WriteAsync(string userId, string vehicleId, BidState state, CancellationToken cancellation)
{
    var existing = await store.ReadAsync<BidDocument>(store.Bids, vehicleId, userId, Partition, cancellation: cancellation);
    var document = new BidDocument
    {
        Id = vehicleId,
        UserId = userId,
        Amount = state.Amount,
        BidCount = state.BidCount,
        WonBuyNow = state.WonBuyNow,
        AtMs = state.AtMs,
    };
    if (existing is null)
    {
        await store.CreateAsync(store.Bids, document, userId, Partition, cancellation: cancellation);
    }
    else
    {
        await store.ReplaceAsync(store.Bids, document, vehicleId, userId, existing.ETag, Partition, cancellation: cancellation);
    }
}

/// <summary>
/// One person's documents: a query inside that person's partition for the
/// ids, then the deletes as an atomic batch, then the standing of each of
/// those vehicles, so the next bid works it out again from whoever is left.
/// There is no ExecuteDelete on this side; the batch is what a partition
/// offers instead (ADR: Reset is one person's start-over).
/// </summary>
public async Task ClearAsync(string userId, CancellationToken cancellation)
{
    var ids = await store.QueryAsync<string>(
        store.Bids,
        new QueryDefinition("SELECT VALUE c.id FROM c WHERE c.user_id = @user").WithParameter("@user", userId),
        partitionKey: userId,
        Partition, cancellation: cancellation);
    if (ids.Count > 0)
    {
        // Both with no token, as a pair: a reset that dropped the buyer's documents and
        // then stopped would leave every vehicle they touched standing at a price nobody
        // holds, which is the one thing a start-over must not do.
        await store.DeleteBatchAsync(store.Bids, userId, ids, Partition, cancellation: CancellationToken.None);
        await ForgetStandingsAsync(ids, CancellationToken.None);
    }
}

Live from api/TheYard.Infrastructure.Cosmos/Shared/Auction/CosmosSources.cs, region cosmos-bid-store, read from this build at 8003063.

The same guarantee, given and built

Uniqueness of an address. On the relational side it is one line of DDL and the engine's job; on the document side it is a method, because no index spans partitions (ADR: Accounts on a document store).

-- ASP.NET Core Identity's user table, plus the one column this application adds
-- (ADR: Accounts and per-user bids). The shape is Identity's, not this
-- application's: these column names and widths are what IdentityDbContext
-- expects to find, so they are transcribed rather than designed.
--
-- The key is nvarchar(128) and not Identity's default nvarchar(450), because 450
-- is what makes a composite key too wide for SQL Server. A clustered index key is
-- capped at 900 bytes; nvarchar(450) is 900 on its own, so PK_AspNetUserTokens at
-- Identity's default is 2,700 bytes and PK_Bids is 1,028. The first publish of
-- this schema said so out loud, four times, and every one of those tables would
-- have failed an insert on a long enough value. The ids this application creates
-- are GUIDs, so 128 is generous.
--
-- The nvarchar(max) columns are deliberate and are the only unbounded columns in
-- this database. PasswordHash and the two stamps belong to a framework whose
-- hashing algorithm can change under this application, they hold a handful of
-- rows, and bounding them would trade nothing measurable for a truncation that
-- would appear on the day somebody upgraded.
CREATE TABLE [dbo].[AspNetUsers] (
    [Id]                   nvarchar(128)     NOT NULL,
    [CreatedAtMs]          bigint            NOT NULL,  -- this application's one addition: when the account was created
    [UserName]             nvarchar(256)     NULL,
    [NormalizedUserName]   nvarchar(256)     NULL,
    [Email]                nvarchar(256)     NULL,
    [NormalizedEmail]      nvarchar(256)     NULL,
    [EmailConfirmed]       bit               NOT NULL,
    [PasswordHash]         nvarchar(max)     NULL,
    [SecurityStamp]        nvarchar(max)     NULL,
    [ConcurrencyStamp]     nvarchar(max)     NULL,
    [PhoneNumber]          nvarchar(max)     NULL,
    [PhoneNumberConfirmed] bit               NOT NULL,
    [TwoFactorEnabled]     bit               NOT NULL,
    [LockoutEnd]           datetimeoffset    NULL,
    [LockoutEnabled]       bit               NOT NULL,
    [AccessFailedCount]    int               NOT NULL,
    CONSTRAINT [PK_AspNetUsers] PRIMARY KEY ([Id])
);
GO

-- Identity looks accounts up by the normalized forms, never by the display ones.
CREATE INDEX [EmailIndex] ON [dbo].[AspNetUsers] ([NormalizedEmail]);
GO

-- Filtered, because a null user name is not a duplicate of another null one.
CREATE UNIQUE INDEX [UserNameIndex] ON [dbo].[AspNetUsers] ([NormalizedUserName]) WHERE [NormalizedUserName] IS NOT NULL;
GO

Live from api/TheYard.Database/Tables/Identity/AspNetUsers.sql, region *, read from this build at 8003063.

/// <summary>
/// The claim first, the account second. A store with no unique index across
/// partitions cannot refuse a second account on the same address; a create
/// of a document whose id is the address can, with 409, and that refusal is
/// what makes the address unique. If the account write then fails, the
/// claim is removed again, so a failure leaves nothing behind. A process
/// that dies between the two writes leaves a claim with no account, which
/// is the honest cost of the shape and is written down in the record.
/// </summary>
public async Task<IdentityResult> CreateAsync(YardUser user, CancellationToken cancellationToken)
{
    string normalizedEmail = user.NormalizedEmail ?? user.NormalizedUserName
        ?? throw new InvalidOperationException("an account needs a normalized email address before it is stored");
    var claim = new EmailClaimDocument { Id = EmailClaimDocument.IdFor(normalizedEmail), UserId = user.Id };
    try
    {
        await store.CreateAsync(store.Users, claim, claim.Id, ClaimPartition, "CreateItem (email claim)");
    }
    catch (CosmosException ex) when (ex.StatusCode == HttpStatusCode.Conflict)
    {
        return IdentityResult.Failed(Errors.DuplicateEmail(user.Email ?? ""));
    }

    try
    {
        var response = await store.CreateAsync(store.Users, ToDocument(user), user.Id, Partition, "CreateItem (account)");
        user.ConcurrencyStamp = response.ETag;
        return IdentityResult.Success;
    }
    catch (CosmosException)
    {
        // None: this undoes the claim this method wrote a moment ago, and a registration
        // that stopped here would leave the address claimed with no account behind it.
        await TryDeleteClaimAsync(claim.Id, CancellationToken.None);
        throw;
    }
}

Live from api/TheYard.Infrastructure.Cosmos/Features/Accounts/CosmosUserStore.cs, region create, read from this build at 8003063.

Where the two are the same

The application does not care. The three ports are the same interfaces on both sides; BidService serializes simultaneous bids the same way whichever store is underneath; a sign-in is Identity's own code on both, with a different IUserStore behind it. Both are reached with a managed identity and no secret. Both are logged, statement for operation, into the same ring the Admin tab reads. Both cost nothing until 14 September, when the relational side's free allowance ran out and the sites moved to a Basic database at $4.90 a month. And on the pages a visitor spends most of their time on, the listing, the vehicle and the filter values, neither store is on the path at all, because the catalogue is served from memory, so those pages measure the same to within a few milliseconds on both sites (ADR: Measuring both stores).

Where they differ, and what that buys

The relational side buys guarantees the application uses: a foreign key that takes an account's bids with it, a unique index that is nobody's job to remember, a transaction that can touch every table, a schema in source control as DDL, and a planner that will sort by anything. The document side buys a cost model you can see on every response, point reads at single-digit milliseconds from compute in the account's own region, a shape you can change without a migration, and the freedom to add regions if the site ever needed them. What the document side charges for those is visible on this page: the claim document that stands in for a unique index, the cascade that does not exist, the sort that needs an index on the sorted path, and the synchronous port that had to learn to wait (ADR: The ports learn to wait).

The measured difference between them on this workload is geography, not engine: until 20 September the SQL server was one region away because that region refused to create one, and the Cosmos DB account was next door; since the move to the App Service plan the two have traded places. With the round trips taken out the two are the same, which is the sentence the proof record was written to be able to say (ADR: Same performance, proven). The honest choice for a used-vehicle auction whose catalogue fits in memory and whose writes are bids is the one whose guarantees it uses, and that reasoning is ADR: Cosmos DB, explained for someone who knows SQL Server; this page is the evidence laid out beside it.

Reading

Microsoft's own documentation for each row, thirty-six pages, every one read on 2026-09-09 before it was linked here (three more that answered 404 that morning were left out). The relational side first, then the document side, then the guides that compare them.

Azure SQL Database and Entity Framework Core:

Azure Cosmos DB for NoSQL:

Choosing between them:

Keeping it true

  • The comparison has one address on each site, and a picture that is a claim about the repository rather than an illustration: the diagram names files, and the page shows those files live.
  • A change to either adapter's bid store changes this page on the next request, because the code is read from the build; a change to a number in ADR-064 or ADR-067 has to be carried here by hand, and the drawing redrawn.
  • This is the page to send a reader who asks "why not just one database": the answer is in the last two sections, with the measurements above them.

Files

Store

The Yard, home

Welcome to The Yard

Steven Stout’s showcase: a used-vehicle auction on .NET and React, built and run by one engineer, live on Azure, with its decisions written down.

Answering in

unread

0255075100

server time at p95, the usual five minutes of the last day.

Azure this month

unread

the bill, by resource, read from Azure. The Admin tab shows every line.

Since the last roll

unread

 

rolled blue and green, with no downtime.

People who have visited

unread

30 days agotoday

Scanners, my own tools' and my own reads left out.

  •    
  •    
  •    

Tech stack

Read from the build, never typed by hand

What TheYard runs on

What we use, where it runs, the version as built, and the file that proves it.

  • .NET 1014 projects
  • React 19.3TypeScript 7.0
  • Azure6 Bicep files
  • 9GitHub Actions workflows
LayerWhat we useWhere it runsVersionProof
Backendthe API
Backendthe API.NET and ASP.NET CoreApp Service, the API container10.0TheYard.Api.csproj
Entity Framework Core (SQL Server)App Service, talking to Azure SQL10.0.12TheYard.Infrastructure.csproj
Identity with JWT bearer authApp Service, the API container10.0.12TheYard.Api.csproj
OpenAPI with Scalar API docsApp Service, /api docs2.17.13TheYard.Api.csproj
Frontendthe page
Frontendthe pageReactYour browser19.3package.json
TypeScriptYour browser7.0package.json
ViteBuild step, in the container image8.3package.json
Server-side rendering on NodeApp Service, the renderer container24render/Dockerfile
Datatwo stores, one port
Datatwo stores, one portAzure SQL DatabaseAzure, managed SQLEF Core 10.0.12TheYard.Infrastructure.csproj
Azure Cosmos DBAzure, NoSQLSDK 3.63.2TheYard.Infrastructure.Cosmos.csproj
Azureinfrastructure as code
Azureinfrastructure as codeApp Service and Container Registry (Linux container)Azure App Service and ACRlive resourceappservice.bicep
Front Door (written, switched off)Off: the subscription refuses Front Door, so the Netlify edge stands in front todayoffmain.bicep
Application Insights with OpenTelemetryAzure Monitor1.6.0TheYard.Api.csproj
Communication Services emailAzure Communication Services1.1.0TheYard.Api.csproj
Azure Functions, Flex Consumption (clock, snapshot, tracker merger, Sheet mirror, ATS sweep, daily card)func-jobhunt-ss, Flex Consumption.NET 10 isolatedJobHunt.Functions.csproj
Bicep for every resource it deploysDeployed from the repository6 Bicep filesinfra/
Edge
EdgeNetlify edge in front of the siteNetlify edge networklive resourcenetlify.toml
Quality gatesevery push
Quality gatesevery pushxUnitGitHub Actions runners2.9.3TheYard.Tests.csproj
NetArchTest architecture rulesGitHub Actions runners1.4.5TheYard.Tests.csproj
VitestGitHub Actions runners5.0package.json
Playwright with axe accessibility checksGitHub Actions runners, against the built site1.63package.json
oxlint and PrettierGitHub Actions runners1.86 and 3.9package.json
Delivery and AIhow it ships
Delivery and AIhow it shipsGitHub Actions (azure-read, blog-post, ci, deploy-function, deploy-render, deploy, edge-branch, gate, nightly)GitHub9 workflowsworkflows/
Built with Claude Code under the test gateDeveloper machine, then the gateevery pushgate.yml
Claude Code on GitHub Actions runs the job hunt's nightly lanesGitHub Actions runners, on the Azure share4 AM and every 4 hnightly.yml

Generated, not typed. A build step reads the project files, package.json, the Dockerfiles, the Bicep and the workflows; a test fails CI when this table drifts from the repository.

How it is built

How it is run

Who and why