Monitoring SQL Server for Linux on Kubernetes

Last Updated On December 07, 2017
You are here:
  • KB Home
  • Sonar
  • Monitoring SQL Server for Linux on Kubernetes

 

Goals

Monitor metrics from SQL Server for Linux on Kubernetes.

Background

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:

Benefits

  • 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.

Liabilities

  • 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.

Steps

Step 1: Clone Sonar chart repository

Shell

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

Shell

Step 2: Deploy SQLServer for Linux

The below command creates SQL server deployment:

Shell

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

Shell

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:

Sonar.config
As you can see, the service name for SQLServer is already set to mssql. To change default password, modify Sonar secrets file for Kubernetes:
Sonarc.dll.config
For monitoring SQL Server, Sonar chart uses just 3 queries. Below is an example:
Sonar.config
</connectionStrings>
Sonar schedule is configured to expose metrics to Prometheus is defined below:
Sonar.config
Sonar schedule can be configured with output element for sending same metrics to InfluxDb instead of Prometheus:
Sonar.config

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:
Shell
Shell
After deploying the above charts, Prometheus will be able to discover Sonar pod and start exporting metrics it collects from SQL Server.

Summary

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:

 

Tags: