.::: create Tools Migration from SQL Server MSSQL to MariaDB using bash shell script using insert & NoCSV :::.

 

correlation with https://teguhth.blogspot.com/2024/01/install-configure-odbcinstini-odbc-for.html

1. sample data mssql & mariadb

MySQL MariaDB
https://teguhth.blogspot.com/2019/04/study-kasus-praktis-belajar-query-mysql.html

MSSQL
https://teguhth.blogspot.com/2019/04/study-kasus-praktis-belajar-query.html

2. data server A & B 
server A sql server
ip : 10.10.10.7
db : teguhth
schema : dbo
port 1433
user : admin 
password : admin11!!

server B Mariadb
ip : 10.10.10.90
db : teguhthsql
port 3306
user : admin
password : admin

mysql -h 10.10.10.90 -uadmin -p --skip-ssl

buat script shell untuk migrasi dari server A mssql to server B 
 3. run script migrasi 

[root@teguhth-all mssql-to-maria]# ls  | grep _realtime
migrasi_barang_mssql_to_mariadb_realtime_log.sh
migrasi_customer_mssql_to_mariadb_realtime_log.sh
migrasi_pasok_mssql_to_mariadb_realtime_log.sh
migrasi_pembelian_mssql_to_mariadb_realtime_log.sh
migrasi_suplier_mssql_to_mariadb_realtime_log.sh
[root@teguhth-all mssql-to-maria]#
 

4. result after migrasi 
 

5. script migrasi table barang

[root@teguhth-all mssql-to-maria]# cat migrasi_barang_mssql_to_mariadb_realtime_log.sh
#!/bin/bash
set -o pipefail

# ===============================
# CONFIG SQL SERVER (SOURCE)
# ===============================
MSSQL_HOST="10.10.10.7"
MSSQL_PORT="1433"
MSSQL_DB="teguhth"
MSSQL_USER="admin"
MSSQL_PASS='admin11!!'

# ===============================
# CONFIG MARIADB (TARGET)
# ===============================
MYSQL_HOST="10.10.10.90"
MYSQL_PORT="3306"
MYSQL_DB="teguhthsql"
MYSQL_USER="admin"
MYSQL_PASS="admin"

LOG_DIR="/data/migrasi/mssql"
LOG_FILE="${LOG_DIR}/barang_insert_realtime.log"
ERR_FILE="${LOG_DIR}/barang_error.log"

mkdir -p "$LOG_DIR"
> "$LOG_FILE"
> "$ERR_FILE"

echo "== Prepare table BARANG di MariaDB =="

mariadb -h ${MYSQL_HOST} -P ${MYSQL_PORT} \
  -u ${MYSQL_USER} -p${MYSQL_PASS} \
  --skip-ssl ${MYSQL_DB} <<EOF
CREATE TABLE IF NOT EXISTS barang (
    KODE_BARANG CHAR(6) NOT NULL,
    NAMA_BARANG VARCHAR(25),
    SATUAN_BARANG VARCHAR(20),
    STOK_BARANG DECIMAL(4),
    PRIMARY KEY (KODE_BARANG)
);
TRUNCATE TABLE barang;
EOF

if [ $? -ne 0 ]; then
  echo "? Gagal prepare table barang"
  exit 1
fi

echo
echo "== Migrasi BARANG (Realtime Log + Counter) =="

sqlcmd \
  -S ${MSSQL_HOST},${MSSQL_PORT} \
  -U ${MSSQL_USER} \
  -P "${MSSQL_PASS}" \
  -d ${MSSQL_DB} \
  -C \
  -W -h -1 \
  -Q "
SET NOCOUNT ON;
SELECT
  'INSERT INTO barang
   (KODE_BARANG,NAMA_BARANG,SATUAN_BARANG,STOK_BARANG)
   VALUES (''' +
   REPLACE(KODE_BARANG,'''','''''') + ''',''' +
   REPLACE(NAMA_BARANG,'''','''''') + ''',''' +
   REPLACE(SATUAN_BARANG,'''','''''') + ''',' +
   CAST(STOK_BARANG AS VARCHAR(20)) +
   ');'
FROM dbo.barang;
" \
| stdbuf -oL tee \
    >(awk '{c++; printf "\r??  Inserted rows: %d", c; fflush()}' >&2) \
    "$LOG_FILE" \
| mariadb -h ${MYSQL_HOST} -P ${MYSQL_PORT} \
    -u ${MYSQL_USER} -p${MYSQL_PASS} \
    --skip-ssl ${MYSQL_DB} \
    2>> "$ERR_FILE"

RET=$?

echo
echo

if [ $RET -ne 0 ]; then
  echo "? Migrasi gagal"
  echo "?? Error log: $ERR_FILE"
  exit 1
fi

echo "== Verifikasi Hasil =="

mariadb -N -s \
  -h ${MYSQL_HOST} -P ${MYSQL_PORT} \
  -u ${MYSQL_USER} -p${MYSQL_PASS} \
  --skip-ssl ${MYSQL_DB} <<EOF
SELECT COUNT(*) AS total_barang FROM barang;
EOF

echo
echo "? Migrasi BARANG BERHASIL"
echo "?? Log SQL   : $LOG_FILE"
echo "?? Error log : $ERR_FILE"
echo "==============================================="

[root@teguhth-all mssql-to-maria]# pwd
/data/migrasi/mssql-to-maria
[root@teguhth-all mssql-to-maria]#

6. script migrasi table suplier

[root@teguhth-all mssql-to-maria]# pwd
/data/migrasi/mssql-to-maria
[root@teguhth-all mssql-to-maria]# cat migrasi_suplier_mssql_to_mariadb_realtime_log.sh
#!/bin/bash
set -o pipefail

# ===============================
# CONFIG SQL SERVER (SOURCE)
# ===============================
MSSQL_HOST="10.10.10.7"
MSSQL_PORT="1433"
MSSQL_DB="teguhth"
MSSQL_USER="admin"
MSSQL_PASS='admin11!!'

# ===============================
# CONFIG MARIADB (TARGET)
# ===============================
MYSQL_HOST="10.10.10.90"
MYSQL_PORT="3306"
MYSQL_DB="teguhthsql"
MYSQL_USER="admin"
MYSQL_PASS="admin"

LOG_DIR="/data/migrasi/mssql"
LOG_FILE="${LOG_DIR}/suplier_insert_realtime.log"
ERR_FILE="${LOG_DIR}/suplier_error.log"

mkdir -p "$LOG_DIR"
> "$LOG_FILE"
> "$ERR_FILE"

echo "== Prepare table SUPLIER di MariaDB =="

mariadb -h ${MYSQL_HOST} -P ${MYSQL_PORT} \
  -u ${MYSQL_USER} -p${MYSQL_PASS} \
  --skip-ssl ${MYSQL_DB} <<EOF
CREATE TABLE IF NOT EXISTS suplier (
    KODE_SUPLIER CHAR(5) NOT NULL,
    NAMA_SUPLIER VARCHAR(30),
    ALAMAT_SUPLIER VARCHAR(30),
    KOTA_SUPLIER VARCHAR(15),
    TELEPON_SUPLIER VARCHAR(15),
    PRIMARY KEY (KODE_SUPLIER)
);
TRUNCATE TABLE suplier;
EOF

if [ $? -ne 0 ]; then
  echo "? Gagal prepare table suplier"
  exit 1
fi

echo
echo "== Migrasi SUPLIER (Realtime Log + Counter) =="

sqlcmd \
  -S ${MSSQL_HOST},${MSSQL_PORT} \
  -U ${MSSQL_USER} \
  -P "${MSSQL_PASS}" \
  -d ${MSSQL_DB} \
  -C \
  -W -h -1 \
  -Q "
SET NOCOUNT ON;
SELECT
  'INSERT INTO suplier
   (KODE_SUPLIER,NAMA_SUPLIER,ALAMAT_SUPLIER,KOTA_SUPLIER,TELEPON_SUPLIER)
   VALUES (''' +
   REPLACE(KODE_SUPLIER,'''','''''') + ''',''' +
   REPLACE(NAMA_SUPLIER,'''','''''') + ''',''' +
   REPLACE(ALAMAT_SUPLIER,'''','''''') + ''',''' +
   REPLACE(KOTA_SUPLIER,'''','''''') + ''',''' +
   REPLACE(TELEPON_SUPLIER,'''','''''') +
   ''');'
FROM dbo.suplier;
" \
| stdbuf -oL tee \
    >(awk '{c++; printf "\r??  Inserted rows: %d", c; fflush()}' >&2) \
    "$LOG_FILE" \
| mariadb -h ${MYSQL_HOST} -P ${MYSQL_PORT} \
    -u ${MYSQL_USER} -p${MYSQL_PASS} \
    --skip-ssl ${MYSQL_DB} \
    2>> "$ERR_FILE"

RET=$?

echo
echo

if [ $RET -ne 0 ]; then
  echo "? Migrasi gagal"
  echo "?? Error log: $ERR_FILE"
  exit 1
fi

echo "== Verifikasi Hasil =="

mariadb -N -s \
  -h ${MYSQL_HOST} -P ${MYSQL_PORT} \
  -u ${MYSQL_USER} -p${MYSQL_PASS} \
  --skip-ssl ${MYSQL_DB} <<EOF
SELECT COUNT(*) AS total_suplier FROM suplier;
EOF

echo
echo "? Migrasi SUPLIER BERHASIL"
echo "?? Log SQL   : $LOG_FILE"
echo "?? Error log : $ERR_FILE"
echo "==============================================="

[root@teguhth-all mssql-to-maria]#

7. script migrasi table customer

[root@teguhth-all mssql-to-maria]# pwd
/data/migrasi/mssql-to-maria
[root@teguhth-all mssql-to-maria]# cat migrasi_customer_mssql_to_mariadb_realtime_log.sh
#!/bin/bash
set -o pipefail

# ===============================
# CONFIG SQL SERVER (SOURCE)
# ===============================
MSSQL_HOST="10.10.10.7"
MSSQL_PORT="1433"
MSSQL_DB="teguhth"
MSSQL_USER="admin"
MSSQL_PASS='admin11!!'

# ===============================
# CONFIG MARIADB (TARGET)
# ===============================
MYSQL_HOST="10.10.10.90"
MYSQL_PORT="3306"
MYSQL_DB="teguhthsql"
MYSQL_USER="admin"
MYSQL_PASS="admin"

LOG_DIR="/data/migrasi/mssql"
LOG_FILE="${LOG_DIR}/customer_insert_realtime.log"
ERR_FILE="${LOG_DIR}/customer_error.log"

mkdir -p "$LOG_DIR"
> "$LOG_FILE"
> "$ERR_FILE"

echo "== Prepare table CUSTOMER di MariaDB =="

mariadb -h ${MYSQL_HOST} -P ${MYSQL_PORT} \
  -u ${MYSQL_USER} -p${MYSQL_PASS} \
  --skip-ssl ${MYSQL_DB} <<EOF
CREATE TABLE IF NOT EXISTS customer (
    KODE_CUSTOMER CHAR(6) NOT NULL,
    NAMA_CUSTOMER VARCHAR(30),
    ALAMAT_CUSTOMER VARCHAR(30),
    KOTA_CUSTOMER VARCHAR(15),
    TELEPON_CUSTOMER VARCHAR(15),
    PRIMARY KEY (KODE_CUSTOMER)
);
TRUNCATE TABLE customer;
EOF

if [ $? -ne 0 ]; then
  echo "? Gagal prepare table customer"
  exit 1
fi

echo
echo "== Migrasi CUSTOMER (Realtime Log + Counter) =="

sqlcmd \
  -S ${MSSQL_HOST},${MSSQL_PORT} \
  -U ${MSSQL_USER} \
  -P "${MSSQL_PASS}" \
  -d ${MSSQL_DB} \
  -C \
  -W -h -1 \
  -Q "
SET NOCOUNT ON;
SELECT
  'INSERT INTO customer
   (KODE_CUSTOMER,NAMA_CUSTOMER,ALAMAT_CUSTOMER,KOTA_CUSTOMER,TELEPON_CUSTOMER)
   VALUES (''' +
   REPLACE(KODE_CUSTOMER,'''','''''') + ''',''' +
   REPLACE(NAMA_CUSTOMER,'''','''''') + ''',''' +
   REPLACE(ALAMAT_CUSTOMER,'''','''''') + ''',''' +
   REPLACE(KOTA_CUSTOMER,'''','''''') + ''',''' +
   REPLACE(TELEPON_CUSTOMER,'''','''''') +
   ''');'
FROM dbo.customer;
" \
| stdbuf -oL tee \
    >(awk '{c++; printf "\r??  Inserted rows: %d", c; fflush()}' >&2) \
    "$LOG_FILE" \
| mariadb -h ${MYSQL_HOST} -P ${MYSQL_PORT} \
    -u ${MYSQL_USER} -p${MYSQL_PASS} \
    --skip-ssl ${MYSQL_DB} \
    2>> "$ERR_FILE"

RET=$?

echo
echo

if [ $RET -ne 0 ]; then
  echo "? Migrasi gagal"
  echo "?? Error log: $ERR_FILE"
  exit 1
fi

echo "== Verifikasi Hasil =="

mariadb -N -s \
  -h ${MYSQL_HOST} -P ${MYSQL_PORT} \
  -u ${MYSQL_USER} -p${MYSQL_PASS} \
  --skip-ssl ${MYSQL_DB} <<EOF
SELECT COUNT(*) AS total_customer FROM customer;
EOF

echo
echo "? Migrasi CUSTOMER BERHASIL"
echo "?? Log SQL   : $LOG_FILE"
echo "?? Error log : $ERR_FILE"
echo "==============================================="

[root@teguhth-all mssql-to-maria]#

8. script migrasi table pasok

[root@teguhth-all mssql-to-maria]# pwd
/data/migrasi/mssql-to-maria
[root@teguhth-all mssql-to-maria]# cat migrasi_pasok_mssql_to_mariadb_realtime_log.sh
#!/bin/bash
set -o pipefail

# ===============================
# CONFIG SQL SERVER (SOURCE)
# ===============================
MSSQL_HOST="10.10.10.7"
MSSQL_PORT="1433"
MSSQL_DB="teguhth"
MSSQL_USER="admin"
MSSQL_PASS='admin11!!'

# ===============================
# CONFIG MARIADB (TARGET)
# ===============================
MYSQL_HOST="10.10.10.90"
MYSQL_PORT="3306"
MYSQL_DB="teguhthsql"
MYSQL_USER="admin"
MYSQL_PASS="admin"

LOG_DIR="/data/migrasi/mssql"
LOG_FILE="${LOG_DIR}/pasok_insert_realtime.log"
ERR_FILE="${LOG_DIR}/pasok_error.log"

mkdir -p "$LOG_DIR"
> "$LOG_FILE"
> "$ERR_FILE"

echo "== Prepare table PASOK di MariaDB =="

mariadb -h ${MYSQL_HOST} -P ${MYSQL_PORT} \
  -u ${MYSQL_USER} -p${MYSQL_PASS} \
  --skip-ssl ${MYSQL_DB} <<EOF
CREATE TABLE IF NOT EXISTS pasok (
    KODE_PASOK CHAR(10),
    KODE_BARANG CHAR(6),
    KODE_SUPLIER CHAR(5),
    TANGGAL_PASOK DATE,
    JUMLAH_PASOK DECIMAL(4),
    PRIMARY KEY (KODE_PASOK, KODE_BARANG, KODE_SUPLIER),
    FOREIGN KEY (KODE_BARANG) REFERENCES barang(KODE_BARANG),
    FOREIGN KEY (KODE_SUPLIER) REFERENCES suplier(KODE_SUPLIER)
);
TRUNCATE TABLE pasok;
EOF

if [ $? -ne 0 ]; then
  echo "? Gagal prepare table pasok"
  exit 1
fi

echo
echo "== Migrasi PASOK (Realtime Log + Counter) =="

sqlcmd \
  -S ${MSSQL_HOST},${MSSQL_PORT} \
  -U ${MSSQL_USER} \
  -P "${MSSQL_PASS}" \
  -d ${MSSQL_DB} \
  -C \
  -W -h -1 \
  -Q "
SET NOCOUNT ON;
SELECT
  'INSERT INTO pasok
   (KODE_PASOK,KODE_BARANG,KODE_SUPLIER,TANGGAL_PASOK,JUMLAH_PASOK)
   VALUES (''' +
   REPLACE(KODE_PASOK,'''','''''') + ''',''' +
   REPLACE(KODE_BARANG,'''','''''') + ''',''' +
   REPLACE(KODE_SUPLIER,'''','''''') + ''',''' +
   CONVERT(VARCHAR(10), TANGGAL_PASOK, 23) + ''',''' +
   CAST(JUMLAH_PASOK AS VARCHAR) +
   ''');'
FROM dbo.pasok;
" \
| stdbuf -oL tee \
    >(awk '{c++; printf "\r??  Inserted rows: %d", c; fflush()}' >&2) \
    "$LOG_FILE" \
| mariadb -h ${MYSQL_HOST} -P ${MYSQL_PORT} \
    -u ${MYSQL_USER} -p${MYSQL_PASS} \
    --skip-ssl ${MYSQL_DB} \
    2>> "$ERR_FILE"

RET=$?

echo
echo

if [ $RET -ne 0 ]; then
  echo "? Migrasi gagal"
  echo "?? Error log: $ERR_FILE"
  exit 1
fi

echo "== Verifikasi Hasil =="

mariadb -N -s \
  -h ${MYSQL_HOST} -P ${MYSQL_PORT} \
  -u ${MYSQL_USER} -p${MYSQL_PASS} \
  --skip-ssl ${MYSQL_DB} <<EOF
SELECT COUNT(*) AS total_pasok FROM pasok;
EOF

echo
echo "? Migrasi PASOK BERHASIL"
echo "?? Log SQL   : $LOG_FILE"
echo "?? Error log : $ERR_FILE"
echo "==============================================="

[root@teguhth-all mssql-to-maria]#

9. script migrasi table pembelian

[root@teguhth-all mssql-to-maria]# pwd
/data/migrasi/mssql-to-maria
[root@teguhth-all mssql-to-maria]# cat migrasi_pembelian_mssql_to_mariadb_realtime_log.sh
#!/bin/bash
set -o pipefail

# ===============================
# CONFIG SQL SERVER (SOURCE)
# ===============================
MSSQL_HOST="10.10.10.7"
MSSQL_PORT="1433"
MSSQL_DB="teguhth"
MSSQL_USER="admin"
MSSQL_PASS='admin11!!'

# ===============================
# CONFIG MARIADB (TARGET)
# ===============================
MYSQL_HOST="10.10.10.90"
MYSQL_PORT="3306"
MYSQL_DB="teguhthsql"
MYSQL_USER="admin"
MYSQL_PASS="admin"

LOG_DIR="/data/migrasi/mssql"
LOG_FILE="${LOG_DIR}/pembelian_insert_realtime.log"
ERR_FILE="${LOG_DIR}/pembelian_error.log"

mkdir -p "$LOG_DIR"
> "$LOG_FILE"
> "$ERR_FILE"

echo "== Prepare table PEMBELIAN di MariaDB =="

mariadb -h ${MYSQL_HOST} -P ${MYSQL_PORT} \
  -u ${MYSQL_USER} -p${MYSQL_PASS} \
  --skip-ssl ${MYSQL_DB} <<EOF
CREATE TABLE IF NOT EXISTS pembelian (
    KODE_PEMBELIAN CHAR(10),
    KODE_BARANG CHAR(6),
    KODE_CUSTOMER CHAR(6),
    TANGGAL_PEMBELIAN DATE,
    JUMLAH_PEMBELIAN DECIMAL(4),
    PRIMARY KEY (KODE_PEMBELIAN, KODE_BARANG, KODE_CUSTOMER),
    FOREIGN KEY (KODE_BARANG) REFERENCES barang(KODE_BARANG),
    FOREIGN KEY (KODE_CUSTOMER) REFERENCES customer(KODE_CUSTOMER)
);
TRUNCATE TABLE pembelian;
EOF

if [ $? -ne 0 ]; then
  echo "? Gagal prepare table pembelian"
  exit 1
fi

echo
echo "== Migrasi PEMBELIAN (Realtime Log + Counter) =="

sqlcmd \
  -S ${MSSQL_HOST},${MSSQL_PORT} \
  -U ${MSSQL_USER} \
  -P "${MSSQL_PASS}" \
  -d ${MSSQL_DB} \
  -C \
  -W -h -1 \
  -Q "
SET NOCOUNT ON;
SELECT
  'INSERT INTO pembelian
   (KODE_PEMBELIAN,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN)
   VALUES (''' +
   REPLACE(KODE_PEMBELIAN,'''','''''') + ''',''' +
   REPLACE(KODE_BARANG,'''','''''') + ''',''' +
   REPLACE(KODE_CUSTOMER,'''','''''') + ''',''' +
   CONVERT(VARCHAR(10), TANGGAL_PEMBELIAN, 23) + ''',''' +
   CAST(JUMLAH_PEMBELIAN AS VARCHAR) +
   ''');'
FROM dbo.pembelian;
" \
| stdbuf -oL tee \
    >(awk '{c++; printf "\r??  Inserted rows: %d", c; fflush()}' >&2) \
    "$LOG_FILE" \
| mariadb -h ${MYSQL_HOST} -P ${MYSQL_PORT} \
    -u ${MYSQL_USER} -p${MYSQL_PASS} \
    --skip-ssl ${MYSQL_DB} \
    2>> "$ERR_FILE"

RET=$?

echo
echo

if [ $RET -ne 0 ]; then
  echo "? Migrasi gagal"
  echo "?? Error log: $ERR_FILE"
  exit 1
fi

echo "== Verifikasi Hasil =="

mariadb -N -s \
  -h ${MYSQL_HOST} -P ${MYSQL_PORT} \
  -u ${MYSQL_USER} -p${MYSQL_PASS} \
  --skip-ssl ${MYSQL_DB} <<EOF
SELECT COUNT(*) AS total_pembelian FROM pembelian;
EOF

echo
echo "? Migrasi PEMBELIAN BERHASIL"
echo "?? Log SQL   : $LOG_FILE"
echo "?? Error log : $ERR_FILE"
echo "==============================================="

[root@teguhth-all mssql-to-maria]#