Skip to content

SQL Server ​

Microtec uses a shared Azure SQL virtual machine to host all tenant and admin databases across non-production environments, and a dedicated SQL VM in production. The database-per-tenant model is enforced at the application layer.


Infrastructure ​

PropertyValue
Resource groupmic-backend-shared-sql-rg
VM public IP20.50.120.95
SSH accessssh -i ~/.ssh/mic-shared-sql sqladmin@20.50.120.95
SQL port1433
SQL versionSQL Server 2022
AuthenticationSQL Authentication + Windows Auth

Shared SQL VM Is Cross-Environment

The SQL VM at 20.50.120.95 serves dev, stage, preprod, and uat environments. Each environment uses a separate SQL login and database naming prefix to maintain isolation. Production uses a dedicated SQL VM (separate resource group, not documented here for security reasons — credentials are in the production Key Vault only).


Database-Per-Tenant Model ​

Each Microtec tenant (customer) has its own database. The application never shares a database between tenants. This model provides:

  • Data isolation: A bug in one tenant's query cannot affect another tenant's data
  • Independent backup and restore: Tenant databases can be backed up and restored independently
  • Compliance: Tenant data can be deleted completely by dropping a single database
  • Scaling: High-volume tenants can be migrated to a dedicated SQL instance without changing application code

Database Naming Convention ​

Database typeName patternExample
Admin/sharedMicrotec_Admin_{env}Microtec_Admin_Dev
Tenant{TenantId}_DBACME_Corp_DB

The TenantId is the slug set during tenant onboarding in the Business Owner portal.

Tenant Resolution ​

The ITenantProvider service resolves the current tenant from the incoming HTTP request (subdomain or JWT claim) and provides the connection string via ITenantContextManager:

csharp
// Simplified tenant connection string resolution
public class TenantContextManager : ITenantContextManager
{
    public string GetConnectionString(string tenantId) =>
        $"Server={_sqlHost};Database={tenantId}_DB;" +
        $"User Id={_sqlUser};Password={_sqlPassword};" +
        "TrustServerCertificate=True;";
}

Connection String Format ​

Connection strings follow this pattern:

Server=mic-backend-shared-sql.database.windows.net,1433;
Database={database};
User Id={user};
Password={password};
TrustServerCertificate=True;
Encrypt=True;
Connection Timeout=30;
Max Pool Size=100;

TrustServerCertificate

TrustServerCertificate=True is set because the SQL VM uses a self-signed certificate. In production with a properly signed certificate, this can be set to False for stricter TLS validation.

Connection strings are stored in Key Vault (double-dash notation) and never appear in appsettings.json:

KV secret: ConnectionStrings--DefaultConnection

EF Core Migrations ​

Each bounded context (Accounting, Inventory, HR, etc.) maintains its own EF Core DbContext and migration history. Migrations are applied automatically at service startup using the MigrateAsync() pattern.

Running Migrations ​

Migrations run automatically in the startup probe window. For manual execution:

bash
# Apply migrations for a specific context
cd Platforms/Src/AppsPortal/Accounting/AppsPortal.Infrastructure

dotnet ef database update \
  --context AccountingDbContext \
  --connection "Server=...;Database=ACME_Corp_DB;..."

Generating a Migration ​

bash
cd Platforms/Src/AppsPortal/Accounting/AppsPortal.Infrastructure

dotnet ef migrations add "Add_Invoice_PaymentStatus" \
  --context AccountingDbContext \
  --output-dir Migrations/Accounting

Migration Conventions ​

RuleDescription
Migration namesDescriptive PascalCase: Add_{Feature}, Rename_{Column}To_{NewName}
No data in migrationsNever seed data in Up() methods; use dedicated data seeder services
Backwards compatibleUp() must not break the previous version of the service (zero-downtime deploy)
Foreign keysAlways add FK constraints explicitly; never rely on EF Core's shadow properties alone

Non-Backwards-Compatible Migrations

Dropping a column or renaming a table is non-backwards-compatible. The old version of the service will fail after migration. These changes require a coordinated multi-step deploy:

  1. Deploy service version that no longer reads/writes the old column/table
  2. Apply migration that drops/renames
  3. Optionally remove the transitional compatibility code in a follow-up deploy

Admin Database ​

The admin database (Microtec_Admin_{env}) stores cross-tenant data managed by the Business Owner portal:

TableContents
TenantsTenant registry (Id, Name, ConnectionString reference, Status)
TenantSubscriptionsActive modules per tenant
TenantUsersMapping of Keycloak user IDs to tenant context
AuditLogCross-tenant admin actions

The admin database uses a separate connection string (AdminConnectionString) and a separate AdminDbContext.


Maintenance Tasks ​

Check Database Sizes ​

sql
SELECT
    DB_NAME(database_id) AS DatabaseName,
    SUM(size * 8 / 1024) AS TotalSizeMB
FROM sys.master_files
GROUP BY database_id
ORDER BY TotalSizeMB DESC;

List All Tenant Databases ​

sql
SELECT name
FROM sys.databases
WHERE name LIKE '%_DB'
ORDER BY name;

Kill Active Connections (before restore) ​

sql
ALTER DATABASE [ACME_Corp_DB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

Check Long-Running Queries ​

sql
SELECT
    r.session_id,
    r.status,
    r.cpu_time,
    r.total_elapsed_time / 1000 AS elapsed_seconds,
    SUBSTRING(st.text, (r.statement_start_offset/2)+1,
        ((r.statement_end_offset - r.statement_start_offset)/2)+1) AS query_text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) st
WHERE r.total_elapsed_time > 10000   -- >10 seconds
ORDER BY r.total_elapsed_time DESC;

Internal Documentation — Microtec