Sharding Azure SQL with elastic pools and a catalog

It fell over at 340 tenants. Not the application; the database. One Premium P2 Azure SQL database holding every tenant of a platform I was responsible for, and an overnight alert run in January 2017 that pinned log write throughput at the ceiling for forty minutes while three hundred and thirty-nine other tenants watched a spinner. We had known for a year that one database was a temporary arrangement. Nothing is as permanent as a temporary arrangement that works.

Sharding fixed it, and sharding turned out to be about a fifth technology and four fifths bookkeeping. The technology is an elastic pool and a connection string. The bookkeeping is the catalog: the one place that knows which tenant lives in which database in which pool on which server, and what state that tenant is in right now. In 2017 I let a library own that bookkeeping, because it came from Microsoft and I had a deadline. In 2025 I own it myself, in two tables, and this sheet is about why and how.

The catalog is two tables

A tenant row maps a tenant to a shard and carries a state. A shard row maps a shard name to a server, a database and, optionally, a pool. That is the whole model. Everything else people put in a catalog, plan codes, settings, contact details, belongs there too, but those are sheet T-05's problem; for routing you need exactly this.

CREATE TABLE catalog.Shard (
    ShardName    sysname       NOT NULL PRIMARY KEY,
    ServerFqdn   nvarchar(255) NOT NULL,
    DatabaseName sysname       NOT NULL,
    PoolName     sysname       NULL
);

CREATE TABLE catalog.Tenant (
    TenantId     uniqueidentifier NOT NULL PRIMARY KEY,
    Host         nvarchar(253)    NOT NULL UNIQUE,
    ShardName    sysname          NOT NULL REFERENCES catalog.Shard (ShardName),
    State        tinyint          NOT NULL,   -- 0 online, 1 moving, 2 offline
    Version      rowversion
);

The catalog lives in its own small database on the same logical server as the shards. It is read on every request and written a few times a week, so it is tiny and it is critical, which is an awkward combination. I run it one tier higher than its size justifies, zone redundant, with a failover group, and I cache reads aggressively. A catalog outage is an outage for every tenant at once, and no amount of shard isolation helps you then.

Resolving the connection string

The lookup happens once per request, after the tenant resolver from sheet T-02 has found the tenant by host. The resolver hands back a tenant id; the shard resolver turns it into a connection string, and the DbContext factory uses it. The cache in the middle is HybridCache, which gives me an in-process layer and a distributed layer with one API, and tags so a tenant move can evict exactly one entry.

public sealed record ShardAddress(string Server, string Database);

public sealed class ShardResolver(CatalogDbContext catalog, HybridCache cache)
{
    public async ValueTask<string> ConnectionStringForAsync(Guid tenantId, CancellationToken ct)
    {
        var shard = await cache.GetOrCreateAsync($"shard:{tenantId}",
            async token => await catalog.Tenants
                .Where(t => t.TenantId == tenantId && t.State == TenantState.Online)
                .Select(t => new ShardAddress(t.Shard.ServerFqdn, t.Shard.DatabaseName))
                .SingleOrDefaultAsync(token),
            new HybridCacheEntryOptions { Expiration = TimeSpan.FromMinutes(5) },
            tags: [$"tenant:{tenantId}"], cancellationToken: ct)
            ?? throw new TenantUnavailableException(tenantId);

        return new SqlConnectionStringBuilder
        {
            DataSource = shard.Server,
            InitialCatalog = shard.Database,
            Authentication = SqlAuthenticationMethod.ActiveDirectoryManagedIdentity,
            Encrypt = true,
        }.ConnectionString;
    }
}

Note the filter on State. A tenant that is moving or offline resolves to nothing, and nothing turns into a 503 with a retry-after header at the edge. In 2017 the resolver returned whatever the shard map said, and the application found out about a move by writing into the old database. Managed identity replaced a SQL login in 2023; one identity has access to every shard, which is fine because the application is one trust boundary, and the tenant fence is inside the database, not at the login.

Pools are budgets, shards are databases

An elastic pool is a fixed amount of compute and storage shared by the databases inside it, billed by the hour whether they use it or not. The point of it is that forty tenants do not all peak in the same minute, so a pool sized for the aggregate is a fraction of forty databases each sized for its own peak. The per-database maximum you set on the pool is the noisy-neighbour control: a shard cannot take more than its cap no matter what its tenants do, and the rest of the pool keeps breathing.

Moving a database between pools is a metadata operation. It does not move data, it takes a few minutes, and connections drop for a few seconds at the end. That makes the pool a knob you can turn from a runbook when a shard grows out of its neighbours, without touching the catalog at all, since the catalog maps to server and database and does not care which pool the database sits in.

ALTER DATABASE [shard-03]
    MODIFY (SERVICE_OBJECTIVE = ELASTIC_POOL(name = [pool-premium]));

The grouping rule I use is by plan and by size. Trial and free-tier tenants share shards of fifty or more in a Standard pool, paying tenants share shards of twenty in a larger pool, and a tenant that has earned its own database gets a shard with one row in it, in whichever pool matches its contract. The largest tenant on that platform, roughly sixty times the size of the median one, has had a shard to itself since 2018 and has moved pools twice, both times in an afternoon.

Moving a tenant between shards

Moving a tenant between shards is a data move, and no amount of tooling makes it free. The procedure is: mark the tenant Moving in the catalog and evict its cache entry by tag, wait for in-flight requests to finish (thirty seconds is enough when every request is short, and every request should be short), copy the tenant's rows to the target shard with TenantId as the only predicate, verify row counts per table, update the shard name in the catalog, and set the state back to Online. The customer sees a maintenance page for the duration of the copy, which for a typical tenant is under two minutes and for the largest one is forty.

For the large ones I copy in two passes: a bulk pass while the tenant is still online, then a short delta pass on a modified-at watermark after the tenant goes Moving. That brings the offline window down to the delta. I have not automated the delta pass. It runs four or five times a year, by hand, from a script that a second person reads before it executes, and that has been the right amount of automation for eight years.

Why the Elastic Database client library went

The 2017 version of this sheet was built on the Elastic Database client library, with a shard map manager database, list mappings from tenant key to shard, and data-dependent routing through OpenConnectionForKey. It worked, and on .NET Framework 4.6 it was clearly the sanctioned answer. It also owned its own schema in a database I could not easily read, carried a global shard map cache with its own invalidation rules, had a split-merge service that was a separate deployment with a separate set of problems, and arrived on .NET Core later than I did. Every time something went wrong, the answer was inside the library.

The two tables above replace the part of it I actually used. What they do not replace is multi-shard querying, and I have made my peace with that: cross-tenant reporting reads from a nightly copy, not from production shards, and fleet-wide maintenance runs through elastic jobs. Microsoft has since put an end-of-support date on the shard-map-manager mode of elastic query, which tells me the direction of travel. A catalog you can SELECT from at three in the morning is worth more than a feature you never quite trusted.

The shape I draw now

Two catalog tables in their own zone-redundant database, a resolver behind HybridCache that refuses anything not Online, shards of twenty to fifty shared-row tenants grouped by plan into elastic pools with a per-database cap, and single-tenant shards for the customers who have earned them. Pool moves from a runbook, shard moves from a script with a second pair of eyes, and no library between me and the table that says where a tenant lives.

Drawn from

• Elastic pools in Azure SQL Databaselearn.microsoft.com
• Scaling out with Azure SQL Databaselearn.microsoft.com
• Manage elastic poolslearn.microsoft.com
• Multitenant SaaS patterns for Azure SQL Databaselearn.microsoft.com