Modern multi-tenant SaaS platforms must scale onboarding without manual intervention. A new customer should receive isolated resources such as a database, schema, file storage container, and configuration without developers or DBAs performing setup. This requirement leads to a Dynamic Provisioning System, where infrastructure for each new tenant is created automatically when onboarding completes.
This article explains how to design and implement such a system using .NET Core for backend orchestration, SQL Server for database provisioning, and optional cloud storage such as Azure Blob or AWS S3.
What Problem Does This Solve?
Manual provisioning becomes a bottleneck when:
You onboard many tenants.
You operate across regions.
You need strict data isolation for regulation (HIPAA, finance, government).
Dynamic provisioning ensures:
Zero manual DBA work.
Predictable and repeatable provisioning steps.
Consistent configuration per tenant.
Support for database-per-tenant and schema-per-tenant models.
Key Requirements
A strong provisioning pipeline must:
Record tenant metadata.
Create required resources (DB/schema/storage).
Apply baseline migrations.
Configure access: users, roles, encryption keys.
Generate service-level configuration (connection string, storage paths).
Mark tenant as active only after validation.
Architecture Overview
A typical high-level architecture:
API Layer: Receives onboarding request.
Provisioning Service: Executes the procedure.
Resource Providers:
Tenant Registry: Stores provisioning status and metadata.
Workflow Diagram
Tenant Signup
|
v
Provisioning Service
|
|----> Create DB or Schema
|----> Apply Migration
|----> Create Storage Bucket
|----> Register Security Policies
|
v
Store Metadata in Tenant Registry
|
v
Tenant Activated
Flowchart
START
|
v
Receive tenant onboarding request
|
v
Does tenant exist?
|----YES--> Reject and exit
|
NO
|
v
Create database/schema
|
v
Apply migrations and seed data
|
v
Create storage container
|
v
Generate connection + storage configuration
|
v
Save configuration to Tenant Registry
|
v
Mark tenant active
|
END
Provisioning Strategies
There are three primary patterns:
| Model | Description | Best For |
|---|
| Shared DB + Shared Schema | All tenants share same structure and rows | High scale, low security requirements |
| Shared DB + Separate Schema | Each tenant has an isolated namespace | Good balance of isolation and cost |
| Separate DB Per Tenant | Tenant has a full database instance | Finance, healthcare, government, compliance-heavy |
For this article, we focus on Schema-per-Tenant and Database-per-Tenant.
Database Provisioning Example (SQL Server)
Step 1: Template SQL Script
CREATE DATABASE [{TenantDbName}];
GO
USE [{TenantDbName}];
CREATE SCHEMA Core AUTHORIZATION dbo;
CREATE TABLE Core.Users (
UserId UNIQUEIDENTIFIER DEFAULT NEWID(),
Username NVARCHAR(200),
CreatedDate DATETIME2 DEFAULT SYSDATETIME()
);
Step 2: .NET Execution Code
public async Task<bool> CreateTenantDatabase(string tenantName)
{
var dbName = $"TENANT_{tenantName.Replace(" ", "_").ToUpper()}";
var sql = $"CREATE DATABASE [{dbName}]";
using var connection = new SqlConnection(_masterConnectionString);
await connection.OpenAsync();
using var command = new SqlCommand(sql, connection);
await command.ExecuteNonQueryAsync();
return true;
}
Applying Migrations
Use Entity Framework Core:
var tenantDbContext = new TenantDbContext(
new DbContextOptionsBuilder<TenantDbContext>()
.UseSqlServer(tenantConnectionString)
.Options);
tenantDbContext.Database.Migrate();
Storage Provisioning (Azure Example)
var blobClient = new BlobServiceClient(_storageConnectionString);
var container = blobClient.GetBlobContainerClient($"tenant-{tenantId}");
await container.CreateIfNotExistsAsync();
Tenant Registry Table
CREATE TABLE TenantRegistry (
TenantId UNIQUEIDENTIFIER PRIMARY KEY,
Name NVARCHAR(255),
DbConnectionString NVARCHAR(MAX),
StoragePath NVARCHAR(MAX),
Status NVARCHAR(20),
CreatedDate DATETIME2 DEFAULT SYSDATETIME()
);
Dynamic Routing at Runtime
When a tenant logs in, middleware determines tenant context and resolves configuration dynamically.
public class TenantResolverMiddleware
{
private readonly RequestDelegate _next;
public TenantResolverMiddleware(RequestDelegate next)
{
_next = next;
}
public async Task Invoke(HttpContext context, TenantRegistryService registry)
{
var host = context.Request.Headers["X-Tenant"].FirstOrDefault();
var tenantConfig = await registry.GetTenantConfigAsync(host);
TenantContext.Set(tenantConfig);
await _next(context);
}
}
Automation and Scheduling
A scheduler can:
Archive inactive tenants.
Upgrade outdated schema versions.
Replicate backup data across regions.
Tools that work well:
SQL Agent
Kubernetes Jobs
Azure Automation
Hangfire
Quartz.NET
Security and Compliance Considerations
Encrypt all connection strings.
Rotate access keys regularly.
Ensure tenant resources cannot reference each other.
Use row-level firewalling for shared hosting VM.
Testing Strategy
| Phase | Test Type |
|---|
| Before Create | Input validation, duplicates |
| During Create | SQL permissions, timeout, rollback scenarios |
| After Create | Schema validation, connection test, UI login |
Real-World Best Practices
Never allow manual provisioning in production.
Provisioning events should be auditable.
Allow retryable provisioning tasks.
Build rollback scripts or snapshots.
Conclusion
A Dynamic Provisioning System transforms a multi-tenant SaaS into a scalable, automated onboarding platform. Using .NET Core for orchestration, SQL Server for tenant isolation, and automated schema and storage provisioning, teams can deliver secure, quick, and consistent tenant environments with minimal operational cost.
This approach is essential for enterprise SaaS platforms that want to scale globally while maintaining strict compliance and isolation.