How to Monitor Tds with Vigilmon

Tds is a pure Elixir driver for Microsoft SQL Server and Azure SQL, letting Ecto-powered applications connect to MSSQL databases with full query support and pooled connections. When your Elixir app depends on SQL Server for data — orders, user records, analytics — silent driver crashes or pool exhaustion translate directly into failed requests. Vigilmon gives you the uptime monitoring, heartbeat checks, and alerting to catch those failures before your users do.

What You'll Set Up

• HTTP health endpoint exposing Tds connection pool status
• Cron heartbeat to confirm background database workers are alive
• Latency threshold alerts for slow SQL Server queries
• SSL certificate monitoring for Azure SQL TLS connections

Prerequisites

• Elixir 1.14+ with the tds and ecto_sql packages configured
• A running Microsoft SQL Server or Azure SQL instance
• A free Vigilmon account

Step 1: Add a Health Endpoint That Checks the Tds Connection

Your application needs a lightweight HTTP route that probes the Tds connection pool and returns a status Vigilmon can interpret:

# lib/my_app_web/controllers/health_controller.ex
defmodule MyAppWeb.HealthController do
  use MyAppWeb, :controller

  def index(conn, _params) do
    case Ecto.Adapters.SQL.query(MyApp.Repo, "SELECT 1", []) do
      {:ok, _} ->
        json(conn, %{status: "ok", database: "connected"})
      {:error, reason} ->
        conn
        |> put_status(503)
        |> json(%{status: "error", reason: inspect(reason)})
    end
  end
end

Wire it to a route:

# lib/my_app_web/router.ex
scope "/health" do
  get "/", HealthController, :index
end

Start your app and verify:

curl http://localhost:4000/health
# {"status":"ok","database":"connected"}

Step 2: Add the Monitor in Vigilmon

1. Log in to vigilmon.online and click Add Monitor.
2. Set Type to HTTP / HTTPS.
3. Enter your app's health URL: https://myapp.example.com/health.
4. Set Check interval to 1 minute.
5. Set Expected HTTP status to 200.
6. Under Response body, enable Contains keyword and enter "connected".
7. Click Save.

The keyword check ensures the monitor fails if the health endpoint returns 200 but with an error body — a common pattern when apps return 200 for all routes regardless of database state.

Step 3: Expose Connection Pool Metrics

Tds uses DBConnection under the hood. You can expose pool utilisation in the health response so Vigilmon alerts on degradation before connections are fully exhausted:

defmodule MyAppWeb.HealthController do
  use MyAppWeb, :controller

  def index(conn, _params) do
    pool_size = Application.get_env(:my_app, MyApp.Repo)[:pool_size] || 10

    case Ecto.Adapters.SQL.query(MyApp.Repo, "SELECT 1", []) do
      {:ok, _} ->
        json(conn, %{
          status: "ok",
          database: "connected",
          pool_size: pool_size
        })
      {:error, reason} ->
        conn
        |> put_status(503)
        |> json(%{status: "error", reason: inspect(reason)})
    end
  end
end

If you instrument with Telemetry, emit pool queue time as a metric:

# In your application.ex or a dedicated metrics module
:telemetry.attach(
  "tds-pool-queue",
  [:my_app, :repo, :query],
  fn _event, measurements, _meta, _config ->
    queue_ms = measurements[:queue_time] / 1_000_000
    if queue_ms > 500 do
      Logger.warning("Tds pool queue time high: #{queue_ms}ms")
    end
  end,
  nil
)

Add a second Vigilmon HTTP monitor pointing at a /metrics endpoint (or a Prometheus scrape endpoint via telemetry_metrics_prometheus) and alert when queue_time_ms exceeds your SLA.

Step 4: Heartbeat for Background Database Workers

If your app runs scheduled Tds queries — data exports, sync jobs, ETL pipelines — add a Vigilmon cron heartbeat so you know when a job silently stops running:

1. In Vigilmon, click Add Monitor → Cron Heartbeat.
2. Set Expected ping interval to match your job schedule (e.g. 60 minutes).
3. Copy the heartbeat URL (e.g. https://vigilmon.online/heartbeat/abc123).

Ping Vigilmon at the end of each successful database job:

defmodule MyApp.DatabaseJob do
  require Logger

  def run do
    {:ok, _} = Ecto.Adapters.SQL.query(
      MyApp.Repo,
      "INSERT INTO sync_log (run_at) VALUES (GETDATE())",
      []
    )
    ping_vigilmon()
  end

  defp ping_vigilmon do
    heartbeat_url = System.get_env("VIGILMON_HEARTBEAT_URL")
    :httpc.request(:get, {String.to_charlist(heartbeat_url), []}, [], [])
  end
end

If the job crashes before the ping, Vigilmon alerts after the expected interval lapses.

Step 5: Monitor Azure SQL TLS Certificates

If you connect to Azure SQL over TLS (the default), add a certificate expiry monitor:

1. Open the HTTP monitor you created in Step 2.
2. Enable Monitor SSL certificate.
3. Set Alert when certificate expires in less than 21 days.
4. Click Save.

Azure SQL certificates are managed by Microsoft, but monitoring lets you catch regional outages or misconfigured TLS configurations before they affect connections. Tds establishes TLS by default; set ssl: true and ssl_opts: [verify: :verify_peer] in your Repo config to enforce peer verification:

config :my_app, MyApp.Repo,
  adapter: Ecto.Adapters.Tds,
  hostname: "myserver.database.windows.net",
  username: "myuser",
  password: System.get_env("DB_PASSWORD"),
  database: "mydb",
  ssl: true,
  ssl_opts: [verify: :verify_peer, cacerts: :public_key.cacerts_get()]

Step 6: Configure Alert Channels

1. Go to Alert Channels in Vigilmon and add Slack, email, or PagerDuty.
2. Set Consecutive failures before alert to 2 — transient network blips between your Elixir app and SQL Server are common and resolve within seconds.
3. Use Maintenance windows for planned SQL Server maintenance to suppress false positives:

curl -X POST https://vigilmon.online/api/maintenance \
  -H "Authorization: Bearer YOUR_API_KEY" \
  -d '{"monitor_id": "abc123", "duration_minutes": 30}'

Summary

| Monitor | Target | What It Catches | |---|---|---| | HTTP health check | /health on your app | Tds connection failure, pool errors | | Cron heartbeat | Heartbeat URL | Silent background job failure | | SSL certificate | Azure SQL app endpoint | TLS certificate expiry | | Response keyword | "connected" in body | App up but database disconnected |

Tds and Ecto make SQL Server a first-class citizen in Elixir applications — Vigilmon makes sure that citizen stays healthy. With a health endpoint, a heartbeat for background workers, and SSL monitoring for Azure SQL connections, you have full visibility into the layer between your Elixir app and its MSSQL data.