Tag: Wmi

Categories
Articles Monitoring Windows Server

Part II — Monitoring MSSQL on Windows Server: A Guide to Maximizing Database Performance

MSSQL Monitoring
MSSQL Monitoring

Here’s how to perform this comprehensive monitoring:

TL;DR

  1. Enable SQL Server Agent: Activate the SQL Server Agent for interaction with external collectors such as Prometheus.
  2. Install Prometheus Windows Exporter: Download and install the Prometheus Windows Exporter from GitHub.
  3. Set Up Prometheus Scraper/Database: Install Prometheus on your monitoring server/computer and configure the scraper.
  4. Access Prometheus Server and Add Targets: Access the Prometheus server through a browser and add a new target.
  5. Query SQL Server Processes: Execute the necessary query to query SQL Server processes.
  6. Install Grafana and Connect to Prometheus: Install Grafana, connect it to the Prometheus server, and visualize the data.

In our previous article, you can find information on how to perform monitoring on a Windows server.

  1. Enable SQL Server Agent in SSMS:
  • To allow external collectors (e.g., Prometheus) to query the SQL Server engine, enable the SQL Server Agent.

  • SQL Server Agent
  • Change the startup type of the SQL Server Agent service to “Automatic” so that it starts automatically upon system startup.
Services / Automatic
Services / Automatic
2.Install Prometheus Windows Exporter:
  • Download  the Prometheus Windows Exporter from GitHub.
  • Use the installer found in the “Assets” section to install the exporter.
  • You can use the default Windows node exporter provided by Prometheus, which includes basic SQL Server metrics. No additional exporter installation is needed.
  • Install the exporter using the MSI installer.
  • After installation, the binary will be located in theC:\Program Files\windows_exporterfolder.
  • If a process/task named “windows_exporter” is running, terminate it, as we will need to manually start the exporter with some additional flags.
  • Manually run the exporter with the following command to fetch SQL Server statistics:
C:\Program Files\windows_exporter\windows_exporter.exe --collectors. Enabled="cpu,cs,logical_disk,net,os,service,system,textfile,mssql"
  • Access http://localhost:9182/metrics in your browser to view available metrics.
  • To make the changes persistent, add the following settings to the registry:
"C:\Program Files\windows_exporter\windows_exporter.exe" --collectors.enabled="cpu,cs,logical_disk,net,os,service,system,textfile,mssql" --log.format logger:eventlog?name=windows_exporter

Search for “regedit” in the Start menu.

regedit

Open regedit.

HKEY_LOCAL_MACHONE/SYSTEM/Current ControlSet/Services/windows_exporter

in the opened window and make the registry changes we provided above to the ‘ImagePath’.

windows_exporter
windows_exporter

3-  Install Prometheus and Configure the Scraper:

  • Download the latest version of Prometheus from prometheus.io. ,
  • Extract the downloaded .tgz file to an appropriate folder.
  • Run the binary file named prometheus.exelocated in the extracted folder.
  • There will be a file named prometheus.yamlin the directory where Prometheus is installed.
  • Open this file with a text editor. In thestatic_configssection, add the IP address of the Windows server you want to add as a target.

Example:

 - job_name: "MSSQL_SERVER"

    # metrics_path defaults to '/metrics'
    # scheme defaults to 'http'.

    static_configs:
      - targets: ["MSSQLSERVERIP:9182"]
  • Replace “MSSQLSERVERIP” with the actual IP address of your Windows server.
  • Save and close the file.

4.Access the Prometheus Server and Add Targets:

  • Open a browser and go to localhost:9090
  • In the Status -> Targets tab, make sure the target at MSSQLSERVERIP:9182/metrics is UP.
Prometheus — UP
Prometheus — UP
5. Query SQL Server Processes Through Prometheus:
  • Tarayıcınızı açın ve Prometheus sunucusuna gidin. Varsayılan olarak localhost:9090 .Open your browser and go to the Prometheus server. By default, it’s localhost:9090
  • Click on the “Console” tab in the upper left corner. This is where you can run PromQL queries.
  • Query SQL Server processes by entering the following query:
rate(windows_mssql_sqlstats_batch_requests[30s]) * 60
sql sorgu
sql sorgu

6.Install Grafana and Connect to Prometheus:

  • Install Grafana and access the interface at localhost:3000.
  • Use the “Import” option to add the MSSQL Windows Exporter Dashboard theme. Enter ‘15024’ as the ID in the “Import via grafana.com” section and proceed with “Load.”
  • In the “Data Sources” section, add the “Prometheus” data source and “Import” it.
  • Now, you can visually examine SQL Server statistics in the added dashboard.
MSSQL Monitoring
MSSQL Monitoring
If you have any questions or details you would like to add, feel free to write me.
Categories
Articles Monitoring Windows Server

Set Up Your Own Monitoring System with Windows Server!

Let’s start by outlining the general steps for setting up a monitoring system using Prometheus, Grafana, and WMI Exporter on Windows Server. Keep in mind that technologies and versions may change over time, so it’s important to refer to the most up-to-date documentation and resources.

  1. Define Requirements:
  • Determine which version of Windows Server you will use.
  • Download the latest versions of Prometheus and Grafana.
  • Download the WMI Exporter.

2.Prometheus Installation:

Prometheus
Prometheus
  • Create a directory, e.g., ‘C:\Monitoring\prometheus’, and extract the downloaded .zip file here.
Dizin Belirle
Directory
  • Create a configuration file named ‘prometheus.yml’.
prometheus.yml
prometheus.yml
  • In ‘prometheus.yml’, define your targets (your Windows Servers) and the metrics you want to monitor. Here, we’re defining ‘localhost:9182’ to collect metrics from our existing server. We’ll install the WMI Exporter in later steps.
  • Example ‘prometheus.yml’ configuration:
# Global config
global:
scrape_interval: 15s
evaluation_interval: 15s

scrape_configs:
- job_name: "prometheus"
static_configs:
- targets: ["localhost:9090"]

- job_name: "WMI_Exporter"
static_configs:
- targets: ["localhost:9182"]
  • Start Prometheus by running the following command in the relevant directory:
prometheus.exe --config.file=prometheus.yml
prometheus başlatma işlemi
prometheus initialization process
Prometheus’un web arayüzü (localhost:9090)
Prometheus’ web interface (localhost:9090)
  • You can access the Prometheus interface by opening this address in a web browser.
Target
Target

3.WMI Exporter Installation:

  • Run the ‘wmi_exporter.exe’ file. (You might encounter an unknown publisher warning; proceed by selecting “Run” anyway.)
wmi_exporter.exe
wmi_exporter.exe
  • To verify that ‘wmi_exporter.exe’ is running, open your browser and navigate to: http://localhost:9182.
localhost:9182
localhost:9182
  • Configure the WMI Exporter. This configuration determines which WMI queries to run and which metrics to collect.

4. Grafana Installation:

Grafana
Grafana

Run the downloaded ‘.exe’ file and follow the installation steps:

Grafana Setup
Grafana Setup

Proceed through the setup by clicking ‘Next’.

Accept the license agreement and continue by clicking ‘Next’.

Complete the installation by clicking ‘Install’.

Finish the installation by clicking ‘Finish’.

  • Access the Grafana interface using a web browser at ‘localhost:3000’.
grafana interface
  • During the initial login, use the default credentials: Username: ‘admin’, Password: (set a password of your choice).
    change default password

    Log in to Grafana, and add a data source. Prometheus will be the database source to transfer data to Grafana.

grafana web interface
  • Log in to Grafana, and add a data source. Prometheus will be the database source to transfer data to Grafana.
  • Log in to Grafana, and add a data source. Prometheus will be the database source to transfer data to Grafana.

  • ‘Save & test’ diyerek ekleme işlemini bitiriyoruz.

To summarize the steps:

  • We completed the setup and configuration of Prometheus to create a database to store metrics received from the WMI Exporter.
  • We installed the WMI Exporter, enabling us to collect metrics from our Windows server.
  • Grafana installation was completed to visualize the metrics obtained from Prometheus.

For visualization, proceed to the Grafana website.

    • Select a suitable theme, navigate to ‘Dashboards’, click ‘New/Import’, and import the desired dashboard using its ID.
Import
Import
  • On the screen that comes up, we enter the theme ID in the ‘Import via grafana.com’ section and select ‘Load’.
Tema Yükleme İşlemi
  • The database ‘Prometheus’ is selected and the ‘import’ option is selected.
Tema Yükleme İşlemi- Veri tabanı seçimi
Theme Installation Process — Database selection
  • As a result, we have completed the monitoring process;

Congratulations on following through these steps patiently. Thank you!

Herhangi bir sorunuz veya eklemek istediğiniz detaylar varsa bana yazmaktan çekinmeyin.