Monitoring

The MSSQL operator provides built-in Prometheus monitoring for SQL Server instances using sql_exporter as a sidecar container. When monitoring is enabled, each MSSQL pod gets an exporter sidecar that queries SQL Server DMVs and exposes metrics on an HTTP endpoint.

Enabling Monitoring

Add the monitoring section to your MSSQLInstance or MSSQLAvailabilityGroup CR:

apiVersion: mssql.solanica.io/v1alpha1
kind: MSSQLInstance
metadata:
  name: my-mssql
spec:
  size: 3
  acceptEULA: true
  saPasswordSecret: my-mssql-secret
  storage:
    size: 10Gi
  monitoring:
    enabled: true
    enablePodMonitor: true

This will:

1. Inject a sql-exporter sidecar into each MSSQL pod
2. Create a ConfigMap with the exporter configuration and default monitoring queries
3. Create a PodMonitor resource for the Prometheus Operator (if enablePodMonitor: true)

Inspecting Metrics

You can inspect the exported metrics from any pod:

POD_IP=$(kubectl get pod my-mssql-0 --template '{{.status.podIP}}')
kubectl exec -ti curl-pod -- curl -s ${POD_IP}:9187/metrics

Monitoring Spec Reference

Field	Type	Default	Description
enabled	bool	false	Enable the sql_exporter sidecar
enablePodMonitor	bool	false	Auto-create a PodMonitor for Prometheus Operator
image	string	burningalchemist/sql_exporter:0.16.0	Exporter container image
port	int32	9399	Metrics HTTP endpoint port
resources	ResourceRequirements	—	CPU/memory for the exporter sidecar
customQueriesConfigMap	[]CustomQueryRef	—	ConfigMap references for custom queries
customQueriesSecret	[]CustomQueryRef	—	Secret references for custom queries
disableDefaultQueries	bool	false	Skip built-in default monitoring queries
tls.enabled	*bool	—	Enable TLS on the metrics endpoint (see Metrics Endpoint TLS)

Default Metrics

When disableDefaultQueries is false (the default), the operator ships a comprehensive set of MSSQL metrics:

Instance Health

Metric	Type	Description
mssql_up	gauge	1 if SQL Server is responding
mssql_instance_uptime_seconds	gauge	Seconds since last restart

Connections

Metric	Type	Labels	Description
mssql_connections	gauge	status	Sessions by status (running, sleeping, etc.)
mssql_user_connections	gauge	—	Active user connection count
mssql_login_errors_total	counter	—	Failed login attempts

Performance Counters

Metric	Type	Description
mssql_batch_requests_total	counter	Total batch requests
mssql_compilations_total	counter	SQL compilations
mssql_recompilations_total	counter	SQL re-compilations
mssql_buffer_cache_hit_ratio	gauge	Buffer cache hit ratio (%)
mssql_page_life_expectancy_seconds	gauge	Page life expectancy
mssql_lazy_writes_total	counter	Lazy writes
mssql_page_splits_total	counter	Page splits
mssql_checkpoint_pages_total	counter	Checkpoint pages flushed
mssql_lock_waits_total	counter	Lock waits
mssql_deadlocks_total	counter	Deadlocks
mssql_transactions_total	counter	Transactions per database
mssql_memory_grants_pending	gauge	Processes waiting for memory
mssql_total_server_memory_bytes	gauge	Memory consumed by SQL Server
mssql_target_server_memory_bytes	gauge	Target memory for SQL Server

Wait Statistics

Metric	Type	Labels	Description
mssql_wait_time_seconds	gauge	wait_type	Cumulative wait time (top 10 waits)

Availability Group Health

Metric	Type	Labels	Description
mssql_ag_replica_role	gauge	ag_name, replica_name	1=PRIMARY, 2=SECONDARY
mssql_ag_replica_connected	gauge	ag_name, replica_name	1 if connected
mssql_ag_replica_sync_health	gauge	ag_name, replica_name	0=NOT_HEALTHY, 1=PARTIAL, 2=HEALTHY
mssql_ag_database_sync_state	gauge	ag_name, database_name, replica_name	Synchronization state
mssql_ag_database_log_send_queue_size_bytes	gauge	ag_name, database_name, replica_name	Log send queue size

Database & IO

Metric	Type	Labels	Description
mssql_database_size_bytes	gauge	database, file_type	Database file size
mssql_io_stall_seconds	gauge	database, operation	IO stall time
mssql_io_reads_total	counter	database	Total IO reads
mssql_io_writes_total	counter	database	Total IO writes

Metrics Endpoint TLS

By default, the sql_exporter metrics endpoint follows the database's TLS setting:

• If spec.tls.enabled: true → the metrics endpoint is served over HTTPS, reusing the same certificate that SQL Server uses.
• If spec.tls.enabled: false → the metrics endpoint is served over plain HTTP.

You can override this independently with spec.monitoring.tls.enabled:

spec:
  tls:
    enabled: false          # database does not use TLS
  monitoring:
    enabled: true
    tls:
      enabled: true         # …but serve metrics over HTTPS anyway

Or the reverse — disable metrics TLS even when the database uses it:

spec:
  tls:
    enabled: true           # database uses TLS
  monitoring:
    enabled: true
    tls:
      enabled: false        # …but expose metrics over plain HTTP

When TLS is active on the metrics endpoint the operator:

1. Mounts the TLS Secret (<cr-name>-tls-certs, or spec.tls.certificateSecret if you supplied your own) into the sql_exporter container at /etc/mssql-tls.
2. Generates a web.yml Prometheus web configuration inside the monitoring ConfigMap that points sql_exporter at those certificate files.
3. Passes --web.config.file=/etc/sql_exporter/web.yml to the sql_exporter process.

No extra secrets or manual certificate management is required when using the operator-generated self-signed certificates.

Custom Monitoring Queries

You can define custom T-SQL queries that get exported as Prometheus metrics, similar to CloudNativePG's custom queries feature.

1. Create a ConfigMap with collector YAML

The ConfigMap must contain a sql_exporter collector definition:

apiVersion: v1
kind: ConfigMap
metadata:
  name: my-custom-queries
  namespace: default
data:
  custom-queries: |
    collector_name: custom_app
    metrics:
      - metric_name: mssql_app_orders_total
        type: counter
        help: "Total number of orders."
        values: [count]
        query: "SELECT COUNT(*) AS count FROM dbo.Orders"
      - metric_name: mssql_app_active_users
        type: gauge
        help: "Number of active users."
        key_labels: [region]
        values: [count]
        query: |
          SELECT region, COUNT(*) AS count
          FROM dbo.Users
          WHERE last_active > DATEADD(MINUTE, -5, GETDATE())
          GROUP BY region

2. Reference it in the CR

spec:
  monitoring:
    enabled: true
    customQueriesConfigMap:
      - name: my-custom-queries
        key: custom-queries

Custom metrics will be exposed alongside the defaults at the /metrics endpoint.

Prometheus Operator Integration

There are two ways to configure Prometheus Operator scraping for your MSSQL instances.

Option 1: Auto-created PodMonitor (enablePodMonitor)

Set enablePodMonitor: true in your CR and the operator will automatically create a PodMonitor resource scoped to that instance:

spec:
  monitoring:
    enabled: true
    enablePodMonitor: true

The operator creates a PodMonitor equivalent to:

apiVersion: monitoring.coreos.com/v1
kind: PodMonitor
metadata:
  name: my-mssql
spec:
  selector:
    matchLabels:
      app.kubernetes.io/name: solanica-mssql
      app.kubernetes.io/instance: my-mssql
  podMetricsEndpoints:
    - port: metrics
      path: /metrics
      interval: 30s

When TLS is enabled on the metrics endpoint (either inherited from spec.tls.enabled or set explicitly via spec.monitoring.tls.enabled: true), the operator automatically includes the TLS configuration in the generated PodMonitor:

  podMetricsEndpoints:
    - port: metrics
      path: /metrics
      interval: 30s
      scheme: https
      tlsConfig:
        ca:
          secret:
            name: my-mssql-tls-certs   # spec.tls.certificateSecret if you supplied your own
            key: ca.crt
        serverName: my-mssql.default.svc.cluster.local

The serverName matches the Subject Alternative Name embedded in the generated certificate, so Prometheus can verify the certificate without any extra configuration.

The PodMonitor is owned by the CR and will be automatically cleaned up on deletion.

Note: The Prometheus Operator CRDs must be installed in the cluster for PodMonitor auto-creation to work. If the CRDs are not found, the operator logs a warning and skips PodMonitor creation without failing.

Option 2: Manually managed PodMonitor (recommended)

For production environments or when you need more control — custom scrape intervals, TLS configuration, relabeling rules, specific namespace selectors, or a single PodMonitor covering multiple instances — it is recommended to manage the PodMonitor yourself. This decouples the scraping configuration from the CR lifecycle and allows fine-grained Prometheus Operator customization.

To use this approach, leave enablePodMonitor unset (or false) and apply a PodMonitor manually:

Plain HTTP (TLS disabled):

apiVersion: monitoring.coreos.com/v1
kind: PodMonitor
metadata:
  name: my-mssql
  namespace: default          # same namespace as the CR
  labels:
    release: prometheus       # label required by your Prometheus Operator installation
spec:
  selector:
    matchLabels:
      app.kubernetes.io/name: solanica-mssql
      app.kubernetes.io/instance: my-mssql   # matches your CR name
  podMetricsEndpoints:
    - port: metrics           # named port on the sql-exporter container
      path: /metrics
      interval: 30s

HTTPS (TLS enabled):

When spec.tls.enabled: true (or spec.monitoring.tls.enabled: true) you must add TLS configuration so Prometheus can verify the certificate. Reference the CA from the operator-managed TLS Secret:

apiVersion: monitoring.coreos.com/v1
kind: PodMonitor
metadata:
  name: my-mssql
  namespace: default
  labels:
    release: prometheus
spec:
  selector:
    matchLabels:
      app.kubernetes.io/name: solanica-mssql
      app.kubernetes.io/instance: my-mssql
  podMetricsEndpoints:
    - port: metrics
      path: /metrics
      interval: 30s
      scheme: https
      tlsConfig:
        ca:
          secret:
            name: my-mssql-tls-certs   # <cr-name>-tls-certs, or spec.tls.certificateSecret
            key: ca.crt
        # Must match the SAN in the certificate. The operator always includes
        # <cr-name>.<namespace>.svc.cluster.local as a SAN in generated certs.
        serverName: my-mssql.default.svc.cluster.local

Apply either manifest with:

kubectl apply -f podmonitor.yaml

Key fields to adjust for your environment:

Field	Description
metadata.namespace	Must match the namespace of the MSSQL CR
metadata.labels.release	Label required by your Prometheus Operator installation to discover PodMonitors (commonly release: prometheus)
spec.selector.matchLabels["app.kubernetes.io/instance"]	Must match the metadata.name of your CR
podMetricsEndpoints[].interval	Scrape interval; tune to your Prometheus global scrape interval
tlsConfig.ca.secret.name	<cr-name>-tls-certs when using operator-generated certs, or spec.tls.certificateSecret for custom certs
tlsConfig.serverName	<cr-name>.<namespace>.svc.cluster.local — matches the SAN in the operator-generated certificate

Generic Sidecars

In addition to the monitoring sidecar, you can inject arbitrary containers into MSSQL pods using spec.sidecars:

spec:
  sidecars:
    - name: log-forwarder
      image: fluent/fluent-bit:latest
      volumeMounts:
        - name: mssql-data
          mountPath: /var/opt/mssql
          readOnly: true

These containers are added as-is to the pod spec and are not managed by the operator.

Operator-Level Metrics

The operator itself exposes standard controller-runtime metrics (reconciliation counts, queue depth, work duration, etc.) on port 8443 via HTTPS. A ServiceMonitor is included in the deployment manifests under config/prometheus/.