tutorial

Monitoring Azure SQL Database with Vigilmon: Connection Health, DTU/vCore Pressure & Application-Layer Availability Checks

Practical guide to uptime and health monitoring for Azure SQL Database — HTTP health endpoints, DTU and vCore pressure detection, geo-replication lag monitoring, and independent availability checks with Vigilmon.

Azure SQL Database is a fully managed relational database with built-in high availability, automatic backups, and intelligent performance tuning. But your application's availability depends on more than Azure's infrastructure SLA. Connection throttling, DTU/vCore capacity pressure, geo-replication lag, firewall rule mismatches, and maintenance window disruptions can all degrade your application while Azure Monitor metrics remain within acceptable ranges. Vigilmon adds an independent external layer: HTTP health checks on your Azure SQL-connected services that probe real database connectivity, detect capacity pressure, and alert on application-level failures before users notice.

This tutorial shows you how to build reliable monitoring for Azure SQL Database-backed applications using Vigilmon.

What You'll Build

  • A health endpoint that probes real Azure SQL connectivity and capacity health
  • DTU and vCore pressure detection in your health checks
  • A Vigilmon HTTP monitor with timeout and assertion settings tailored to Azure SQL
  • Geo-replication lag monitoring for disaster recovery configurations
  • An alerting strategy for unavailability, throttling, and capacity pressure

Prerequisites

  • Azure subscription with an Azure SQL Database instance (DTU or vCore model)
  • SQL authentication or Azure Active Directory authentication configured
  • An application connected to Azure SQL (any runtime)
  • A free account at vigilmon.online

Step 1: Add a Health Endpoint to Your Azure SQL Service

Add a /health endpoint that performs a lightweight query to verify real connectivity and surface capacity pressure signals.

Node.js (mssql)

// routes/health.js
import sql from 'mssql';

const pool = await sql.connect({
  server: process.env.AZURE_SQL_SERVER,       // e.g. myserver.database.windows.net
  database: process.env.AZURE_SQL_DATABASE,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  options: {
    encrypt: true,                            // Required for Azure SQL
    trustServerCertificate: false,
    connectTimeout: 5000,
    requestTimeout: 8000,
  },
  pool: {
    max: 2,
    min: 0,
    idleTimeoutMillis: 10000,
  },
});

export async function healthHandler(req, res) {
  const checks = {};
  let ok = true;
  const start = Date.now();

  try {
    // Query system DMVs for capacity and connection health
    const result = await pool.request().query(`
      SELECT
        @@VERSION AS sqlVersion,
        (SELECT TOP 1 avg_cpu_percent FROM sys.dm_db_resource_stats ORDER BY end_time DESC) AS cpuPercent,
        (SELECT TOP 1 avg_data_io_percent FROM sys.dm_db_resource_stats ORDER BY end_time DESC) AS dataIoPercent,
        (SELECT TOP 1 avg_log_write_percent FROM sys.dm_db_resource_stats ORDER BY end_time DESC) AS logWritePercent,
        (SELECT TOP 1 avg_memory_usage_percent FROM sys.dm_db_resource_stats ORDER BY end_time DESC) AS memoryPercent,
        (SELECT count(*) FROM sys.dm_exec_sessions WHERE is_user_process = 1) AS activeConnections
    `);
    const row = result.recordset[0];
    const latencyMs = Date.now() - start;

    checks.db = 'ok';
    checks.latencyMs = latencyMs;
    checks.cpuPercent = parseFloat(row.cpuPercent || 0).toFixed(1);
    checks.dataIoPercent = parseFloat(row.dataIoPercent || 0).toFixed(1);
    checks.logWritePercent = parseFloat(row.logWritePercent || 0).toFixed(1);
    checks.memoryPercent = parseFloat(row.memoryPercent || 0).toFixed(1);
    checks.activeConnections = row.activeConnections;

    const cpuThreshold = parseInt(process.env.CPU_WARN_PCT || '85', 10);
    const ioThreshold = parseInt(process.env.IO_WARN_PCT || '85', 10);

    if (parseFloat(checks.cpuPercent) > cpuThreshold) {
      checks.capacity = `cpu pressure: ${checks.cpuPercent}%`;
      ok = false;
    } else if (parseFloat(checks.dataIoPercent) > ioThreshold) {
      checks.capacity = `io pressure: ${checks.dataIoPercent}%`;
      ok = false;
    } else {
      checks.capacity = 'ok';
    }

    if (latencyMs > parseInt(process.env.QUERY_WARN_MS || '3000', 10)) {
      checks.db = 'slow';
      ok = false;
    }
  } catch (err) {
    checks.db = `error: ${err.message}`;
    ok = false;
  }

  res.status(ok ? 200 : 503).json({
    status: ok ? 'ok' : 'degraded',
    service: 'azure-sql-service',
    checks,
  });
}

Python (pyodbc — Azure SQL)

# routers/health.py
import os, time, pyodbc
from fastapi import APIRouter

router = APIRouter()

CONNECTION_STRING = (
    f"DRIVER={{ODBC Driver 18 for SQL Server}};"
    f"SERVER={os.environ['AZURE_SQL_SERVER']};"
    f"DATABASE={os.environ['AZURE_SQL_DATABASE']};"
    f"UID={os.environ['DB_USER']};"
    f"PWD={os.environ['DB_PASSWORD']};"
    "Encrypt=yes;"
    "TrustServerCertificate=no;"
    "Connection Timeout=5;"
)

@router.get("/health")
async def health():
    checks = {}
    ok = True
    start = time.monotonic()

    try:
        with pyodbc.connect(CONNECTION_STRING, timeout=5) as conn:
            with conn.cursor() as cur:
                cur.execute("""
                    SELECT
                        (SELECT TOP 1 avg_cpu_percent FROM sys.dm_db_resource_stats ORDER BY end_time DESC),
                        (SELECT TOP 1 avg_data_io_percent FROM sys.dm_db_resource_stats ORDER BY end_time DESC),
                        (SELECT TOP 1 avg_log_write_percent FROM sys.dm_db_resource_stats ORDER BY end_time DESC),
                        (SELECT count(*) FROM sys.dm_exec_sessions WHERE is_user_process = 1)
                """)
                row = cur.fetchone()
                latency_ms = int((time.monotonic() - start) * 1000)

                cpu_pct = float(row[0] or 0)
                io_pct = float(row[1] or 0)
                log_pct = float(row[2] or 0)
                active_conns = row[3]

                checks['db'] = 'ok'
                checks['latencyMs'] = latency_ms
                checks['cpuPercent'] = round(cpu_pct, 1)
                checks['dataIoPercent'] = round(io_pct, 1)
                checks['logWritePercent'] = round(log_pct, 1)
                checks['activeConnections'] = active_conns

                cpu_threshold = int(os.environ.get('CPU_WARN_PCT', '85'))
                io_threshold = int(os.environ.get('IO_WARN_PCT', '85'))

                if cpu_pct > cpu_threshold:
                    checks['capacity'] = f'cpu pressure: {cpu_pct:.1f}%'
                    ok = False
                elif io_pct > io_threshold:
                    checks['capacity'] = f'io pressure: {io_pct:.1f}%'
                    ok = False
                else:
                    checks['capacity'] = 'ok'

    except Exception as e:
        checks['db'] = f'error: {e}'
        ok = False

    return {
        'status': 'ok' if ok else 'degraded',
        'service': 'azure-sql-service',
        'checks': checks,
    }

Java (JDBC — Azure SQL)

// HealthController.java
import org.springframework.web.bind.annotation.*;
import org.springframework.http.ResponseEntity;
import org.springframework.beans.factory.annotation.Autowired;
import javax.sql.DataSource;
import java.sql.*;
import java.util.*;

@RestController
public class HealthController {

    @Autowired
    private DataSource dataSource;

    @GetMapping("/health")
    public ResponseEntity<Map<String, Object>> health() {
        Map<String, Object> checks = new HashMap<>();
        boolean ok = true;
        long start = System.currentTimeMillis();

        try (Connection conn = dataSource.getConnection();
             Statement stmt = conn.createStatement()) {

            ResultSet rs = stmt.executeQuery(
                "SELECT TOP 1 avg_cpu_percent, avg_data_io_percent " +
                "FROM sys.dm_db_resource_stats ORDER BY end_time DESC"
            );

            long latencyMs = System.currentTimeMillis() - start;
            checks.put("latencyMs", latencyMs);

            if (rs.next()) {
                double cpuPct = rs.getDouble("avg_cpu_percent");
                double ioPct = rs.getDouble("avg_data_io_percent");
                checks.put("cpuPercent", cpuPct);
                checks.put("dataIoPercent", ioPct);

                if (cpuPct > 85 || ioPct > 85) {
                    checks.put("capacity", "pressure detected");
                    ok = false;
                } else {
                    checks.put("capacity", "ok");
                }
            }
            checks.put("db", ok ? "ok" : "degraded");
        } catch (SQLException e) {
            checks.put("db", "error: " + e.getMessage());
            ok = false;
        }

        Map<String, Object> body = new HashMap<>();
        body.put("status", ok ? "ok" : "degraded");
        body.put("service", "azure-sql-service");
        body.put("checks", checks);

        return ResponseEntity.status(ok ? 200 : 503).body(body);
    }
}

Step 2: Configure the Vigilmon HTTP Monitor

  1. Log in to Vigilmon → Add Monitor → HTTP.
  2. URL: your service's health endpoint (e.g. https://api.example.com/health).
  3. Check interval: 60 seconds.
  4. Response timeout: 10 seconds.
  5. Expected status: 200.
  6. JSON assertion: path status, expected value ok.

Tip: Azure SQL's sys.dm_db_resource_stats only retains ~64 rows (about 15 minutes of history at 15-second intervals). Query it in real time from your health endpoint for current capacity state rather than relying on historical averages.

Step 3: Geo-Replication Health Monitoring

For Azure SQL Database with Active Geo-Replication or Auto-Failover Groups, monitor replication health from both the primary and secondary regions.

// Check geo-replication partner status from primary
export async function geoReplicationHealthHandler(req, res) {
  const checks = {};
  let ok = true;

  try {
    const result = await pool.request().query(`
      SELECT
        partner_server,
        partner_database,
        replication_state_desc,
        replication_lag_sec
      FROM sys.dm_geo_replication_link_status
    `);

    if (result.recordset.length === 0) {
      checks.geoReplication = 'no geo-replication configured';
    } else {
      result.recordset.forEach((link, i) => {
        const key = `geoLink${i}`;
        checks[key] = {
          partner: `${link.partner_server}/${link.partner_database}`,
          state: link.replication_state_desc,
          lagSeconds: link.replication_lag_sec,
        };

        if (link.replication_state_desc !== 'CATCH_UP' && link.replication_state_desc !== 'SEEDING') {
          ok = false;
        }

        const maxLagSec = parseInt(process.env.MAX_GEO_LAG_SECONDS || '60', 10);
        if (link.replication_lag_sec > maxLagSec) {
          ok = false;
        }
      });
    }
  } catch (err) {
    checks.geoReplication = `error: ${err.message}`;
    ok = false;
  }

  res.status(ok ? 200 : 503).json({ status: ok ? 'ok' : 'degraded', checks });
}

Configure this as a second Vigilmon monitor on /health/geo with a 5-minute check interval.

Step 4: Heartbeat for Background Workers

Azure SQL-backed background jobs and event processors are invisible to HTTP monitors. Use Vigilmon's Heartbeat monitor to detect when processing stops.

# worker/processor.py
import os, requests

def run_worker_cycle():
    with get_azure_sql_connection() as conn:
        pending = fetch_pending_items(conn)
        for item in pending:
            process_item(conn, item)

    # Ping Vigilmon after each successful cycle
    requests.post(os.environ['VIGILMON_HEARTBEAT_URL'], timeout=5)
    print(f'Processed {len(pending)} items — heartbeat sent')

Set the heartbeat grace period to 3× your expected worker interval.

Step 5: Alerting Strategy

| Alert channel | When to use | |---|---| | Email / PagerDuty | Service returns 503 (database unavailable or throttled) | | Slack webhook | CPU or I/O pressure exceeds 85% | | Slack + Email | Geo-replication lag exceeds threshold | | PagerDuty | Heartbeat missed (background worker stopped) | | Status page | Any user-facing service backed by Azure SQL |

Maintenance window awareness: Azure SQL performs automated maintenance during configured windows. Check your maintenance window schedule in the Azure portal and configure a Vigilmon maintenance window to suppress alerts during planned downtime.

What Vigilmon Catches That Azure Monitor Misses

| Scenario | Azure Monitor | Vigilmon | |---|---|---| | Application can't connect (firewall rule deleted) | No application-layer check | HTTP monitor catches 503 immediately | | DTU/vCore exhausted — queries queueing | dtu_consumption_percent metric | Health endpoint probes actual query latency | | Geo-replication link broken | Replication metrics exist but no HTTP check | /health/geo endpoint catches state change | | Connection string points to wrong server | No routing check | Health probe fails — alert fires | | Background worker stopped silently | No end-to-end check | Heartbeat grace period expires → alert | | Azure Monitor alert action group broken | Alerts silently lost | Vigilmon is external — unaffected |

Azure SQL Database delivers enterprise-grade reliability, but DTU/vCore pressure, geo-replication failures, and connection issues can degrade your application silently. External health checks from Vigilmon give you the independent, application-layer signal that Azure Monitor can't provide.

Start monitoring your Azure SQL applications in under 5 minutes — register free at vigilmon.online.

Monitor your app with Vigilmon

Free plan — 5 monitors, no credit card required. Up and running in 60 seconds.

Start free →
← Back to all articles