Dynamic Provisioning System (Auto-Create DB, Schema, and Storage for New Tenant)

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:
- SQL Resource Provider
- Storage Provider
- Identity Provider (optional)
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.