Multi-Tenancy Best Practices

Production-ready patterns and recommendations for PearDrop multi-tenant applications.

Data Isolation Strategy

PearDrop uses a Shared Database isolation model for all multi-tenant applications:

• All tenants share the same database, schema, and tables
• Data isolation enforced via TenantId column on every entity
• Automatic query filtering ensures tenant isolation
• Cost-effective and scales to thousands of tenants
• Simple operations and maintenance

When to Use Multi-Tenancy

Use multi-tenancy when:

• You have multiple customers/organizations using the same application
• Each customer needs isolated data and cannot see other customers' data
• You want to manage all customers in a single deployment
• Cost optimization is important (shared infrastructure)
• You need simple operations and maintenance

Consider single-tenant deployments when:

• You have very few customers (1-5) with completely different requirements
• Customers require dedicated infrastructure for compliance
• Customers need custom application code (not just configuration)
• Each customer has vastly different scale requirements

Security Hardening

Prevent Tenant Data Leakage

1. Always use tenant-aware queries:

// ❌ BAD: Could expose all tenants' data
public async Task<List<Order>> GetOrders()
{
    return await dbContext.Orders.ToListAsync();
}

// ✅ GOOD: Tenant filter automatically applied
public async Task<List<Order>> GetOrders()
{
    // DbContext query filter enforces tenant isolation
    return await dbContext.Orders.ToListAsync();
}

// ✅ GOOD: Explicit tenant check for extra safety
public async Task<Order?> GetOrder(Guid orderId)
{
    var order = await dbContext.Orders.FindAsync(orderId);
    
    if (order != null && order.TenantId != currentTenantId)
    {
        logger.LogWarning(
            "Tenant {TenantId} attempted to access order {OrderId} from tenant {OwnerTenantId}",
            currentTenantId, orderId, order.TenantId);
        
        return null; // Or throw UnauthorizedAccessException
    }
    
    return order;
}

2. Verify tenant context exists:

public class RequireTenantMiddleware
{
    private readonly RequestDelegate next;
    
    public async Task InvokeAsync(
        HttpContext context,
        IMultiTenantContextAccessor accessor)
    {
        // Skip for owner/admin routes
        if (context.Request.Path.StartsWithSegments("/admin"))
        {
            await next(context);
            return;
        }
        
        var tenantInfo = accessor.MultiTenantContext?.TenantInfo;
        
        if (tenantInfo == null)
        {
            context.Response.StatusCode = 400;
            await context.Response.WriteAsJsonAsync(new
            {
                error = "Tenant context required but not found"
            });
            return;
        }
        
        await next(context);
    }
}

// Register after UseMultiTenant()
app.UseMultiTenant();
app.UseMiddleware<RequireTenantMiddleware>();

3. Unit test tenant isolation:

[Fact]
public async Task GetOrders_OnlyReturnsTenantOrders()
{
    // Arrange
    var tenant1Id = Guid.NewGuid();
    var tenant2Id = Guid.NewGuid();
    
    await SeedOrder(tenant1Id, "ORDER-1");
    await SeedOrder(tenant1Id, "ORDER-2");
    await SeedOrder(tenant2Id, "ORDER-3");
    
    // Act - Set current tenant to tenant1
    SetCurrentTenant(tenant1Id);
    var orders = await queryHandler.GetOrders();
    
    // Assert
    Assert.Equal(2, orders.Count);
    Assert.All(orders, o => Assert.Equal(tenant1Id, o.TenantId));
}

SQL Injection Prevention

Entity Framework protects against SQL injection by default.

// ✅ SAFE: Parameterized query
var orders = await dbContext.Orders
    .Where(o => o.CustomerName == searchTerm)
    .ToListAsync();

// ❌ DANGEROUS: String concatenation
var sql = $"SELECT * FROM Orders WHERE CustomerName = '{searchTerm}'";
var orders = await dbContext.Orders.FromSqlRaw(sql).ToListAsync();

// ✅ SAFE: Use parameters with raw SQL
var sql = "SELECT * FROM Orders WHERE CustomerName = @p0";
var orders = await dbContext.Orders.FromSqlRaw(sql, searchTerm).ToListAsync();

Authentication with Multi-Tenancy

Store tenant info in user claims:

// During login
var claims = new List<Claim>
{
    new Claim(ClaimTypes.NameIdentifier, user.Id.ToString()),
    new Claim("TenantId", user.TenantId.ToString()),
    new Claim("TenantIdentifier", tenantIdentifier)
};

// In controller/handler
var tenantId = Guid.Parse(User.FindFirst("TenantId")!.Value);

Validate user belongs to requested tenant:

public class ValidateTenantMiddleware
{
    public async Task InvokeAsync(
        HttpContext context,
        IMultiTenantContextAccessor accessor)
    {
        if (!context.User.Identity?.IsAuthenticated ?? false)
        {
            await next(context);
            return;
        }
        
        var claimTenantId = context.User.FindFirst("TenantId")?.Value;
        var resolvedTenantId = accessor.MultiTenantContext?.TenantInfo?.Id;
        
        if (claimTenantId != null && claimTenantId != resolvedTenantId)
        {
            logger.LogWarning(
                "User {UserId} with TenantId {ClaimTenantId} attempted to access TenantId {ResolvedTenantId}",
                context.User.FindFirst(ClaimTypes.NameIdentifier)?.Value,
                claimTenantId,
                resolvedTenantId);
            
            context.Response.StatusCode = 403;
            await context.Response.WriteAsync("Access denied to this tenant");
            return;
        }
        
        await next(context);
    }
}

Performance Optimization

Database Indexing

Always index TenantId:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Order>(entity =>
    {
        // Composite index: TenantId + frequently queried columns
        entity.HasIndex(e => new { e.TenantId, e.OrderDate })
              .HasDatabaseName("IX_Orders_TenantId_OrderDate");
        
        // Cover common queries
        entity.HasIndex(e => new { e.TenantId, e.CustomerName })
              .HasDatabaseName("IX_Orders_TenantId_CustomerName");
    });
}

Analyze query patterns:

-- Find missing indexes for tenant queries
SELECT 
    t.name AS TableName,
    s.avg_total_user_cost * s.avg_user_impact * (s.user_seeks + s.user_scans) AS ImprovementMeasure,
    'CREATE INDEX IX_' + t.name + '_TenantId_' + 
        REPLACE(REPLACE(d.equality_columns + ISNULL('_' + d.inequality_columns, ''), '[', ''), ']', '') +
    ' ON ' + t.name + ' (' + d.equality_columns + ISNULL(', ' + d.inequality_columns, '') + ')' AS CreateIndexStatement
FROM sys.dm_db_missing_index_details d
INNER JOIN sys.dm_db_missing_index_groups g ON d.index_handle = g.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats s ON g.index_group_handle = s.group_handle
INNER JOIN sys.tables t ON d.object_id = t.object_id
WHERE d.equality_columns LIKE '%TenantId%'
ORDER BY ImprovementMeasure DESC;

Connection Pooling

Shared database architecture makes connection pooling highly efficient:

• All tenants use the same connection string
• Single connection pool shared across all tenants
• Efficient resource utilization

Configure pool size appropriately:

builder.Services.AddDbContextFactory<OrdersDbContext>(
    options => options
        .UseSqlServer(connectionString)
        .EnableSensitiveDataLogging(isDevelopment)
        .LogTo(Console.WriteLine, LogLevel.Warning),
    poolSize: 128  // Adjust based on concurrent load
);

Monitor connection pool metrics:

// Add performance counters for connection pool monitoring
// .NET Connection Pooling:
// - NumberOfActiveConnections
// - NumberOfFreeConnections
// - NumberOfPooledConnections

Caching Strategies

Tenant-specific cache keys:

public async Task<List<Product>> GetProducts(Guid tenantId)
{
    var cacheKey = $"products:tenant:{tenantId}";
    
    if (cache.TryGetValue(cacheKey, out List<Product> products))
    {
        return products;
    }
    
    products = await dbContext.Products
        .Where(p => p.TenantId == tenantId)
        .ToListAsync();
    
    cache.Set(cacheKey, products, TimeSpan.FromMinutes(10));
    
    return products;
}

Invalidate on updates:

public async Task<Result> UpdateProduct(Guid productId, ProductUpdateDto dto)
{
    // Update database
    var product = await dbContext.Products.FindAsync(productId);
    product.Name = dto.Name;
    await dbContext.SaveChangesAsync();
    
    // Invalidate cache
    var cacheKey = $"products:tenant:{product.TenantId}";
    cache.Remove(cacheKey);
    
    return Result.Success();
}

Cache tenant info:

public class CachedTenantStore : IMultiTenantStore<PearDropTenantInfo>
{
    private readonly IMultiTenantStore<PearDropTenantInfo> innerStore;
    private readonly IMemoryCache cache;
    
    public async Task<PearDropTenantInfo?> TryGetAsync(string identifier)
    {
        var cacheKey = $"tenant:identifier:{identifier}";
        
        if (cache.TryGetValue(cacheKey, out PearDropTenantInfo? tenantInfo))
        {
            return tenantInfo;
        }
        
        tenantInfo = await innerStore.TryGetAsync(identifier);
        
        if (tenantInfo != null)
        {
            cache.Set(cacheKey, tenantInfo, TimeSpan.FromMinutes(30));
        }
        
        return tenantInfo;
    }
}

Monitoring and Observability

Per-Tenant Metrics

public class TenantMetricsMiddleware
{
    private readonly RequestDelegate next;
    private readonly ILogger<TenantMetricsMiddleware> logger;
    
    public async Task InvokeAsync(
        HttpContext context,
        IMultiTenantContextAccessor accessor)
    {
        var stopwatch = Stopwatch.StartNew();
        
        try
        {
            await next(context);
        }
        finally
        {
            stopwatch.Stop();
            
            var tenantInfo = accessor.MultiTenantContext?.TenantInfo;
            
            if (tenantInfo != null)
            {
                logger.LogInformation(
                    "Tenant {TenantId} request to {Path} completed in {ElapsedMs}ms with status {StatusCode}",
                    tenantInfo.Id,
                    context.Request.Path,
                    stopwatch.ElapsedMilliseconds,
                    context.Response.StatusCode);
            }
        }
    }
}

Application Insights

services.AddApplicationInsightsTelemetry(options =>
{
    options.ConnectionString = configuration["ApplicationInsights:ConnectionString"];
});

// Add tenant context to telemetry
services.AddSingleton<ITelemetryInitializer, TenantTelemetryInitializer>();

public class TenantTelemetryInitializer : ITelemetryInitializer
{
    private readonly IHttpContextAccessor httpContextAccessor;
    
    public void Initialize(ITelemetry telemetry)
    {
        var context = httpContextAccessor.HttpContext;
        if (context == null) return;
        
        var accessor = context.RequestServices
            .GetService<IMultiTenantContextAccessor>();
        
        var tenantInfo = accessor?.MultiTenantContext?.TenantInfo;
        
        if (tenantInfo != null && telemetry is ISupportProperties props)
        {
            props.Properties["TenantId"] = tenantInfo.Id;
            props.Properties["TenantIdentifier"] = tenantInfo.Identifier;
        }
    }
}

Health Checks

builder.Services.AddHealthChecks()
    .AddCheck<TenantDatabaseHealthCheck>("tenant_databases")
    .AddCheck<TenantStoreHealthCheck>("tenant_store");

public class TenantDatabaseHealthCheck : IHealthCheck
{
    private readonly IMultitenancyReadModels readModels;
    
    public async Task<HealthCheckResult> CheckHealthAsync(
        HealthCheckContext context,
        CancellationToken cancellationToken)
    {
        try
        {
            // Check if we can query tenants
            var count = await readModels.Tenants.CountAsync(cancellationToken);
            
            return HealthCheckResult.Healthy($"{count} tenants configured");
        }
        catch (Exception ex)
        {
            return HealthCheckResult.Unhealthy("Cannot access tenant store", ex);
        }
    }
}

Backup and Recovery

Database Backups

Single backup covers all tenants:

-- Full backup
BACKUP DATABASE [MyApp]
TO DISK = 'C:\Backups\MyApp_Full.bak'
WITH COMPRESSION, INIT;

-- Differential backup
BACKUP DATABASE [MyApp]
TO DISK = 'C:\Backups\MyApp_Diff.bak'
WITH DIFFERENTIAL, COMPRESSION, INIT;

-- Transaction log backup (for point-in-time recovery)
BACKUP LOG [MyApp]
TO DISK = 'C:\Backups\MyApp_Log.trn'
WITH COMPRESSION, INIT;

Restore Single Tenant Data

If you need to restore data for a specific tenant:

-- 1. Restore to temporary database
RESTORE DATABASE [MyApp_Temp]
FROM DISK = 'C:\Backups\MyApp_Full.bak'
WITH MOVE 'MyApp' TO 'C:\Temp\MyApp_Temp.mdf',
     MOVE 'MyApp_Log' TO 'C:\Temp\MyApp_Temp_Log.ldf',
     REPLACE;

-- 2. Copy tenant data from backup
DECLARE @TenantId uniqueidentifier = '550e8400-e29b-41d4-a716-446655440000';

-- Example: Restore orders for specific tenant
INSERT INTO [MyApp].[dbo].[Orders]
SELECT * FROM [MyApp_Temp].[dbo].[Orders]
WHERE TenantId = @TenantId
  AND Id NOT IN (SELECT Id FROM [MyApp].[dbo].[Orders]);

-- Repeat for other tables as needed

-- 3. Clean up
DROP DATABASE [MyApp_Temp];

Automated Backup Script

public async Task PerformBackup(CancellationToken cancellationToken)
{
    var backupPath = $@"C:\Backups\MyApp_{DateTime.UtcNow:yyyyMMddHHmmss}.bak";
    
    await using var connection = new SqlConnection(connectionString);
    await connection.OpenAsync(cancellationToken);
    
    await using var command = connection.CreateCommand();
    command.CommandText = $@"
        BACKUP DATABASE [MyApp]
        TO DISK = @BackupPath
        WITH COMPRESSION, INIT, STATS = 10;
    ";
    command.Parameters.AddWithValue("@BackupPath", backupPath);
    
    await command.ExecuteNonQueryAsync(cancellationToken);
    
    logger.LogInformation("Database backup completed: {Path}", backupPath);
}

Backup Best Practices

• Automate backups - Schedule regular full and differential backups
• Test restores - Regularly verify backups can be restored
• Off-site storage - Store backups in separate geographic location
• Retention policy - Keep backups for compliance requirements (e.g., 7 years for financial data)
• Monitor backup jobs - Alert on failed or missing backups

Testing Multi-Tenancy

Integration Tests

public class MultiTenantIntegrationTests : IClassFixture<WebApplicationFactory<Program>>
{
    private readonly WebApplicationFactory<Program> factory;
    
    [Fact]
    public async Task GetOrders_ReturnsTenantOrders_WhenSubdomainProvided()
    {
        // Arrange
        var client = factory.CreateClient();
        client.DefaultRequestHeaders.Host = "tenant1.localhost";
        
        // Act
        var response = await client.GetAsync("/api/orders");
        var orders = await response.Content.ReadFromJsonAsync<List<OrderDto>>();
        
        // Assert
        Assert.NotNull(orders);
        Assert.All(orders, o => Assert.Equal("tenant1", o.TenantIdentifier));
    }
    
    [Fact]
    public async Task GetOrders_ReturnsEmpty_WhenDifferentTenant()
    {
        // Arrange
        var client = factory.CreateClient();
        client.DefaultRequestHeaders.Host = "tenant2.localhost";
        
        // Act
        var response = await client.GetAsync("/api/orders");
        var orders = await response.Content.ReadFromJsonAsync<List<OrderDto>>();
        
        // Assert
        Assert.NotNull(orders);
        Assert.Empty(orders); // tenant2 has no orders
    }
}

Unit Tests with Tenant Context

public class OrderServiceTests
{
    [Fact]
    public async Task CreateOrder_SetsTenantId_FromContext()
    {
        // Arrange
        var tenantId = Guid.NewGuid();
        var mockAccessor = new Mock<IMultiTenantContextAccessor>();
        mockAccessor.Setup(x => x.MultiTenantContext.TenantInfo.Id)
            .Returns(tenantId.ToString());
        
        var service = new OrderService(mockAccessor.Object, ...);
        
        // Act
        var order = await service.CreateOrder(new CreateOrderDto { ... });
        
        // Assert
        Assert.Equal(tenantId, order.TenantId);
    }
}

Deployment Considerations

Database Migrations

Shared database architecture makes migrations simple:

# Apply migrations (updates all tenants at once)
dotnet ef database update

Production migration deployment:

// Apply migrations at application startup
public static async Task Main(string[] args)
{
    var host = CreateHostBuilder(args).Build();
    
    // Apply migrations automatically
    using (var scope = host.Services.CreateScope())
    {
        var dbContext = scope.ServiceProvider.GetRequiredService<MyAppDbContext>();
        await dbContext.Database.MigrateAsync();
    }
    
    await host.RunAsync();
}

Zero-Downtime Deployments

Rolling updates:

1. Deploy new version alongside old
2. Route new requests to new version
3. Wait for old version requests to complete
4. Shut down old version

Database compatibility:

// Migration 1: Add new column (nullable)
ALTER TABLE Orders ADD NewColumn nvarchar(100) NULL;

// Deploy new code that uses NewColumn

// Migration 2: Make column required
UPDATE Orders SET NewColumn = 'default' WHERE NewColumn IS NULL;
ALTER TABLE Orders ALTER COLUMN NewColumn nvarchar(100) NOT NULL;

---

Common Pitfalls

❌ Forgetting Tenant Filter

// BAD: Returns all orders across all tenants
var orders = await dbContext.Orders
    .IgnoreQueryFilters()  // Dangerous!
    .ToListAsync();

Solution: Only use IgnoreQueryFilters() in explicitly authorized admin operations with proper authorization checks.

❌ Hard-Coding Tenant IDs

// BAD: Hard-coded tenant ID
var orders = await dbContext.Orders
    .Where(o => o.TenantId == Guid.Parse("123..."))
    .ToListAsync();

Solution: Always use current tenant from context.

❌ Mixing Tenant Strategies

// BAD: Some queries use TenantId, some use subdomain
// Inconsistent behavior across app

Solution: Use one tenant resolution strategy consistently. Chain strategies with clear fallback order.

❌ Cross-Tenant Foreign Keys

// BAD: Order from Tenant A references Customer from Tenant B
public class Order
{
    public Guid CustomerId { get; set; }  // Could reference wrong tenant!
}

Solution: Always scope foreign keys within tenant:

var customer = await dbContext.Customers
    .FirstOrDefaultAsync(c => c.Id == customerId && c.TenantId == currentTenantId);

---

Next Steps

• Multi-Tenancy Overview - Architecture and concepts
• Configuration - Complete settings reference
• Tenant Management - CRUD operations