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