Author: Ahmet Orhan

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

Post author By Ahmet Orhan
Post date 21.11.2023
No Comments on Part II — Monitoring MSSQL on Windows Server: A Guide to Maximizing Database Performance

MSSQL Monitoring

Continuing our guide, we will explore the process of setting up monitoring for Microsoft SQL Server (MSSQL) in your Windows Server environment. Monitoring your MSSQL instance is crucial for maintaining database performance, identifying bottlenecks, and ensuring optimal operations.

Here’s how to perform this comprehensive monitoring:

TL;DR

Enable SQL Server Agent: Activate the SQL Server Agent for interaction with external collectors such as Prometheus.
Install Prometheus Windows Exporter: Download and install the Prometheus Windows Exporter from GitHub.
Set Up Prometheus Scraper/Database: Install Prometheus on your monitoring server/computer and configure the scraper.
Access Prometheus Server and Add Targets: Access the Prometheus server through a browser and add a new target.
Query SQL Server Processes: Execute the necessary query to query SQL Server processes.
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.

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.

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.

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

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.

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

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.

If you have any questions or details you would like to add, feel free to write me.

Tags Grafana, Monitoring, mssql, Prometheus, sql, Windows Server, Wmi

Articles Monitoring Windows Server

Set Up Your Own Monitoring System with Windows Server!

Post author By Ahmet Orhan
Post date 21.11.2023
No Comments on 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.

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:

Download and extract Prometheus.

Create a directory, e.g., ‘C:\Monitoring\prometheus’, and extract the downloaded .zip file here.

Create a configuration file named ‘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

By default, Prometheus’ web interface will be available at http://localhost:9090.

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.

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

Tags Grafana, Monitoring, Prometheus, Windows Server, Wmi

Articles Windows

The Secret to Swiftly Managing Windows Packages: Discover, Keep Up-to-Date, and Update All Programs with the WinGet Command!

winget

Application management and update processes can become quite complex for users of the Windows operating system. But worry not, the solution is right here: Welcome to the magical world of Windows Package Manager (WinGet)! In this article, you will learn step by step how to use the WinGet command and discover how to streamline the application management process. You’ll find out how to accelerate the pace of managing your applications.

What is WinGet and How to Use It?

WinGet is a package manager designed specifically for the Windows operating system. Here are some fundamental commands and examples:

winget

1.Discovering Applications: You can search for any application using WinGet. For instance, quickly find Firefox:

winget search Firefox

2.Swift Installation: Installing applications swiftly is now incredibly easy! Let’s consider Microsoft Edge browser as an example:

winget install MicrosoftEdge

3.Clean Uninstall: Uninstalling unnecessary applications is a breeze. For example:

winget uninstall MicrosoftEdge

4.Updating All Applications: Ensuring your applications are running the latest versions is now achievable with just a single command:

winget upgrade -all

winget upgrade -all

By using the command above, just like in the Command Prompt, you can swiftly update all the programs on your Windows operating system.

The Next Generation of Application Management

WinGet elevates application management to a new level. Now you can control your operating system more effectively, instantly download the applications you desire, and keep them up-to-date. Thanks to this next-generation package manager, wasting time is a thing of the past! With WinGet, you can take your Windows application experience to the highest level.

For More Information

Windows Package Manager Documentation
You can view help information by using the command winget --help in the command prompt.

If you have any questions or details you would like to add, feel free to write me.

Tags App Management, Application Control, Tech Tips, Upgradeall, Winget

Articles Windows Server

Diskperf -Y and Diskperf -N Commands in Windows Server: Applications and Necessities

Post author By Ahmet Orhan
Post date 21.11.2023
No Comments on Diskperf -Y and Diskperf -N Commands in Windows Server: Applications and Necessities

disk performans

The Windows Server operating system provides various tools and commands for managing and monitoring servers’ administration and performance. Among these tools are the Diskperf -Y and Diskperf -N commands. In this article, we will explore the necessity and usage scenarios of the diskperf commands on Windows Server.

Diskperf -Y: Activation Command

The Diskperf -Y command is used to enable disk performance measurements. The applications and necessities of this command include:

1.Performance Monitoring and Analysis: Monitoring and analyzing server performance is crucial for maintaining efficient operations. The Diskperf -Y command activates performance measurements used to monitor disk usage levels, data transfer rates, and processing delays. System administrators can continually track server disk performance and promptly identify issues when needed.

2.Troubleshooting and Problem Resolution: Issues related to disks, such as performance degradation or excessive load, can adversely impact server efficiency. The Diskperf -Y command serves as an essential tool for detecting and swiftly resolving problems. System administrators can determine when disks are being excessively used or are insufficient in various situations.

Diskperf -N: Disable Command

The Diskperf -N command is used to disable disk performance measurements. The applications and necessities of this command include:

1.Data Privacy and Security: In certain situations, preventing the monitoring of disk performance data on servers becomes necessary. This is especially important when dealing with sensitive data or ensuring server security. The Diskperf -N command can be used to limit the monitoring of such sensitive data and safeguard data privacy.

2.Performance Optimization: The Diskperf -N command enables the temporary disabling of performance measurements based on measurement requirements. This can help optimize overall server performance by freeing up unnecessary system resources.

The Diskperf -Y and Diskperf -N commands provide the means to enable and disable disk performance measurements in the Windows Server operating system. Monitoring disk performance is vital for performance monitoring, troubleshooting, and ensuring data security on servers. When used correctly and purposefully, these commands empower system administrators and users with increased control over server management.

Enabling Diskperf -Y: How to Activate It

Disk performance measurements are crucial indicators for the health and efficiency of computer systems. Windows Server operating systems offer various tools and commands for managing and monitoring servers, and among these tools are the Diskperf -Y and Diskperf -N commands. In this article, we will explore the necessity and applications of the Diskperf -Y and Diskperf -N commands on Windows Server.

Diskperf -Y Command: Activation Process

Search for “Command Prompt” in the Start Menu: Navigate to the Start Menu and search for “Command Prompt.” Right-click on the “Command Prompt” application from the search results, and select “Run as administrator” to open it with administrative privileges.

Başlat Menüsü

2.Enter the Diskperf -Y Command: In the opened Command Prompt window, type the following command and press the Enter key:

diskperf -Y

Command Prompt

3.Check the Confirmation Message: After executing the command, the system should display a confirmation message such as “Disk performance counters were successfully enabled.” This indicates that disk performance measurements have been successfully activated.

By now, you have successfully used the diskperf -Y command to enable disk performance measurements. This will allow you to monitor performance metrics such as disk activity, data transfer speed, and disk usage.

disk performance

emember that performance measurements can be disabled using the “diskperf -N” command when necessary.

Monitoring disk performance using tools like Task Manager or Performance Monitoring is essential for optimizing the health and efficiency of servers or computers. Keeping track of disk activity and performance ensures a stable and responsive system.

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

Tags cmd, Command Line, Disk, Disk Performance, diskperf, Windows Server

Articles Windows Server

Creating and Managing an SSL Certificate on Windows Server

Post author By Ahmet Orhan
Post date 21.11.2023
No Comments on Creating and Managing an SSL Certificate on Windows Server

IIS (Internet Information Services)

Web security holds significant importance for the security of online activities nowadays. SSL (Secure Sockets Layer) certificates play a critical role in the security of websites and applications. This article outlines the steps to create and manage an SSL certificate on a Windows Server.

Step 1: Generating a .CSR File:

Open the IIS (Internet Information Services) management tool on your Windows Server.

Go to “Server Certificates.”

(Server Certificates)

Click on “Create Certificate Request.”

Create Certificate Request

Fill in the required details: domain name, organization name, country, etc.

domain name, organization name, country, etc.

Save the .CSR (Certificate Signing Request) file.

.CSR (Certificate Signing Request)

Step 2: Mail Confirmation and Obtaining .CRT File from SSL Provider:

Visit your chosen SSL provider and initiate the certificate purchase process.
Complete the necessary verification steps during the certificate purchase. Typically, you’ll need to verify ownership of the domain you own.
Once the verification is completed, your SSL provider will send you a .CRT (Certificate) file.

Step 3: Importing the .CRT File using MMC:

On the Windows Server, open the “Run” dialog and type “mmc,” then press Enter.

mmc

From the “File” menu, select “Add/Remove Snap-in.”

Add/Remove Snap-in.

Add the “Certificates” snap-in.

Certificates

Choose the “Computer account” option and select your local computer.

“Bilgisayar hesabı” (Computer Account) — Computer Account

Navigate to “Certificates” > “Personal” > “Certificates.”

Personal

Right-click on the imported .CRT file and choose “All Tasks” > “Import…,” then add the .CRT file.

Import…

Step 4: Converting .CRT File to .PFX Format:

In MMC, right-click on the certificate you imported the .CRT file to.

Choose “All Tasks” > “Export…”

Export

In the Certificate Export Wizard, select the .PFX format and proceed.

.PFX format

Set a password, save the .PFX file, and complete the process.

Set a password

Conclusion: This article has provided a detailed explanation of the steps to create and manage an SSL certificate on a Windows Server. Proper creation and management of SSL certificates are crucial for ensuring web security and safeguarding sensitive user data. Following the correct steps can enhance the security of websites and applications.

If you have any questions or details you would like to add, feel free to write me.

Tags csr, export, IIS, pfx, Windows Server