Monitoring SQL Server for Linux on Kubernetes

You are here:



Monitor metrics from SQL Server for Linux on Kubernetes.


Deploying SQL Server for Linux to Kubernetes cluster requires to define approach for monitoring. While there are several choices available, Sonar is only monitoring agent that utilize .NET Core framework to accomplish this task. More information about scenarios supported by Sonar is available in our knowledge base. Helm charts are published in our repository on GitHub.

Design Considerations

The following benefits and liabilities should be considered for using Sonar to monitor SQL Server for Linux on Kubernetes:


  • Configurable queries for getting IO, Waits, etc.
  • Easy to configure polling any metric or data without coding.
  • Supports Prometheus and InfluxDb at the same time.
  • Database credentials are stored as Kubernetes secret.
  • Low latency within pod network ( sample queries take 1-3ms).
  • Ability to measure query cost using Sonar metrics.


  • Ensure that performance cost of exciting queries periodically is low.
  • Ensure that database credentials are stored as Kubernetes secret when modifying Sonar chart.
  • Sonar supports only UDP protocol for sending output to InfluxDb.
  • Deploying Grafana is required for data visualization.
  • Alerts can be created using your target monitoring system: Prometheus AlertManager, InfluxData Kapacitor or Grafana alerts.


Step 1

Clone Sonar chart repository:

git clone

If you are using minikube, please make sure you have enough RAM required by SQL Server:

minikube start –v=10 –vm-driver=xhyve –memory=4096 –cpus=2

Step 2: Deploy SQLServer for Linux The below command creates SQL server deployment:

kubectl run mssql –-image=microsoft/mssql-server-linux –-port=1433 –-env ACCEPT_EULA=Y –-env SA_PASSWORD=Pass@word1

At this point, SQL Server pod should be running on Kubernetes. Next expose Sql Server port:

kubectl expose deployment mssql --type=LoadBalancer

Step 3: Configure Sonar

This step is optional if you choose to update SQL server user name and password. To accomplish this, edit review connection string in Sonar.config in charts/stable/sonar folder of repository you cloned:

      <connectionstrings> <add name=”sqldb” providerName=”mssql” connectionString=”Server=mssql,1433;Database=master; User Id=sa; Password={$mssql_password};Connection Timeout=1;”></add></connectionstrings>

As you can see, the service name for SQLServer is already set to mssql. To change default password, modify Sonar secrets file for Kubernetes, named Sonarc.dll.config:

<connectionstrings><add key=”mssql_password” value=”<your password>”/></add></connectionstrings>

For monitoring SQL Server, Sonar chart uses just 3 queries. Below is an example from Sonar.config:

<add name=”sql_server_io_history” type=”sql”
              DB_NAME(fs.database_id) AS [Database Name],
              CAST(io_stall_read_ms/(1.0 + num_of_reads) AS float) AS [avg_read_stall_ms],
              CAST(io_stall_write_ms/(1.0+num_of_writes) AS float) AS [avg_write_stall_ms],
              io_stall_read_ms + io_stall_write_ms AS [io_stalls],
              num_of_reads + num_of_writes AS [total_io],
              CAST((io_stall_read_ms + io_stall_write_ms)/(1.0 + num_of_reads + num_of_writes) AS float) AS [avg_io_stall_ms]
              FROM sys.dm_io_virtual_file_stats(null,null) AS fs
              INNER JOIN sys.master_files AS mf
              ON fs.database_id = mf.database_id
              AND fs.[file_id] = mf.[file_id]
              ORDER BY avg_io_stall_ms DESC;”>
                 <add name ="Database" value="Database Name"/>

Sonar schedule is configured to expose metrics to Prometheus is defined below in Sonar.config

    <add name=”sql06″ query=”sql_server_io_history” input=”sqldb” intervalSeconds=”10″ />

Sonar schedule can be configured with output element for sending same metrics to InfluxDb instead of Prometheus:

    <add name=”sql06″ query=”sql_server_io_history” input=”sqldb” output=”influxdb” intervalSeconds=”10″>

Step 4: Deploy Sonar

This step assumes that you will be monitoring data by deploying Prometheus. The below steps use global repository chart for deploying Prometheus and local Sonar chart from local repository cloned earlier:

$ helm install –-name prometheus stable/prometheus

$ helm install –-name sonarc ./stable/sonar-ds

After deploying one the above charts, Prometheus will be able to discover Sonar pod and start exporting metrics it collects from SQL Server.


The end result of the above steps will create Sonar sidecar with Prometheus labeling:

As you can see, Sonar service endpoint was discovered as metrics service endpoint and metrics from SQL Server are being imported. After deploying Grafana to k8s, you can quickly create dashboard for collected metrics:  

Last Updated On September 18, 2018