Production Readiness — Azure SQL, Connection Resilience and Pre-Deployment Checklist

The final lesson brings together the complete SQL Server knowledge from Part 6 into a production readiness checklist for the BlogApp. Moving from LocalDB development to Azure SQL production involves connection string changes, firewall configuration, Managed Identity authentication, and performance validation. The checklist ensures nothing is forgotten before go-live — from security settings to backup schedules to monitoring configuration.

Azure SQL and Production Checklist #

// ── Azure SQL connection with Managed Identity (no password) ──────────────
// appsettings.Production.json:
// {
//   "ConnectionStrings": {
//     "Default": "Server=blogapp.database.windows.net;Database=BlogApp;
//                 Authentication=Active Directory Default;Encrypt=True;
//                 TrustServerCertificate=False;Application Name=BlogApp"
//   }
// }

// Program.cs — configure for Azure SQL resilience:
builder.Services.AddDbContext<AppDbContext>((sp, opts) => {
    opts.UseSqlServer(
        builder.Configuration.GetConnectionString("Default"),
        sqlOpts => {
            sqlOpts.EnableRetryOnFailure(
                maxRetryCount:    5,
                maxRetryDelay:    TimeSpan.FromSeconds(30),
                errorNumbersToAdd: null  // includes all transient Azure SQL errors
            );
            sqlOpts.CommandTimeout(30);
        });
    opts.AddInterceptors(
        sp.GetRequiredService<AuditInterceptor>(),
        sp.GetRequiredService<SoftDeleteInterceptor>()
    );
});

// ── Health check — database connectivity probe ────────────────────────────
builder.Services.AddHealthChecks()
    .AddDbContextCheck<AppDbContext>("database",
        failureStatus: HealthStatus.Degraded,
        tags: ["db", "sql"]);

app.MapHealthChecks("/health/db", new HealthCheckOptions {
    Predicate = check => check.Tags.Contains("db"),
});
// Kubernetes readiness probe: GET /health/db → 200 OK when DB reachable

-- ════════════════════════════════════════════════════════════════════════════
-- BLOGAPP PRODUCTION READINESS CHECKLIST
-- ════════════════════════════════════════════════════════════════════════════

-- ── SECURITY ──────────────────────────────────────────────────────────────
-- [ ] sa account disabled:
SELECT name, is_disabled FROM sys.server_principals WHERE name = 'sa';

-- [ ] Application login uses least privilege (no db_owner, no sysadmin):
SELECT dp.name, r.name AS Role
FROM   sys.database_role_members m
JOIN   sys.database_principals dp ON dp.principal_id = m.member_principal_id
JOIN   sys.database_principals r  ON r.principal_id  = m.role_principal_id
WHERE  dp.name = 'blogapp_api';

-- [ ] No passwords in appsettings — using Managed Identity or Key Vault
-- [ ] TrustServerCertificate=False in production connection string

-- ── PERFORMANCE ───────────────────────────────────────────────────────────
-- [ ] RCSI enabled:
SELECT is_read_committed_snapshot_on FROM sys.databases WHERE name = 'BlogApp';
-- Should return 1

-- [ ] Query Store enabled:
SELECT actual_state_desc FROM sys.database_query_store_options;
-- Should return 'READ_WRITE'

-- [ ] All production indexes in place:
SELECT i.name, i.type_desc, STATS_DATE(i.object_id, i.index_id) AS LastStatsUpdate
FROM sys.indexes i JOIN sys.tables t ON t.object_id = i.object_id
WHERE t.name IN ('Posts', 'Users', 'Tags', 'PostTags', 'Comments')
ORDER BY t.name, i.name;

-- [ ] Filtered index for published posts:
SELECT name FROM sys.indexes WHERE name = 'IX_Posts_Published_Date';

-- ── RELIABILITY ───────────────────────────────────────────────────────────
-- [ ] FULL recovery model (for log backups + point-in-time recovery):
SELECT recovery_model_desc FROM sys.databases WHERE name = 'BlogApp';
-- Should return 'FULL'

-- [ ] AUTO_SHRINK OFF:
SELECT is_auto_shrink_on FROM sys.databases WHERE name = 'BlogApp';
-- Should return 0

-- [ ] AUTO_CLOSE OFF:
SELECT is_auto_close_on FROM sys.databases WHERE name = 'BlogApp';
-- Should return 0

-- [ ] Backup jobs configured and last backup recent:
SELECT database_name, backup_type = CASE type WHEN 'D' THEN 'Full'
    WHEN 'I' THEN 'Differential' WHEN 'L' THEN 'Log' END,
    backup_finish_date
FROM msdb.dbo.backupset
WHERE database_name = 'BlogApp'
ORDER BY backup_finish_date DESC;

-- ── MAINTENANCE ───────────────────────────────────────────────────────────
-- [ ] DBCC CHECKDB scheduled (no integrity errors):
-- Check CommandLog table (Ola Hallengren) or SQL Server error log

-- [ ] Index maintenance job configured:
SELECT name, enabled, next_run_date, next_run_time FROM msdb.dbo.sysjobs
WHERE name LIKE '%Index%';

-- ── MONITORING ────────────────────────────────────────────────────────────
-- [ ] Alerts configured for severity 17+ errors
-- [ ] Disk space monitoring in place
-- [ ] PLE baseline established
-- [ ] Connection string Application Name set (for profiling attribution)

🎓 Part 6 Complete — SQL Server Summary #

Part 6 has covered SQL Server from setup through production — 10 chapters, 50 lessons, progressing from LocalDB installation to Azure SQL deployment with security, performance, and reliability best practices. The BlogApp now has a complete, production-ready database layer: a well-designed schema, correct indexes, optimised queries, proper concurrency handling, and an EF Core integration that uses advanced features correctly.

The next part — Part 7: Full Stack Integration — connects the Angular frontend to the ASP.NET Core API, implementing real end-to-end features with the complete C# + Angular + SQL Server stack working together.

Common Mistakes #

Mistake 1 — Not enabling RCSI before go-live (read/write blocking under load) #

❌ Wrong — default READ COMMITTED without RCSI; first load test reveals massive blocking between reads and writes.

✅ Correct — include ALTER DATABASE BlogApp SET READ_COMMITTED_SNAPSHOT ON in the production deployment script.

Mistake 2 — No health check endpoint for load balancer / Kubernetes probes #

❌ Wrong — no health endpoint; load balancer routes traffic to unhealthy pods that cannot connect to the database.

✅ Correct — MapHealthChecks("/health/db") with database check; Kubernetes readiness probe uses this endpoint.