In this blog, we will look at how we configure near real-time monitoring of SQL Server on Linux and containers with the Telegraf-InfluxDB and Grafana stack. This is built on similar lines to Azure SQLDB and Managed Instance solutions already published by my colleague Denzil Ribeiro. You can refer to the above blogs to know more about Telegraf, InfluxDB and Grafana.
A quick rundown of all the tasks we'll be carrying out to complete the setup:
For this demonstration, the host on which I deploy the containers is an Azure VM running Ubuntu 20.04. I'm collecting data from the four SQL Server instances listed below:
Let's start deploying containers:
docker network create --driver bridge influxdb-telegraf-net
#You can change the name of the network from “influxdb-telegraf-net” to whatever you want.
you can list the network using the commanddocker network ls
We will now create the SQL Server login that telegraf will use to connect to the target SQL Server instances. This login must be created on all target SQL Server instances that you intend to monitor. You can change the login name from telegraf to any other name of your choice, but the same also needs to be changed in the telegraf.conf file as well.
USE master;
CREATE LOGIN telegraf WITH PASSWORD = N'StrongPassword1!', CHECK_POLICY = ON;
GO
GRANT VIEW SERVER STATE TO telegraf;
GO
GRANT VIEW ANY DEFINITION TO telegraf;
GO
Run the following command to deploy the telegraf container
docker run -d --name=telegraf -v /home/amvin/monitor/sqltelegraf/telegraf.conf:/etc/telegraf/telegraf.conf --net=influxdb-telegraf-net telegraf
# where:/home/amvin/monitor/sqltelegraf/telegraf.conf is a telegraf configuration file placed on my host machine, please update the path as per your environment.
# please ensure that you change the IP addresses and port numbers to your target SQL Server instances in the telegraf.conf file that you create in your environment.
Note: You can download the sample telegraf.conf from here. Please remember to change the IP address to your target SQL Server instance IP addresses.
docker run --detach --net=influxdb-telegraf-net -v /home/amvin/monitor/data/influx:/var/lib/influxdb:rw --hostname influxdb --restart=always -p 8086:8086 --name influxdb influxdb:1.8
# where: /home/amvin/monitor/data/influx is a folder on the host that I am mounting inside the container, you can create this folder in any location.
# please ensure you set the right permissions so files can be written inside this folder by the container.
docker run --detach -p 3001:3000 --net=influxdb-telegraf-net --restart=always -v /home/amvin/monitor/data/grafana:/var/lib/grafana -e "GF_INSTALL_PLUGINS=grafana-piechart-panel,savantly-heatmap-panel" --name grafana grafana/grafana:8.1.1
# where: home/amvin/monitor/data/grafana is a folder on the host that I am mounting inside the container, you can create this folder in any location.
# please ensure you set the right permissions so files can be written inside this folder.
# grafana-azure-monitor-datasource is already included with grafana, so removing it from the list of plugins to install.
With the containers now deployed, use "docker ps -a" to list them, and you should see something like this:
Note: Please ensure that you open the ports on the host to which Grafana and InfluxDB containers are mapped to, in this case they are 3000 and 8086 respectively.
Let's now setup retention policy on InfluxDB to ensure that there is limited growth of the database. I am setting this for 30 days, you can configure it as per your requirement.
sudo docker exec -it influxdb bash
#then run beow commands inside the container
influx
create database telegraf;
use telegraf;
show retention policies;
create retention policy retain30days on telegraf duration 30d replication 1 default;
quit
We are now ready to create the dashboard, before that we need to setup Grafana and to do that follow the below steps:
You are ready and this is how the dashboard should look, feel free to modify the graphs as per your requirement.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.