.::: Setup Polybase in SQL Server for External Table MSSQL :::.

 

Fungsi PolyBase di SQL Server untuk Sumber Eksternal
PolyBase di SQL Server memungkinkan Anda untuk mengakses data dari berbagai sumber eksternal, termasuk SQL Server lain dan MSSQL. Dengan PolyBase, Anda dapat menjalankan query T-SQL di SQL Server yang menggabungkan data dari sumber eksternal tersebut tanpa memindahkan data.

1. setup ODBC MSSQL

2. Buat Credential untuk Login

CREATE DATABASE SCOPED CREDENTIAL RemoteCred
WITH IDENTITY = 'admin', SECRET = 'admin';

.::: Setup Polybase in SQL Server for External Table PostgreSQL :::.

 

Fungsi PolyBase di SQL Server untuk Sumber Eksternal
PolyBase di SQL Server memungkinkan Anda untuk mengakses data dari berbagai sumber eksternal, termasuk SQL Server lain dan PostgreSQL. Dengan PolyBase, Anda dapat menjalankan query T-SQL di SQL Server yang menggabungkan data dari sumber eksternal tersebut tanpa memindahkan data.

1. setup ODBC PostgreSQL
 

2. Buat Credential untuk Login

CREATE DATABASE SCOPED CREDENTIAL PGCred
WITH IDENTITY = 'admin', SECRET = 'admin'

.::: Setup Polybase in SQL Server for External Table MariaDB :::.

 

Fungsi PolyBase di SQL Server untuk Sumber Eksternal
PolyBase di SQL Server memungkinkan Anda untuk mengakses data dari berbagai sumber eksternal, termasuk SQL Server lain dan MariaDB. Dengan PolyBase, Anda dapat menjalankan query T-SQL di SQL Server yang menggabungkan data dari sumber eksternal tersebut tanpa memindahkan data.

1. setup ODBC MariaDB
 

2. Buat Credential untuk Login

CREATE DATABASE SCOPED CREDENTIAL MariadbCred
WITH IDENTITY = 'admin', SECRET = 'admin';

.::: Query to check table, column, compare schema database, structure table in Oracle Database :::.

 

Untuk membandingkan struktur tabel antara dua database teguhth dan aisyah di SQL Server, kita bisa menggunakan query yang membandingkan schema (skema tabel), kolom, tipe data, dan properti lainnya.

A. Query to check table & system table

1. ALL_TABLES & ALL_TAB_COLUMNS

SELECT OWNER, TABLE_NAME
FROM ALL_TABLES
WHERE OWNER = 'AISYAH';

SELECT OWNER, TABLE_NAME
FROM ALL_TABLES
WHERE OWNER = 'TEGUHTH';
 

.::: Query to check table, column, compare schema database, structure table in EDB PostgreSQL :::.

 

Untuk membandingkan struktur tabel antara dua database teguhth dan aisyah di SQL Server, kita bisa menggunakan query yang membandingkan schema (skema tabel), kolom, tipe data, dan properti lainnya.

A. Query to check table & system table

1. INFORMATION_SCHEMA

## from teguhth akses teguhth
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_CATALOG = 'teguhth' AND TABLE_SCHEMA = 'public';
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'public';

## from teguhth akses aisyahdb
SELECT * FROM dblink('dbname=aisyahdb host=10.10.10.9 user=admin password=xxx',
    'SELECT table_catalog, table_schema, table_name, table_type
     FROM information_schema.tables WHERE table_schema = ''public''')
AS t(table_catalog text, table_schema text, table_name text, table_type text);
 

.::: Query to check table, column, compare schema database, structure table in MariaDB MySQL :::.

 

Untuk membandingkan struktur tabel antara dua database teguhth dan aisyah di SQL Server, kita bisa menggunakan query yang membandingkan schema (skema tabel), kolom, tipe data, dan properti lainnya.

A. Query to check table & system table

1. INFORMATION_SCHEMA

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'teguhth';
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'aisyahdb';

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'teguhth';
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'aisyahdb';
 

 

.::: Script EXPDP Backup Daily Database Oracle 19C with Record to Table with Duration with insert client table :::.

 

correlation https://teguhth.blogspot.com/2025/02/script-expdp-backup-daily-database.html

A. Create Backup with record table
1. Create DATA_PUMP_DIR backup directory

CREATE OR REPLACE DIRECTORY DATA_PUMP_DIR AS '/home/oracle/backup';
GRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO aisyah;

2. create schema (dbatools)

.::: Script Backup Full All Database Using Record to Table include start, finish & duration backup Using insert table in PostgreSQL EDB :::.

 

correlation https://teguhth.blogspot.com/2025/02/script-backup-full-all-database-using.html

1. create database & table
CREATE TABLE backuplogduration (
    host VARCHAR(255) NOT NULL,
    backuptime TIMESTAMP NOT NULL,
    start TIMESTAMP NOT NULL,
    finish TIMESTAMP NOT NULL,
    duration VARCHAR(255) NOT NULL,
    dbname VARCHAR(255) NOT NULL,
    type VARCHAR(255) NOT NULL,
    size_mb BIGINT NOT NULL,
    size BIGINT NOT NULL,
    filename VARCHAR(255) NOT NULL
);
 

.::: Script Backup Daily Multiple Database, Backup FULL, Backup LOG, Backup Incremental with Record Table with start, finish, duration backup logs and using insert table client in MariaDB MySQL MarinaDB :::.

 

correlation https://teguhth.blogspot.com/2025/02/script-backup-daily-multiple-database.html

1. Create database & table for save backup log

create database dbatools;
CREATE TABLE backuplogduration  (
    host VARCHAR(255) NOT NULL,
    backuptime  DATETIME NOT NULL,
    start DATETIME NOT NULL,
    finish DATETIME NOT NULL,
    duration VARCHAR(255) NOT NULL,
    dbname VARCHAR(255) NOT NULL,
    type VARCHAR(255) NOT NULL,
    size_mb BIGINT NOT NULL,
    size BIGINT NOT NULL,
    filename VARCHAR(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
 

.::: How to configure Transparent Data Encryption (TDE), Monitoring, Remove TDE, remove certificate, test backup & Restore in SQL Server :::.

 

correlation with https://teguhth.blogspot.com/2024/06/how-to-configure-transparent-data.html

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_source
WITH
SUBJECT='Database_Encryption Source';
GO
 

.::: Create Script Backup Daily Full, Differential, Log SQL Server Using T-SQL with Record to Table with Size Backup and using insert table client and create store procedure sp_backup_full, sp_backup_log, exec sp_backup_diff :::.

 

1. enable sqlcmd

EXEC sp_configure 'show advanced options', 1;
        RECONFIGURE;
        EXEC sp_configure 'xp_cmdshell', 1;
        RECONFIGURE;
        
2. create table record

use dbatools
go

CREATE TABLE BackupLogStatus (
    BackupDate DATETIME,
    DatabaseName NVARCHAR(255),
    BackupType NVARCHAR(50),
    Status NVARCHAR(50),
    BackupFile NVARCHAR(500),
    BackupSizeMB BIGINT  -- Ukuran file dalam byte
);

.::: Query to check table, column, compare schema database, structure table in SQL Server :::.

 

Untuk membandingkan struktur tabel antara dua database teguhth dan aisyah di SQL Server, kita bisa menggunakan query yang membandingkan schema (skema tabel), kolom, tipe data, dan properti lainnya.

A. Query to check table & system table

1. INFORMATION_SCHEMA
select * from teguhth.INFORMATION_SCHEMA.tables
select * from aisyah.INFORMATION_SCHEMA.tables

select * from teguhth.INFORMATION_SCHEMA.COLUMNS
select * from aisyah.INFORMATION_SCHEMA.COLUMNS
 

.::: Create Script Backup Daily Full, Differential, Log SQL Server Using T-SQL with Record to Table with Size Backup and create store procedure sp_backup_full, sp_backup_log, exec sp_backup_diff :::.

 

1. enable sqlcmd

EXEC sp_configure 'show advanced options', 1;
        RECONFIGURE;
        EXEC sp_configure 'xp_cmdshell', 1;
        RECONFIGURE;
        
2. create table record

CREATE TABLE BackupLogStatus (
    BackupDate DATETIME,
    DatabaseName NVARCHAR(255),
    BackupType NVARCHAR(50),
    Status NVARCHAR(50),
    BackupFile NVARCHAR(500),
    BackupSizeMB BIGINT  -- Ukuran file dalam byte
);