{"id":55438,"date":"2023-12-02T08:24:07","date_gmt":"2023-12-02T08:24:07","guid":{"rendered":"https:\/\/dbtut.com\/?p=55438"},"modified":"2023-12-02T08:26:43","modified_gmt":"2023-12-02T08:26:43","slug":"sql-server-monitoring-with-influxdb-and-grafana","status":"publish","type":"post","link":"https:\/\/dbtut.com\/index.php\/2023\/12\/02\/sql-server-monitoring-with-influxdb-and-grafana\/","title":{"rendered":"SQL Server Monitoring With Influxdb And Grafana"},"content":{"rendered":"
In our article today, we will be discussing how we can monitor SQL Server using open source systems (Influxdb and Grafana).<\/p>\n
First of all, let’s get to know our Open-Source products.<\/p>\n
InfluxDB<\/h3>\n
It is an open source database that is prepared with GO with its superior features such as real -time analytical studies, storage of application metrics and analysis of high performance\/queries.<\/p>\n
Grafana<\/h3>\n
Grafana is an open source, general-purpose dashboard and graphics program that works as a web application.<\/p>\n
I can say that it has become very popular lately because it shows you the data it receives from different data sources as a dashboard.<\/p>\n
Our needs;<\/p>\n
-One Linux server \n-SQL Server Server you will monitor<\/p>\n
INFluxdb and Grafana Installation to Linux server<\/h3>\n
Demo will be using ubuntu 16.04 TLs, I already have a Linux server on the Amazon AWS and we will be doing our installation operations on this server.<\/p>\n
Let’s start the installation.<\/p>\n
First of all, I run APT-GET Update on my Linux server and apply the current files to my server.<\/p>\nPicture-1<\/figcaption><\/figure>\n
I will be based on the official site of the product.<\/p>\n
I’m running the following installation commands.<\/p>\nPicture-2<\/figcaption><\/figure>\n
After defining the repository server required for Influxdb as in the picture above, I run the following installation commands.<\/p>\nPicture-3<\/figcaption><\/figure>\n
After completing the installation with apt-get install influxdb, I start influxdb. (sudo service influxdb start)<\/p>\nPicture-4<\/figcaption><\/figure>\n
We have completed the installation of Influxdb. I confirm the installation process with systemctl status influxdb.Service.<\/p>\nPicture-5<\/figcaption><\/figure>\n
As shown in the picture above, influxdb has successfully realized the installation.<\/p>\n
The way to the configuration file is \/etc\/influxdb\/influxdb.Conf.<\/p>\n
I will make some changes to this configuration file.<\/p>\n
I edit the configuration file using vi.<\/p>\nPicture-6<\/figcaption><\/figure>\n
I make the following changes in the configuration file.<\/p>\n[http]\nBind-Address = \u201c: 8088\u201d \nEnabled = True<\/p>\n
After performing the changes, I save and restart the Systemctl restart influxdb.service.<\/p>\nPicture-7<\/figcaption><\/figure>\n
We have made the necessary adjustments to access Influxdb, now I will create a database and user in Influxdb.<\/p>\n
To connect to Influxdb, I just need to write influx.<\/p>\nPicture-8<\/figcaption><\/figure>\n
As you can see in the picture, I have reached Influxdb and tells me that I can now use InfluxQL.<\/p>\n
With Create Database mshowto_influx, I create a database called mshowto_influx.<\/p>\nPicture-9<\/figcaption><\/figure>\n
With the following command, I create a user called “telegraf” and perform the necessary authorization.<\/p>\n
CREATE USER telegraf WITH PASSWORD \u2018superpa$$word\u2019\r\nGRANT ALL ON mshowto_influx TO telegraf<\/pre>\n
I performed the necessary operations on the Influxdb side.<\/p>\n
Now I’m going to the Grafana installation I need to visualize the existing data in InfluxDB.<\/p>\n
Grafana Installation<\/h3>\n
I get the official site for the installation of Grafana.<\/p>\n
After running the above codes, I verify the Grafana installation with the following command.<\/p>\n
Systemctl status Grafana<\/pre>\nPicture-11<\/figcaption><\/figure>\n
We have completed the Grafana installation and it informs us that a default user named admin has been created.<\/p>\n
Now all I have to do is finalize my installation process by allowing the required ports 3000 and 8086 on Amazon AWS for the influxdb and Grafana I installed on the server.<\/p>\nPicture-12<\/figcaption><\/figure>\n
When you encounter a screen like above, you can log in with the user name and admin password.<\/p>\n
After this stage, you will ask you to replace the admin user’s password.<\/p>\nPicture-13<\/figcaption><\/figure>\n
After setting a new strong password, you can continue by clicking save or continue without changing the password with skip.<\/p>\n
After accessing Grafana, you need to choose which data source you will use, so we click on add data source and select influxdb.<\/p>\nPicture-14<\/figcaption><\/figure>\n
As you can see in the image above, you can choose from many data sources other than InfluxDB.<\/p>\n
We choose InfluxDB and continue.<\/p>\n
After selecting InfluxDB, it will ask you to enter the necessary configuration settings for the InfluxDB you have installed.<\/p>\n
After the registration process, we successfully complete our data source addition process.<\/p>\nPicture-15<\/figcaption><\/figure>\n
After performing all these operations on our Linux server, we will make the necessary adjustments to monitor SQL Server with Telegraf.<\/p>\n
For Telegraf, you can use the telegraf.exe and telegraf.conf file that I will share in the attachment.<\/p>\n
Configuration of the “Telegraf.conf” file<\/h3>\n
You should open the telegraf.conf file I shared attached with a text editor and enter the SQL Server information you want to follow.<\/p>\n
However, for this process, you must first create a user named telegraf on SQL Server with the code block below and provide the necessary authorization.<\/p>\n
USE master;\r\nGO\r\nCREATE LOGIN [telegrafuser] WITH PASSWORD = N\u2019superpa$$word\u2019;\r\nGO\r\nGRANT VIEW SERVER STATE TO [telegrafuser];\r\nGO\r\nGRANT VIEW ANY DEFINITION TO [telegrafuser];\r\nGO<\/pre>\n
After defining the user, we need to enter the access information for InfluxDB in the OUTPUT PLUGINS section in the telegraf.conf file.<\/p>\nPicture-16<\/figcaption><\/figure>\n
After making the InfluxDB settings as shown in the example, you must enter your SQL Server access information that you want to monitor in the INPUT PLUGINS section.<\/p>\nPicture-17<\/figcaption><\/figure>\n
In the Servers section, you can enter the server information you want to access more than once.<\/p>\n
After entering this information, you need to run telegraf.exe as a Windows service with the configuration file you prepared.<\/p>\n
To do this, you can run Powershell or the command line as an administrator and use the code block below. (I assume that the file directory I shared in the attachment is under the Program Files folder.)<\/p>\nPicture-18<\/figcaption><\/figure>\n
As you can see above, our data collection process via telegraph has started to work successfully.<\/p>\n
Now all we have to do is configure the dashboard via Grafana.<\/p>\n
I usually use one of Grafana’s ready-made dashboards for this. (https:\/\/grafana.com\/dashboards\/409)<\/p>\n
To load the ready dashboard, I apply draft number 409 from the Import menu on Grafana.<\/p>\nPicture-19<\/figcaption><\/figure>\n
It asks me to choose a data source for the Dashboard.<\/p>\n
I select InfluxDB and import it.<\/p>\n
After the import process, I can monitor my SQL Server server with the screen as below.<\/p>\nPicture-20<\/figcaption><\/figure>\n
![\"\"](\"https:\/\/www.mshowto.org\/images\/articles\/2019\/04\/Resim1.png\")
![\"\"](\"https:\/\/www.mshowto.org\/images\/articles\/2019\/04\/Resim2.png\")
![\"\"](\"https:\/\/www.mshowto.org\/images\/articles\/2019\/04\/Resim3.png\")
![\"\"](\"https:\/\/www.mshowto.org\/images\/articles\/2019\/04\/Resim4.png\")
![\"\"](\"https:\/\/www.mshowto.org\/images\/articles\/2019\/04\/Resim5.png\")
![\"\"](\"https:\/\/www.mshowto.org\/images\/articles\/2019\/04\/Resim6.png\")
![\"\"](\"https:\/\/www.mshowto.org\/images\/articles\/2019\/04\/Resim7.png\")
![\"\"](\"https:\/\/www.mshowto.org\/images\/articles\/2019\/04\/Resim8.png\")
![\"\"](\"https:\/\/www.mshowto.org\/images\/articles\/2019\/04\/Resim9.png\")
![\"\"](\"https:\/\/www.mshowto.org\/images\/articles\/2019\/04\/Resim10.png\")
![\"\"](\"https:\/\/www.mshowto.org\/images\/articles\/2019\/04\/Resim11.png\")
![\"\"](\"https:\/\/www.mshowto.org\/images\/articles\/2019\/04\/Resim12.png\")
![\"\"](\"https:\/\/www.mshowto.org\/images\/articles\/2019\/04\/Resim13.png\")
![\"\"](\"https:\/\/www.mshowto.org\/images\/articles\/2019\/04\/Resim14.png\")
![\"\"](\"https:\/\/www.mshowto.org\/images\/articles\/2019\/04\/Resim15.png\")
![\"\"](\"https:\/\/www.mshowto.org\/images\/articles\/2019\/04\/Resim16.png\")
![\"\"](\"https:\/\/www.mshowto.org\/images\/articles\/2019\/04\/Resim17.png\")
![\"\"](\"https:\/\/www.mshowto.org\/images\/articles\/2019\/04\/Resim18.png\")
![\"\"](\"https:\/\/www.mshowto.org\/images\/articles\/2019\/04\/Resim19.png\")
![\"\"](\"https:\/\/www.mshowto.org\/images\/articles\/2019\/04\/Resim20.png\")