Monitoring SQL Server: setting up ELK+G

In 9 cases out of 10 our clients have some sort of database that they want to interface with. And 8 out of those 9 cases the database is going to be SQL Server. Yes, this is us being biased, but you know what?

It does not matter

The important bit is – out clients like to know how the database is doing. Some are happy to pay for commercial APMs, others either have very specific needs or love the challenge to DIY.

We are here to help

One way to get better picture of what’s happening with the DB would be to keep grabbing vitals over time and plotting them on a graph of some sort. Grafana is a fantastic way to achieve that. It supports a whole bunch of backends (including SQL server) and allows insane amount of customisations.

Diversify

It is possible to store SQL telemetry in another SQL database on the same server (you could even set up SQL Agent jobs to do the polling – all nicely packaged). We however thought it might be a good idea to not store all data on the same machine. We’d like to not overstrain the main database in time of pinch and completely decouple analytics from critical business processes.

ELK G stack

One of many ways to approach this is to introduce a (somewhat) free and open source ElasticSearch into the mix. And mightly Logstash for data ingestion. This is where we’d normally go on to Kibana for dashboards and nice UI (and we did end up running it), but the main focus of this exercise will still fall onto Grafana.

Setting it up

Theres no point repeating official documentation for respective products, let’s instead write up a docker-compose file:

version: '3'
services:
    elasticsearch:
        image: docker.elastic.co/elasticsearch/elasticsearch:7.6.1
        environment:
            - node.name=elastic01
            - discovery.type=single-node  
            - bootstrap.memory_lock=true
            - "ES_JAVA_OPTS=-Xms512m -Xmx512m"        
        volumes:
            - ./elastic:/usr/share/elasticsearch/data
    logstash:
        image: docker.elastic.co/logstash/logstash:7.6.1
        volumes: 
            - ./logstash-pipeline:/usr/share/logstash/pipeline/
            - ./logstash-config/usr/share/logstash/config/
        depends_on:
          - elasticsearch
    kibana:
        image: docker.elastic.co/kibana/kibana:7.6.1
        environment:
          - ELASTICSEARCH_HOSTS=http://elasticsearch:9200
        ports:
          - 5601:5601
        depends_on:
          - elasticsearch
    grafana:
        image: grafana/grafana
        ports:
          - 3000:3000
        depends_on:
          - elasticsearch

All that’s left to do is docker-compose up -d and run. Stay tuned for next posts in the series.