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:

Dynamic provisioning ensures:

Key Requirements

A strong provisioning pipeline must:

  1. Record tenant metadata.

  2. Create required resources (DB/schema/storage).

  3. Apply baseline migrations.

  4. Configure access: users, roles, encryption keys.

  5. Generate service-level configuration (connection string, storage paths).

  6. Mark tenant as active only after validation.

Architecture Overview

A typical high-level architecture:

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:

ModelDescriptionBest For
Shared DB + Shared SchemaAll tenants share same structure and rowsHigh scale, low security requirements
Shared DB + Separate SchemaEach tenant has an isolated namespaceGood balance of isolation and cost
Separate DB Per TenantTenant has a full database instanceFinance, 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:

Tools that work well:

Security and Compliance Considerations

Testing Strategy

PhaseTest Type
Before CreateInput validation, duplicates
During CreateSQL permissions, timeout, rollback scenarios
After CreateSchema validation, connection test, UI login

Real-World Best Practices

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.