Skip to main content

ADR-005: Database Strategy — Database-Per-Tenant on SQL MI

Status: Accepted Date: 2026-03-16

Context​

PSSaaS is a multi-tenant SaaS platform. Existing customers have isolated SQL Server instances (one per customer, either on-premises or in IBM Softlayer VMs). The SaaS platform needs a tenant isolation strategy that provides strong data separation and a natural migration path from existing deployments.

Options considered:

  • Shared database, shared schema (row-level isolation): All tenants in one database, tenant_id column on every table
  • Shared database, separate schemas: One database, each tenant gets its own schema
  • Database-per-tenant: Each tenant gets its own database on a shared SQL MI instance
  • Instance-per-tenant: Each tenant gets its own SQL MI instance (current state for MWFI)

Decision​

Each tenant gets their own database on Azure SQL Managed Instance. Connection string is resolved by tenant context from the JWT. Schema is uniform across tenants (same pscat_/rmcat_/pxcat_ structure, modernized incrementally).

Key design:

  • Tenant context middleware extracts tenant identifier from JWT claims
  • A tenant registry maps tenant IDs to connection strings
  • EF Core DbContext is configured per-request with the tenant-specific connection string
  • Schema migrations are applied to all tenant databases via a migration runner

Consequences​

Positive:

  • Strongest data isolation: No possibility of cross-tenant data leakage at the database level
  • Natural migration path: Existing customer databases (already isolated) move to SQL MI databases with minimal schema changes
  • Independent backup/restore: Each tenant can be backed up and restored independently
  • Performance isolation: One tenant's heavy query doesn't affect another tenant
  • Regulatory compliance: Simplifies audit and compliance (each tenant's data is physically separate)

Negative:

  • Higher per-tenant cost: Each database has a storage footprint on SQL MI
  • Schema migration complexity: Schema changes must be applied to every tenant database (requires a migration orchestrator)
  • Connection management: Connection pool per tenant database (mitigated by SQL MI's connection pooling)
  • Cross-tenant queries impossible by design: Aggregate reporting across tenants requires a separate analytics approach