.::: Check Progres Status Backup & Restore Progress in % Percent MSSQL SQL Server :::.

A. Backup

1. Query 1

SELECT @@servername as ServerName, command, percent_complete,total_elapsed_time, estimated_completion_time, start_time
  FROM sys.dm_exec_requests
  WHERE command IN ('RESTORE DATABASE','BACKUP DATABASE')

.::: How to enable CDC (Change Data Capture) in MariaDB, MySQL, MarinaDB in Table :::.

Enable Binary Logging

1. check table for sample

SELECT *,@@hostname,@@version FROM teguhth.pembelian
 

2. Create table for cdc

# create table

CREATE TABLE audit_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    table_name VARCHAR(100),
    action_type ENUM('INSERT', 'UPDATE', 'DELETE'),
    old_data JSON,
    new_data JSON,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

SELECT *,@@hostname,@@version FROM audit_table;

.::: Script check cpu usage & memory usage & Total Memory Server SQL Server include using sqlcmd to monitoring NMS PRTG:::.

 

correlation with https://teguhth.blogspot.com/2025/02/add-sensor-microsoft-sql-v2-sensor.html

 

1. create store procedure cpu monitoring sql server for monitoring

CREATE PROCEDURE sp_total_usage_cpu_SQLProcessUtilization

AS
BEGIN
DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks)FROM sys.dm_os_sys_info);

SELECT TOP(1)
               100 - SystemIdle - SQLProcessUtilization + SQLProcessUtilization AS [Total Usage CPU SQL Utilization]
  

.::: Script to check cpu & memory usage service in linux include custome service, mariadb, mysql, mongodb :::.

 
1. check custome cpu usage and memory usage

[root@teguhth special]# cat custom_service.sh

#!/bin/bash

echo "run custom_service.sh <name_service>"
PIDX=${1}
# Mendapatkan PID dari proses mariadbd
MARIADB_PID=$(pgrep $PIDX)

# Mendapatkan informasi tentang proses mariadbd dari top
TOP_INFO=$(top -n 1 -b -p "$MARIADB_PID" | tail -n +8 | head -n 1)
Mem_Total_mb=$(free -m | awk 'NR==2{printf "%.f", $2 }')
Mem_Total_gb=$(free -m | awk 'NR==2{printf "%.2f", $2/1024 }')

# Mengekstrak nilai CPU dan penggunaan memori dari output top
CPU_USAGE=$(echo "$TOP_INFO" | awk '{print $9}')
MEMORY_USAGE=$(echo "$TOP_INFO" | awk '{print $10}')
MEMORY_B=$(echo "$TOP_INFO" | awk '{print $6 }')

echo "CPU Usage(%) $PIDX   : ${CPU_USAGE}%"
echo "Memory Usage(%) $PIDX: ${MEMORY_USAGE}%"
echo "Memory Usage $PIDX   : ${MEMORY_B}"
echo "Memory Total Server   : $Mem_Total_mb MB or $Mem_Total_gb GB"

[root@teguhth special]#

.::: Study Kasus Praktis belajar Query create & insert table/Collection MongoDB (Data Warehouse) dengan Primary Key :::.

1. correlation with other post

http://teguhth.blogspot.com/2020/12/study-kasus-praktis-belajar-query.html

2. create collection with primarykey

use teguhth
show dbs

show collections
db.createCollection("barang")
db.createCollection("suplier")
db.createCollection("customer")
db.createCollection("pasok")
db.createCollection("pembelian")
show collections
 

.::: Create User MongoDB role admin and readonly and authenticationRestrictions :::.

1. create user admin

use admin
db.createUser({ user: "admin",pwd: "admin",
roles: [{ role: "readAnyDatabase", db: "admin" },{ role: "readWriteAnyDatabase", db: "admin" }]});
show users;

.::: Create a dblink to a remote server MariaDB MySQL to PostgreSQL EDB and using metode SRCDEF :::.

1. install ODBC postgresql * configure

http://teguhth.blogspot.com/2024/01/install-postgresql-odbc-driver-on-linux.html

A. using dblink

1. create table dblink example table barang
CREATE TABLE table_edb_barang(
KODE_BARANG char(6) not null ,
NAMA_BARANG varchar(25),
SATUAN_BARANG varchar(20),
STOK_BARANG decimal(4),
primary key (KODE_BARANG)) ENGINE = CONNECT TABLE_TYPE=ODBC TABNAME='barang' CONNECTION='DSN=edbdb';

.::: Access MariaDB from PostgreSQL using dblink /FOREIGN DATA WRAPPER mysql_fdw :::.

1. install mysql_fdw
wget https://download.postgresql.org/pub/repos/yum/15/redhat/rhel-7.9-x86_64/mysql_fdw_15-2.8.0-2.rhel7.x86_64.rpm
yum install mysql_fdw_15-2.8.0-2.rhel7.x86_64.rpm -y

2. create extension;

teguhth=# \c edbmaria;
You are now connected to database "edbmaria" as user "postgres".
edbmaria=#

edbmaria=# CREATE EXTENSION mysql_fdw;
CREATE EXTENSION
edbmaria=#
SELECT * FROM pg_extension;
 

.::: Enable pgaudit & pgauditlogtofile in PostgreSQL EDB ::.

 

1. Download pgaudit & pgauditlogtofile
https://download.postgresql.org/pub/repos/yum/16/redhat/rhel-8.2-x86_64/
yum install -y https://rpmfind.net/linux/centos-stream/9-stream/AppStream/x86_64/os/Packages/pgaudit-16.0-1.module_el9+807+b1de07f3.x86_64.rpm
yum install -y https://download.postgresql.org/pub/repos/yum/16/redhat/rhel-8.2-x86_64/pgauditlogtofile_16-1.5.12-1PGDG.rhel8.x86_64.rpm

2. check rpm & extension

[root@teguhth ~]# rpm -qa | grep -i postgres
postgresql16-server-16.0-1PGDG.rhel8.x86_64
postgresql16-contrib-16.0-1PGDG.rhel8.x86_64
postgresql16-16.0-1PGDG.rhel8.x86_64
postgresql16-libs-16.0-1PGDG.rhel8.x86_64
[root@teguhth ~]#

.::: How to Use backup & Restore Database MySQL MariaDB using MariaDB Backup :::.

 
A. Backup with mariabackup
1. install mariabackup

yum install -y MariaDB-backup -y

2. check data before testing

select * from teguhth.barang;
select * from teguhth.pembelian;
 

.::: Create dblink using dblink_connect & dblink in PostgreSQL EDB :::.

1. check before setting
select pg_read_file('/etc/hostname') as hostname, version();

psql -h 10.10.10.8 -U admin -p 5432 -d teguhth -c "select pg_read_file('/etc/hostname') as hostname, version();";
psql -h 10.10.10.71 -U admin -p 5444 -d teguhth -c "select pg_read_file('/etc/hostname') as hostname, version();";
 

2. create create new database dblink_edb <optional only case>
teguhth=# create database dblink_edb;
CREATE DATABASE
teguhth=#

.::: Create External Table PostgreSQL, EDB using Foreign Data Wrapper (postgres_fdw) like DBLink :::.

1. create new externaldb for sample
create database externaldb;
\c externaldb;

teguhth=# create database externaldb;
CREATE DATABASE
teguhth=# \c externaldb;
You are now connected to database "externaldb" as user "postgres".
externaldb=#

.::: Script Backup Full All Database in PostgreSQL EDB :::.

corelation with http://teguhth.blogspot.com/2023/08/backup-and-restore-postgresql-edb.html

 

 1. check data from table
select * from teguhth.public.pembelian;
select * from edb.public.barang;
 

2. run backup
su - postgres
sh backup_edb_full_script.sh