Multi-tenancy is one of the most consequential architectural decisions in a SaaS product. Get it right early and your schema scales cleanly as your customer base grows. Get it wrong and you're facing a painful migration - usually at the worst possible time, when you've just landed your biggest customer and can't afford downtime.
There is no single correct approach to multi-tenant schema design in SQL Server. The right pattern depends on your isolation requirements, your expected tenant count, your data volume, and the compliance constraints your customers will bring. This post walks through the three main patterns, their trade-offs, and the mistakes we see repeatedly in the wild.
The Three Patterns
#1: Shared database, shared schema.
Every tenant's data lives in the same tables, distinguished by a TenantId column. This is the simplest approach and the most common starting point. It's easy to deploy, easy to maintain, and scales well in terms of infrastructure cost. The downsides are real: a missing WHERE clause in a query can leak data across tenants, index design becomes complex as tenant data volumes diverge, and compliance requirements - particularly around data residency or isolation - are harder to satisfy.
#2: Shared database, separate schema.
Each tenant gets their own schema within the same database. Tables are identical in structure but isolated by schema. This gives you cleaner separation without the infrastructure overhead of separate databases. It's a reasonable middle ground for applications with moderate tenant counts and similar data volumes. The main drawback is operational complexity - migrations must be applied across every tenant schema, which requires careful tooling and discipline.
#3: Separate database per tenant.
Each tenant gets their own database. This provides the strongest isolation, simplest query logic (no TenantId needed), and the most flexibility for compliance and data residency requirements. The trade-off is infrastructure cost and operational overhead. Managing migrations across hundreds of databases requires a robust deployment pipeline. It's the right choice for enterprise SaaS products with high-value customers and strict isolation requirements.
Implementing the Shared Schema Pattern
If you're building a new SaaS product and starting with the shared schema pattern, the most important thing you can do is enforce TenantId at the application layer, consistently and without exception. Every repository method, every query, every raw SQL call must include a tenant filter. The simplest way to enforce this is to inject the current tenant context as a scoped dependency and build it into a base repository class that all data access inherits from.
In Entity Framework Core, you can use query filters to apply TenantId automatically to every query on a given entity type. Add HasQueryFilter to each entity configuration, pointing to a scoped ITenantContext service. This gives you a safety net - even if a developer forgets to add a tenant filter manually, the global filter applies. Be aware that this doesn't protect you from raw SQL queries or stored procedures, which must be audited separately.
A single missing WHERE TenantId = @TenantId in a raw SQL query is a data breach. Build tenant isolation into your infrastructure, not just your conventions.
Indexing for Multi-Tenancy
Every table with a TenantId column needs a composite index that includes TenantId as the leading column. If your queries filter by tenant and then by another column - say, CreatedDate or Status - your index should be (TenantId, CreatedDate) or (TenantId, Status) respectively. This is different from a single-tenant schema where you'd index on the data column alone.
In practice, this means revisiting your indexing strategy specifically for multi-tenancy. Don't just add TenantId to existing indexes as a trailing column - it won't help. The index needs to lead with TenantId to enable efficient range scans within a single tenant's data partition. Use the Query Store and execution plans to validate that your indexes are being used as tenant data volumes grow.
Row-Level Security
SQL Server's Row-Level Security feature offers a database-enforced alternative to application-layer tenant filtering. You define a security policy that maps database sessions to tenant IDs, and SQL Server automatically filters rows accordingly. This is a compelling option for applications where you want defence-in-depth - isolation enforced at the database level regardless of application code.
The practical limitation is that RLS adds complexity to your deployment and debugging workflow. Query plans can be harder to interpret, and performance tuning requires understanding how the security predicate interacts with your indexes. We use it selectively - typically for high-value tenants with explicit isolation requirements - rather than as the primary multi-tenancy mechanism.
Migration Strategy
Whichever pattern you choose, migrations are the operational challenge that compounds over time. For shared schema, migrations are straightforward - one schema change, all tenants affected simultaneously. For separate schema or separate database patterns, you need a migration orchestration layer. We use a purpose-built migration runner that iterates over all tenant databases in a controlled sequence, with rollback capability and per-tenant logging.
The most important rule: never apply migrations manually. Once you have more than a handful of tenants, manual migration is a liability. Build the tooling early, even if it feels like over-engineering at the time. When you need to deploy a schema change to 200 tenant databases at 11pm before a customer launch, you'll want that tooling to be battle-tested.
Choosing the Right Pattern
For early-stage SaaS products with uncertain scale, start with shared schema. It's the fastest to implement and the easiest to operate. Build tenant isolation rigorously at the application layer. Design your indexes with TenantId in mind from day one. If you later need to move individual high-value tenants to their own databases, that migration is manageable - especially if your application layer already treats TenantId as a first-class concept.
For products targeting enterprise customers from the outset - particularly in regulated industries like financial services, healthcare, or legal - separate databases are worth the operational investment. The compliance conversations become significantly easier when you can tell a customer their data is physically isolated.
The schema pattern you choose on day one will still be running your business on day one thousand. It's worth an extra week of thought before you write the first migration.
