.::: Check Date when Microsoft SQL Server (MSSQL), MariaDB MySQL & PostgreSQL Installed & Upgraded :::.

1. MSSQL

 
SELECT @@servername as ServerName,SERVERPROPERTY('productversion') as ProductVersion
      ,SERVERPROPERTY ('productlevel') as ProductLevel
      ,SERVERPROPERTY ('edition') as Edition
      ,SERVERPROPERTY ('MachineName') as MachineName
      ,SERVERPROPERTY ('LicenseType') as LicenseType
      ,SERVERPROPERTY ('NumLicenses') as NumLicenses
      ,create_date as 'SQLInstallDate'
      ,(select modify_date from sys.server_principals where name like '%##MS_SQLAuthenticatorCertificate##%') as SQLUpgradeDate
FROM sys.server_principals
WHERE name='NT AUTHORITY\SYSTEM';
 

2. MariaDB MySQL

.::: Backup All User, Specify User , Permission, Roles, Server Roles in SQL Server :::.

 
Untuk melakukan backup user dan privilege di SQL Server, Anda bisa menggunakan perintah Transact-SQL (T-SQL) untuk mengekstrak informasi tentang pengguna (user) dan hak akses (privilege). Berikut langkah-langkah yang dapat dilakukan:

1. Backup User Login dan Mapping ke Database

Gunakan skrip di bawah ini untuk mencadangkan login pengguna dan mappingnya ke database.
Output dari skrip ini akan memberikan Anda perintah untuk membuat ulang login yang sudah ada. Perhatikan bahwa password tidak dapat diekstrak, jadi Anda perlu memasukkannya secara manual saat melakukan restore.

-- Backup logins
SELECT @@servername as ServerName,'CREATE LOGIN [' + l.name + '] '
    + CASE
        WHEN l.type_desc = 'SQL_LOGIN' THEN 'WITH PASSWORD = ''<password>'', '
        WHEN l.type_desc = 'WINDOWS_LOGIN' THEN 'FROM WINDOWS, '
        ELSE ''
      END
    + ' DEFAULT_DATABASE=[' + l.default_database_name + ']'
    + CASE
        WHEN l.is_disabled = 1 THEN ', DISABLE;'
        ELSE ';'
      END AS CreateLoginScript
FROM sys.server_principals l
WHERE l.type IN ('S', 'U');  -- SQL Logins (S) and Windows Logins (U)
 

.::: Enable collect MariaDB User login Connect & Disconnect include prviledge using MariaDB Audit :::.

1. enable MariaDB Audit

http://teguhth.blogspot.com/2022/08/enable-server-audit-in-mariadb-mysql.html

2. convert to table

http://teguhth.blogspot.com/2022/09/load-log-file-insert-into-table-in.html

3. check filter tbl_mariadb_audit to filter connect & disconnect

MariaDB [teguhthtools]> select * from tbl_mariadb_audit where type ='connect' or type ='disconnect';
+-------------------+---------+--------+-----------+------+---------------+------------+------+---------+--------+
| date              | host    | userdb | client    | id   | connection_id | type       | db   | sqltext | status |
+-------------------+---------+--------+-----------+------+---------------+------------+------+---------+--------+
| 20240822 07:52:09 | teguhth | admin  | teguhth   |    4 |             0 | CONNECT    |      |         | 0      |
| 20240822 07:52:32 | teguhth | admin  | teguhth   |    4 |             0 | DISCONNECT |      |         | 0      |
+-------------------+---------+--------+-----------+------+---------------+------------+------+---------+--------+
3 rows in set (0.001 sec)

MariaDB [teguhthtools]>

.::: Script Broadcast Query in EDB / PostgreSQL to Multple Database in Linux :::.

1. create database sample
create database db_klt_teguh;
create database db_klt_teguh_dmart;
create database db_klt_teguh_archive;

create database db_bgr_tri;
create database db_bgr_tri_dmart;
create database db_bgr_tri_archive;

create database db_lpg_raju;
create database db_lpg_raju_dmart;
create database db_lpg_raju_archive;

.::: Script Broadcast Query in MariaDB MySQL to Multple Database in Linux :::.

1. create database sample
create database db_klt_teguh;
create database db_klt_teguh_dmart;
create database db_klt_teguh_archive;

create database db_bgr_tri;
create database db_bgr_tri_dmart;
create database db_bgr_tri_archive;

create database db_lpg_raju;
create database db_lpg_raju_dmart;
create database db_lpg_raju_archive;

.::: How to enable the MySQL/MariaDB general query logs :::.

1. enable general log temporary

SET GLOBAL general_log_file='/var/lib/mysql/general.log';
SET GLOBAL log_output = 'FILE,TABLE';
SET GLOBAL general_log = 'ON';
SET GLOBAL general_log = 1;
 

.::: Create User Access MariaDB MySQL using PAM example OS Linux Login :::.

 
1. install pam plugin

INSTALL PLUGIN pam SONAME 'auth_pam';

MariaDB [(none)]> INSTALL PLUGIN pam SONAME 'auth_pam';
ERROR 1968 (HY000): Plugin 'pam' already installed
MariaDB [(none)]>

2. check plugin pam

show plugins
SELECT plugin_name,plugin_version,plugin_status,plugin_type,plugin_library,plugin_author FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%pam%';

.::: Script Backup Full + Differential + Transaction Log in MariaDB MySQL & Scheduler just like SQL Server for SingleDB & MultipleDB include Restore in One File Script :::.

 
A. SingleDB

1. run script one time to full backup

sh /data/script/scriptbackup/backup_full_log_single_db.sh
 

2. run script two time to log backup

sh /data/script/scriptbackup/backup_full_log_single_db.sh

.::: How to configure Transparent Data Encryption (TDE), Monitoring & Remove TDE in SQL Server :::.

A. Create TDE

1. Create Master Key

USE Master;
GO
CREATE MASTER KEY ENCRYPTION
BY PASSWORD='TDEpassword!';
GO
 
 
2. Create Certificate protected by master key
CREATE CERTIFICATE TDE_Cert
WITH
SUBJECT='Database_Encryption';
GO

.::: Additional Environment PostgreSQL EDB example pg_ctl: command not found :::.

 1. Run pg_ctl

pg_ctl
[root@teguhth ~]# su - postgres
Last login: Sun May 12 08:18:13 WIB 2024 on pts/1
-bash-4.2$ pg_ctl -D /var/lib/pgsql/15/data status
-bash: pg_ctl: command not found
-bash-4.2$
-bash-4.2$ pg_ctl -D /var/lib/pgsql/15/data start
-bash: pg_ctl: command not found
-bash-4.2$
-bash-4.2$ pg_ctl -D /var/lib/pgsql/15/data stop
-bash: pg_ctl: command not found
-bash-4.2$
-bash-4.2$

.::: Enable userstat USER_STATISTICS, CLIENT_STATISTICS, INDEX_STATISTICS, and the TABLE_STATISTICS MariaDB MySQL :::.

A. Enable Statistic Plugin

1. Check configuration statistic
From query
show variables like '%userstat%';

From mariadb configuration
# cat /etc/my.cnf.d/server.cnf | grep userstat

 

2. Enable userstat configuration From Query ( for temporary configuration)

SET GLOBAL userstat=1;
show variables like 'userstat';

.::: Backup & Restore Database MongoDB Using Mongodump & mongorestore :::.

Step 1: Create Direct Backups Using Mongodump

mongodump
 
[root@teguhth ~]# mongodump
2024-04-15T14:59:40.783+0700    writing admin.system.users to dump/admin/system.users.bson
2024-04-15T14:59:40.789+0700    done dumping admin.system.users (3 documents)
2024-04-15T14:59:40.790+0700    writing admin.system.version to dump/admin/system.version.bson
2024-04-15T14:59:40.790+0700    done dumping admin.system.version (2 documents)
2024-04-15T14:59:40.792+0700    writing teguhth.pembelian to dump/teguhth/pembelian.bson
2024-04-15T14:59:40.793+0700    writing teguhth.pasok to dump/teguhth/pasok.bson
2024-04-15T14:59:40.794+0700    writing teguhth.suplier to dump/teguhth/suplier.bson
2024-04-15T14:59:40.795+0700    writing kampus.mahasiswi to dump/kampus/mahasiswi.bson
2024-04-15T14:59:40.797+0700    done dumping teguhth.pembelian (15 documents)
2024-04-15T14:59:40.798+0700    writing teguhth.barang to dump/teguhth/barang.bson
2024-04-15T14:59:40.799+0700    done dumping teguhth.pasok (12 documents)
2024-04-15T14:59:40.800+0700    writing teguhth.customer to dump/teguhth/customer.bson
2024-04-15T14:59:40.801+0700    done dumping kampus.mahasiswi (8 documents)
2024-04-15T14:59:40.801+0700    writing tka.barang to dump/tka/barang.bson
2024-04-15T14:59:40.802+0700    done dumping teguhth.barang (6 documents)
2024-04-15T14:59:40.803+0700    writing tka.Barang to dump/tka/Barang.bson
2024-04-15T14:59:40.803+0700    done dumping teguhth.customer (6 documents)
2024-04-15T14:59:40.806+0700    done dumping tka.barang (6 documents)
2024-04-15T14:59:40.807+0700    done dumping teguhth.suplier (6 documents)
2024-04-15T14:59:40.808+0700    done dumping tka.Barang (0 documents)
[root@teguhth ~]#

.::: How to enable CDC (Change Data Capture) in PostgreSQL, EDB :::.

1. Enable WAL (Write-Ahead Logging): PostgreSQL uses Write-Ahead Logging for durability. Ensure that your PostgreSQL server is configured to use WAL.

2. Create Audit Table: Create an audit table where you will log the changes.

CREATE TABLE audit_table (
    id SERIAL PRIMARY KEY,
    table_name TEXT NOT NULL,
    action_type TEXT NOT NULL,
    old_data JSONB,
    new_data JSONB,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);