Engineering Insights

Building Modern, Scalable Web Systems

In-depth tutorials, database optimization strategies, and enterprise backend patterns.

Multi-Tenant Database Design Patterns in SQL Server

SQL Server October 2026

Multi-Tenant Database Design Patterns in SQL Server

Designing database architectures for SaaS applications requires balancing data isolation, operational complexity, and cost efficiency. Depending on regulatory compliance and tenant scale, database engineers typically choose between a shared database with discriminator columns or isolated per-tenant databases.

In this post, we will examine the shared database pattern with Row-Level Security (RLS) in SQL Server to enforce multi-tenant isolation at the engine level.

1. Architecture Models Compared

  • Database-per-Tenant: Maximum isolation and security; higher maintenance overhead and infrastructure cost.
  • Schema-per-Tenant: Moderate isolation; schema migrations become complex across hundreds of schemas.
  • Shared Database, Shared Schema: Highly cost-effective and scalable; requires strict application or engine-level security filters.

2. Enforcing Tenant Isolation with Row-Level Security (RLS)

Relying solely on application-level WHERE TenantId = @TenantId filters introduces high risk—a single forgotten clause leaks cross-tenant data. SQL Server Row-Level Security uses Security Policies and Inline Table-Valued Functions (TVFs) to transparently filter rows based on the session's context.

-- Step 1: Create Schema and Security Predicate Function
CREATE SCHEMA Security;
GO

CREATE FUNCTION Security.fn_TenantAccessPredicate(@TenantId INT)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS fn_accessResult
WHERE @TenantId = CAST(SESSION_CONTEXT(N'TenantId') AS INT);
GO

-- Step 2: Apply Security Policy to Multi-Tenant Table
CREATE TABLE dbo.Orders (
    OrderId INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    TenantId INT NOT NULL,
    OrderDate DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
    TotalAmount DECIMAL(18,2) NOT NULL
);
GO

CREATE SECURITY POLICY Security.TenantOrderPolicy
ADD FILTER PREDICATE Security.fn_TenantAccessPredicate(TenantId) ON dbo.Orders,
ADD BLOCK PREDICATE Security.fn_TenantAccessPredicate(TenantId) ON dbo.Orders AFTER INSERT
WITH (STATE = ON);
GO

3. Executing Context-Aware Queries

Before executing queries on behalf of a tenant, set the current tenant session variable using sp_set_session_context. SQL Server will automatically filter all subsequent reads and writes.

-- Set Tenant Context in Connection Pipeline (e.g., via EF Core Interceptor)
EXEC sp_set_session_context @key = N'TenantId', @value = 101;

-- All queries automatically filtered to TenantId = 101
SELECT OrderId, OrderDate, TotalAmount 
FROM dbo.Orders;

Key Architectural Recommendations

  • Index Leading Columns: Always include TenantId as the leading column in non-clustered composite indexes (e.g., IX_Orders_TenantId_OrderDate).
  • Use SESSION_CONTEXT: Prefer SESSION_CONTEXT over CONTEXT_INFO as it supports key-value pairs and read-only flags for safer tenant scoping.
  • Automate Migrations: Ensure database migrations run within transactional batches to avoid partial schema updates in multi-tenant environments.
Older Posts →