SaaS Data Isolation on Azure: Database per Tenant vs Shared Schema

For most SaaS products on Azure, the choice is between a shared database where every table carries a tenant ID and row-level security enforces isolation, and a database per tenant hosted in Azure SQL Database elastic pools. Shared schema gives the lowest cost per tenant and the simplest operations but the weakest isolation; database per tenant gives strong isolation, per-tenant restore and per-tenant encryption keys at a higher cost. Many products end up with a hybrid: small tenants share databases, and tenants who pay for isolation get their own.

Who this is for and what you will have at the end

This guide is for architects and lead developers designing the data tier of a multi-tenant SaaS application on Azure, or revisiting the model of an existing product before it grows further. Microsoft's own documentation notes that switching tenancy model later is sometimes costly, so this decision deserves time early.

At the end you will have:

• A comparison of the tenancy models for Azure SQL Database and Azure Cosmos DB, and a requirements table that maps each tenant to a model.
• Working T-SQL for shared-schema isolation with row-level security.
• Azure CLI commands for database per tenant in an elastic pool, including a per-tenant restore.

The tenancy models at a glance

A tenancy model describes how each tenant's data maps to storage. Azure SQL guidance describes single-tenant databases (one tenant per database), multitenant databases (many tenants per database, separated by a tenant identifier) and hybrids of the two. The models you are realistically choosing between are these:

The Azure SQL tenancy patterns article gives indicative scale: hundreds of tenants for a standalone app, up to around 100,000 for database per tenant, and millions for sharded multitenant databases, which carry medium development complexity because of the catalog and routing logic.

How to choose: the requirements that decide it

Start from what tenants and regulators require, not from what is cheapest to build. The Azure Architecture Center lists conditions that force you to isolate a tenant, or at least group it only with tenants that share the same policy:

• The tenant needs its own encryption keys.
• The tenant has its own backup and restore policy.
• The tenant's data must be stored in a specific geography.

Use the following table to map each requirement to the models that can meet it.

Two observations follow from this table. First, if even a minority of your customers need their own keys or per-tenant restore, you need at least a dedicated-database tier for them. Second, if most tenants are small and price-sensitive, a shared model keeps the unit cost down. That is why the hybrid approach, where all databases carry a tenant ID column but some contain only one tenant, is attractive: you can move a tenant from a shared database to its own database without a schema change.

Prerequisites

To follow the build steps you need:

• An Azure subscription where you can create resource groups, an Azure SQL logical server and elastic pools (Contributor or SQL Server Contributor on the resource group).
• Azure CLI installed and signed in with az login.
• A SQL client such as SQL Server Management Studio or Visual Studio Code connected to the server.
• A tenant identifier that your application already resolves for every request, for example from a claim in the access token. If your API sits behind Azure API Management, see validating Entra tokens and per-client rate limits in API Management for how to extract claims at the gateway.

Option A: shared schema with row-level security

In a shared database every tenant-owned table carries a tenant ID. Microsoft recommends a single set of multitenant tables with a tenant identifier column rather than a separate table per tenant. If you ever plan to shard, make the tenant ID the leading column of the primary key; the Azure SQL split/merge tooling expects the sharding key to lead the primary key of every sharded table.

Step 1: add the tenant column

CREATE TABLE dbo.Orders (
    TenantId   int           NOT NULL,
    OrderId    bigint        NOT NULL,
    CustomerId bigint        NOT NULL,
    Total      decimal(18,2) NOT NULL,
    CONSTRAINT PK_Orders PRIMARY KEY (TenantId, OrderId)
);

Step 2: create a low-privileged application user

The application connects as one database user. Grant it data access and deny updates to the tenant column so that rows can never be moved between tenants.

CREATE USER AppUser WITHOUT LOGIN;  -- use a Microsoft Entra identity in production
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.Orders TO AppUser;
DENY UPDATE ON dbo.Orders(TenantId) TO AppUser;

Step 3: create the predicate function in its own schema

Row-level security uses an inline table-valued function as the predicate. Microsoft recommends a separate schema for predicate functions and security policies, and SCHEMABINDING is the default for policies.

CREATE SCHEMA Security;
GO
 
CREATE FUNCTION Security.fn_tenantAccessPredicate(@TenantId int)
    RETURNS TABLE
    WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS fn_result
    WHERE DATABASE_PRINCIPAL_ID() = DATABASE_PRINCIPAL_ID('AppUser')
      AND CAST(SESSION_CONTEXT(N'TenantId') AS int) = @TenantId;
GO

Step 4: bind a filter and a block predicate

The filter predicate silently removes other tenants' rows from SELECT, UPDATE and DELETE. The block predicate stops inserts for the wrong tenant. Because the tenant column can't be updated, an AFTER UPDATE block predicate isn't needed.

CREATE SECURITY POLICY Security.TenantIsolationPolicy
    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);

Add one filter clause and one block clause per tenant-owned table to the same policy.

Step 5: set the tenant in the session from the application

After opening a connection, the application sets the tenant ID. Setting @read_only = 1 prevents the value changing again until the connection is closed and returned to the pool.

EXEC sp_set_session_context @key = N'TenantId', @value = 42, @read_only = 1;

Note what row-level security does and doesn't cover. It applies to every user, including dbo and members of db_owner, so administrators need a policy that explicitly allows them if they must see all rows. It does not stop information leaks through carefully crafted queries that use errors, and Change Data Capture can expose filtered rows to db_owner and to the CDC gating role. The Azure Architecture Center also warns that propagating tenant identity into every query is complex to design and test, which is why many products rely on application-level filtering instead. Treat row-level security as a second line of defense next to tenant-scoped data access code, not as a replacement for it.

Option B: database per tenant in elastic pools

Elastic pools let many databases on the same logical server share a set amount of compute at a set price. They suit databases with low average utilization and infrequent spikes; databases with persistent medium to high utilization shouldn't share a pool. In the vCore model the unit price for pooled compute is the same as for single databases, and there's no per-database charge inside a pool.

Step 1: create the server and pool

az group create --name rg-saas-data --location "East US"
 
az sql server create --name sql-contoso-tenants --resource-group rg-saas-data \
  --location "East US" --admin-user sqladmin --admin-password "<strong-password>"
 
az sql elastic-pool create --resource-group rg-saas-data --server sql-contoso-tenants \
  --name pool-standard-01 --edition GeneralPurpose --family Gen5 --capacity 2

Step 2: provision a database for a new tenant

Automate this as part of tenant onboarding. Microsoft is explicit that manual provisioning becomes overwhelming at scale.

az sql db create --resource-group rg-saas-data --server sql-contoso-tenants \
  --name tenant-fabrikam --elastic-pool pool-standard-01

Moving a tenant to a different pool uses the same command with a different --elastic-pool value. Moving a database into or out of a pool drops connections for a brief period, on the order of seconds, at the end of the operation.

Step 3: keep a tenant catalog

The application needs to know which database serves which tenant, and which schema version that database runs. Keep this in a small catalog database. A minimal design:

CREATE TABLE dbo.TenantCatalog (
    TenantId      int           NOT NULL PRIMARY KEY,
    ServerName    sysname       NOT NULL,
    DatabaseName  sysname       NOT NULL,
    ElasticPool   sysname       NULL,
    SchemaVersion int           NOT NULL,
    Status        varchar(20)   NOT NULL  -- Online, Moving, Offboarding
);

For sharded or hybrid designs, the Elastic Database client library provides shard maps that serve the same purpose, and the split/merge tool marks tenants offline while it moves their data between shards. Use elastic jobs to run schema changes and maintenance across every tenant database instead of looping over them by hand.

Step 4: restore one tenant without touching the others

Point-in-time restore creates a new database on the same server; it can't overwrite an existing database. Restore next to the original, then either swap names with ALTER DATABASE ... MODIFY NAME or copy the needed rows back.

az sql db restore --resource-group rg-saas-data --server sql-contoso-tenants \
  --name tenant-fabrikam --dest-name tenant-fabrikam-restored \
  --time "2026-10-10T08:30:00" --elastic-pool pool-standard-01

Make sure the pool has enough spare capacity for the restored database. The same restore in a shared database would bring back every tenant, and you would then need a script to extract one tenant's rows.

Step 5: give a tenant its own encryption key when required

Azure SQL Database supports transparent data encryption with customer-managed keys at the database level, so each tenant database in a shared pool can have its own TDE protector in a key vault or managed HSM that the customer owns. Each database uses a single user-assigned managed identity to reach the key; system-assigned identities aren't supported at the database level. Use New-AzSqlDatabase with -EncryptionProtector, -AssignIdentity and -UserAssignedIdentityId to create such a database. Keep every old key version, because backups stay encrypted with the protector that was current when they were taken.

Where Azure Cosmos DB fits

If your data store is Azure Cosmos DB, the Architecture Center recommends two models and advises against the others.

• Partition key per tenant. All tenants share one container partitioned by /TenantId and share its RU/s. This is typical for B2C. Isolation is logical only, and you can't set geo-replication, point-in-time restore or customer-managed keys per tenant. A single logical partition is limited to 20 GB, so use hierarchical partition keys such as /TenantId then /id when a tenant can grow beyond that.
• Database account per tenant. Each tenant gets its own account with dedicated RU/s per container. This is typical for B2B premium tiers and is the only Cosmos DB model that supports a customer-managed key per tenant. Fleet pools let accounts in the same fleetspace share a pool of RU/s to avoid sizing every tenant for peak.

Container per tenant and database per tenant within one account are possible but not recommended, because of account limits on containers and on metadata operations, and because neither supports per-tenant customer-managed keys.

Antipatterns to avoid

The Azure Architecture Center calls out four relational antipatterns:

• Table per tenant in one database. It doesn't scale to many tenants and makes querying and updates hard. Use shared tables with a tenant ID, or separate databases.
• Column-level customization for one tenant. Adding columns for individual customers becomes unmanageable. Store tenant-specific data in a dedicated table instead.
• Manual schema changes. Deploy schema through a pipeline and record the schema version per tenant database.
• Single-version dependency. Make each application release compatible with at least the previous schema version, because tenant databases won't all upgrade at the same moment.

Verification

Check isolation and placement before onboarding real customers.

Test the row-level security policy as the application user for two tenants. Each should only see its own rows, and inserting a row for another tenant should fail.

EXECUTE AS USER = 'AppUser';
EXEC sp_set_session_context @key = N'TenantId', @value = 42;
SELECT COUNT(*) AS VisibleRows FROM dbo.Orders;
INSERT INTO dbo.Orders VALUES (7, 1001, 1, 10.00);  -- expected to fail: wrong tenant
REVERT;

Confirm which pool each tenant database sits in by querying from the master database:

SELECT d.name, so.edition, so.service_objective, so.elastic_pool_name
FROM sys.databases AS d
JOIN sys.database_service_objectives AS so ON d.database_id = so.database_id;

List the databases in a pool from the CLI:

az sql elastic-pool list-dbs --resource-group rg-saas-data \
  --server sql-contoso-tenants --name pool-standard-01 --query "[].name"

Troubleshooting

Queries return no rows after enabling row-level security. The filter predicate returns an empty set when nothing matches. The usual cause is a connection that never called sp_set_session_context, or a code path that connects as a different database principal than the one named in the predicate.

An administrator can't see all tenants. Security policies apply to dbo and db_owner as well. Either write the predicate to allow a specific support principal or disable the policy in a controlled, audited way.

A database can't be added to a pool. All databases in a pool must be on the same logical server. You can create several pools per server, but you can't add a database from another server.

Point-in-time restore fails because the name exists. Restore never overwrites. Restore to a new name, then rename the original and the restored database.

Error PerDatabaseCMKKeyRotationAttemptedWhileOldThumbprintInUse. A database-level key rotation is blocked while active virtual log files are still encrypted with an older key. Check sys.dm_db_log_info for active VLFs and retry once enough log has been generated to clear them.

One Cosmos DB tenant is throttling the others. Burst capacity, priority-based execution and throughput buckets help in a shared container. If one tenant consistently needs far more RU/s than the rest, move it to its own account and keep the remaining tenants shared.

Decision checklist

• List which tenants need their own keys, their own restore policy, a specific region or guaranteed performance. Those tenants need dedicated databases or accounts.
• For everyone else, decide between a shared schema with a tenant ID and database per tenant in pools, based on tenant count, size and how often you restore individual tenants.
• Put the tenant ID in every table and lead the primary key with it, even in single-tenant databases, so you can move tenants between shared and dedicated databases later.
• Automate provisioning, schema deployment, restore and offboarding from day one, and keep a tenant catalog with schema versions.
• Meter consumption per tenant; Cosmos DB reports RU charge per request, while shared SQL databases need application-level metering.
• Deploy the whole data tier as part of a repeatable deployment stamp so you can add capacity or regions without redesign. The enterprise Azure migration playbook covers landing zone and subscription design that this fits into.

References

• Architectural approaches for storage and data in multitenant solutions
• Multitenant SaaS database tenancy patterns - Azure SQL Database
• Multitenancy and Azure SQL Database
• Multitenancy and Azure Cosmos DB
• Azure Database for PostgreSQL in a multitenant solution
• Row-level security
• Elastic pools overview
• Manage elastic pools
• Azure CLI example: move a database between elastic pools
• Restore a database from a backup - Azure SQL Database
• az sql db restore
• TDE with database level customer-managed keys