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

Property	Value
Resource group	mic-backend-shared-sql-rg
VM public IP	20.50.120.95
SSH access	ssh -i ~/.ssh/mic-shared-sql sqladmin@20.50.120.95
SQL port	1433
SQL version	SQL Server 2022
Authentication	SQL 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 type	Name pattern	Example
Admin/shared	Microtec_Admin_{env}	Microtec_Admin_Dev
Tenant	{TenantId}_DB	ACME_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:

// 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:

# 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

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

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

Migration Conventions

Rule	Description
Migration names	Descriptive PascalCase: Add_{Feature}, Rename_{Column}To_{NewName}
No data in migrations	Never seed data in Up() methods; use dedicated data seeder services
Backwards compatible	Up() must not break the previous version of the service (zero-downtime deploy)
Foreign keys	Always 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:

Table	Contents
Tenants	Tenant registry (Id, Name, ConnectionString reference, Status)
TenantSubscriptions	Active modules per tenant
TenantUsers	Mapping of Keycloak user IDs to tenant context
AuditLog	Cross-tenant admin actions

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

---

Maintenance Tasks

Check Database Sizes

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

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

Kill Active Connections (before restore)

ALTER DATABASE [ACME_Corp_DB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

Check Long-Running Queries

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;