Azure SQL Database — Production Configuration for .NET Applications

Azure SQL Database:
  → Fully managed PaaS: no patching, no backups configuration, no failover setup
  → Automatic backups: point-in-time restore up to 35 days
  → Built-in high availability: 99.99% SLA on General Purpose tier
  → Auto-scaling: serverless tier scales to zero when idle
  → Active Geo-Replication: read replicas in other regions

SQL Server on Azure VM (IaaS):
  → Full control over SQL Server version, features, and configuration
  → Required for: SQL Server Agent jobs, linked servers, CLR assemblies
  → More operational overhead: you manage patching, backups, clustering
  → Choose when Azure SQL Database lacks a feature you depend on

// Azure SQL may have transient connection errors (failover, connection pool churn)
// EF Core built-in retry strategy handles these automatically

builder.Services.AddDbContext<ClinicalDbContext>(options =>
{
    options.UseSqlServer(
        builder.Configuration.GetConnectionString("Clinical"),
        sqlOptions =>
        {
            // Retry up to 5 times for transient Azure SQL errors
            sqlOptions.EnableRetryOnFailure(
                maxRetryCount:       5,
                maxRetryDelay:       TimeSpan.FromSeconds(30),
                errorNumbersToAdd:   null);

            // Command timeout: longer than default for complex clinical reports
            sqlOptions.CommandTimeout(60);
        });
});

// No username/password in the connection string
// Azure App Service uses its Managed Identity to authenticate

// Connection string:
// "Server=tcp:clinical-sql.database.windows.net,1433;
//  Initial Catalog=ClinicalDb;
//  Authentication=Active Directory Managed Identity"

// For local development: use Azure CLI or Visual Studio credentials
// DefaultAzureCredential picks up whatever is available:
// → Managed Identity in production (App Service)
// → Azure CLI credentials locally (az login)
// → Visual Studio / VS Code credentials locally

// If using Microsoft.Data.SqlClient directly (not via EF):
var credential = new DefaultAzureCredential();
var token      = await credential.GetTokenAsync(
    new TokenRequestContext(new[] { "https://database.windows.net/.default" }), ct);

connection.AccessToken = token.Token;
await connection.OpenAsync(ct);

// EF Core handles this automatically when connection string includes
// Authentication=Active Directory Managed Identity

// Azure SQL has per-database connection limits
// Tier:       Max connections
// Basic:      30
// Standard S1: 60
// General Purpose 4 vCores: 408

// Without connection pooling: every request opens and closes a TCP connection
// → Expensive, slow, hits connection limits immediately

// EF Core uses ADO.NET connection pooling by default — no extra configuration needed
// Each DbContext gets a connection from the pool; returns it on Dispose

// For high-concurrency clinical apps: tune the pool size
var connectionString = builder.Configuration.GetConnectionString("Clinical");
var connStr = new SqlConnectionStringBuilder(connectionString)
{
    MaxPoolSize = 100,   // per application instance
    MinPoolSize = 5,     // keep 5 warm connections ready
    ConnectTimeout = 15  // seconds before timeout
}.ToString();

// Monitor connection pool saturation via Azure Monitor:
// Metric: "Connection failed" — indicates pool exhaustion
// Metric: "Connections used" — alert if above 80% of the tier limit

Scenario: Clinical platform deployed to 20 hospital trusts.
  → Each trust has its own database (data isolation)
  → Most trusts have low utilisation during the day
  → Peak hours vary (different time zones, shift patterns)

Problem with 20 separate databases:
  → 20 × General Purpose S2 = 20 × £X/month even when idle
  → Each database is provisioned for peak, not average

Elastic Pool:
  → Pool of DTUs/vCores shared across databases
  → Databases burst to pool capacity when needed
  → Idle databases use near-zero resources from the pool
  → Typically 30-50% cost saving for SaaS/multi-tenant workloads

Azure Bicep:
  resource elasticPool 'Microsoft.Sql/servers/elasticPools@2022-05-01-preview' = {
    name: 'clinical-elastic-pool'
    parent: sqlServer
    sku: { name: 'GP_Gen5', tier: 'GeneralPurpose', capacity: 8 }
    properties: {
      maxSizeBytes: 107374182400 // 100 GB shared across all databases
      perDatabaseSettings: { minCapacity: 0, maxCapacity: 4 }
    }
  }

// Geo-replication: primary in UK South, read replica in UK West
// Clinical reporting queries run against the replica — no load on primary

// Register two DbContexts: one for writes, one for reads
builder.Services.AddDbContext<ClinicalWriteDbContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("ClinicalPrimary")));

builder.Services.AddDbContext<ClinicalReadDbContext>(options =>
    options.UseSqlServer(
        builder.Configuration.GetConnectionString("ClinicalReplica"),
        sql => sql.EnableRetryOnFailure(3, TimeSpan.FromSeconds(5), null)));

// Query handlers: inject the read context for queries
public sealed class GetPrescriptionListHandler
    : IRequestHandler<GetPrescriptionListQuery, PagedList<PrescriptionDto>>
{
    private readonly ClinicalReadDbContext _db; // read replica

    public async Task<PagedList<PrescriptionDto>> Handle(
        GetPrescriptionListQuery query, CancellationToken ct)
    {
        return await _db.Prescriptions
            .AsNoTracking()
            .Where(p => p.WardId == query.WardId)
            .ProjectTo<PrescriptionDto>(_mapper.ConfigurationProvider)
            .ToPagedListAsync(query.Page, query.PageSize, ct);
    }
}

// Replica lag: typically under 2 seconds — acceptable for reporting
// Not acceptable for: prescription approval confirmation (use primary for those reads)