Three ways to slice a tenant

The first time a customer asked me for a database of their own was in the spring of 2010, at a securities settlement bank, about three weeks before a web application went live. We had built the data layer on shared rows with a tenant id column, and the honest answer was no. Not because of the code, which would have taken a fortnight, but because a database per customer in 2010 meant a SQL Server 2008 instance with its own licence, its own maintenance plan and its own backup window, on hardware somebody had to order. They accepted that the way people accept a train timetable. Fifteen years later I still get the same request in every second commercial conversation, and I still often give the same answer, but the reason has changed completely: saying yes now costs a row in a catalog rather than a purchase order, so when I say no it has to be about something else.

The choice between a database per tenant, a schema per tenant and shared rows is not a coding decision. The code differs by a connection string and a query filter. It is an operations decision, and you make it once, because moving between models later means moving data, which is the one thing customers do not forgive you for getting wrong. So the trade-offs below are stated in terms of what you will be doing at three in the morning, not in terms of how the DbContext looks.

Database per tenant

Each tenant gets its own Azure SQL database. Same schema, same code, different connection string. Isolation is as good as it gets short of a separate subscription: a tenant cannot see another tenant's rows because the rows are not in the same database, and no query filter can be forgotten because there is nothing to filter. Backup and restore are per tenant by construction. When a customer deletes a month of records by accident, and one of them will, you point-in-time restore their database and nobody else notices.

The price is density and operations. Four hundred tenants means four hundred databases, four hundred migrations on every release, and four hundred sets of indexes. Azure SQL is built for this; automatic tuning and elastic pools exist precisely so that a database-per-tenant product does not pay for peak capacity four hundred times over. In 2016, the year after pools became generally available, I ran 120 databases on a 200 eDTU Standard pool and the bill was lower than the single Premium database it replaced. That is the number that changed my mind about this whole sheet. But a schema migration that fails on database 213 of 400 is a Tuesday you will remember. You need a catalog to know which database belongs to whom, a migration runner that is idempotent and resumable, and a rule that no release ships until every database has been migrated or explicitly parked.

// .NET 9: pick the connection string per request from the catalog.
// In 2010 the equivalent was a connection string handed to an ObjectContext
// constructor; the factory overload has existed since 3.0 and is the better place.
builder.Services.AddDbContext<AppDbContext>((sp, options) =>
{
    var tenant = sp.GetRequiredService<ITenantContext>();
    var catalog = sp.GetRequiredService<ITenantCatalog>();
    options.UseSqlServer(catalog.ConnectionStringFor(tenant.TenantId));
});

Schema per tenant

One database, one schema per tenant, tables duplicated under tenant_0042.Invoices and so on. On paper it splits the difference: cheaper than a database per tenant, better separated than shared rows. In practice it gives you the worse half of each. You still get noisy neighbours, because everyone shares the same compute and the same transaction log. You cannot restore a single tenant, because Azure SQL restores a database; backups are for everyone or for no one. And EF Core does not support it as a first-class model. You can override the schema in OnModelCreating, but migrations are then your problem, multiplied by tenant count, inside a single database where a DDL lock on one schema blocks the others.

I recommended this model for most of the 2010s, for products between twenty and two hundred tenants, and I was wrong for most of the 2010s. The two systems I know that chose it both migrated away, one to shared rows and one to databases, and both migrations took longer than the original build. If you want the separation, take the database. If you want the density, take the rows.

Shared rows with a tenant id

Every table carries a TenantId column, every query filters on it, every index leads with it. This is the cheapest model per tenant by a wide margin, and the one that scales to numbers where the others become unmanageable: Microsoft's own guidance puts sharded shared-row databases at millions of tenants, against a hundred thousand or so for database per tenant. It is also the model where a bug leaks data. The fence between tenant A and tenant B is a WHERE clause, and a WHERE clause can be forgotten, bypassed with raw SQL, or lost in a bulk update. EF Core's global query filters make the default safe, and Azure SQL's row-level security makes the database enforce the same rule independently. I treat both as mandatory, and sheet T-03 walks through where the filters leak.

Restore is the sore point. A point-in-time restore brings back everyone's data, so restoring one tenant means restoring a copy of the database next to production and copying that tenant's rows across, with foreign keys and identity columns fighting you the whole way. We wrote a tool for this in 2019. It took two weeks and has been used four times, which is four times more than I expected.

Noisy neighbours and migrations

Noisy neighbours are the argument that sells databases, and the one most people get backwards. A single tenant running a heavy report degrades everyone in shared rows, yes. But in a database-per-tenant pool, that same tenant consumes pool resources, and the per-database maximum you set on the pool is what protects the others, not the database boundary itself. The pool gives you a knob to turn; shared rows give you nothing but query governance in your own code. That knob is worth a great deal at three in the morning, and it is the single strongest practical argument for the database model.

Migrations invert the picture. One database means one migration, one transaction, one place to look when it fails. Four hundred databases means a fleet, and fleets need tooling. The middle path I have settled on is shared rows in a small number of sharded databases, say twenty to fifty tenants per database grouped by plan, which keeps the migration count in the dozens and lets a large tenant be moved to a database of its own when it starts to hurt. That is the hybrid model in Microsoft's guidance, and it is the shape most mature products end up with whether they planned it or not.

What I would pick today

For a new B2B product with fewer than fifty tenants and customers who ask about isolation in the first meeting: database per tenant in an elastic pool, with a catalog from day one. For anything that expects hundreds of small tenants, self-service signup or a free tier: shared rows with a tenant id in a handful of sharded databases, with global query filters and row-level security both switched on. The schema-per-tenant model I no longer draw at all. And when a customer asks for their own database, the answer in the shared-row design is a shard with one tenant in it, which is the same column, the same code, and a different row in the catalog.

Drawn from

• Multitenant SaaS patterns for Azure SQL Databaselearn.microsoft.com
• Tenancy models for a multitenant solutionlearn.microsoft.com
• Elastic pools in Azure SQL Databaselearn.microsoft.com
• Multi-tenancy in EF Corelearn.microsoft.com