.::: Migration Microsoft SQL Server to MariaDB/MySQL using MySQL WorkBench :::.

 

A. Pree

1. capture mysql for destination/Target

  show databases

 

2. capture MSSQL for source

select * from sys.databases
use teguhth 
select * from INFORMATION_SCHEMA.TABLES
exec sp_columns barang
select * from barang

B. Setting Driver for SQL Server 

1. Open MySQL WorkBench. Database -> Migration Wizard

2. Open ODBC Administrator -> Use DNS -> Add / Configure

3. on Microsoft SQL Server DSN Configuration. next until finish

 name : sqltest 
 server source = 127.0.0.1
 WithSQL Server Authentication using a login ID & Password by user
 Login ID = sa
 passwd  = 

4. Data sources for SQL Server (ODBC driver 17 for SQL Server) done 

C. Migration 

1. Open MySQL WorkBench. Database -> Migration Wizard

== Source & Target ==

2. Source Selection
 Database System = Microsoft SQL Server 
 Connection Method = ODBC Data Source (FreeTDS)
 Parameter
 DSN = sqltest(ODBC driver 17 for SQL Server) 
 username = sa 
 Protocol Version = Microsoft SQL Server 2021(7.2)
 Database = teguhth
 test connection 

3. Target selection

  Hostname = 127.0.0.1
  username = root
  default schema = <blank> 
  test connection 

4. Fetch Schemas list

5. Schemas selection 

  check. only one schema: catalog.schema.table -> Catalog.Table 

6. Reverse Engineer resource

== Object migration ==

7. Source Objects = select table will be migration 

8. Migration

9. Manual Editing 

10. Target Creation Options

 Create Schema in target RDBMS

11. Create Schemas 

12. Create Target Results

== Data Migration ==

13. Data Transfer setup 

 check Online copy of table data to target RDBMS 

14. Bulk Data Transfer 

== Report == 

15. Migration Report 

------------------------------------------------------------------------------------
MySQL Workbench Migration Wizard Report
Date: Thu Jan 28 14:28:16 2021
Source: Microsoft SQL Server 11.0.2100
Target: MySQL 10.1.14
------------------------------------------------------------------------------------
I. Migration
1. Summary
Number of migrated schemas: 1
1. teguhth
Source Schema:   teguhth
- Tables:             5
- Triggers:           0
- Views:              0
- Stored Procedures:  0
- Functions:          0

2. Migration Issues
  - teguhth
    note  Collation SQL_Latin1_General_CP1_CI_AS migrated to utf8_general_ci
  - barang
    note  Collation SQL_Latin1_General_CP1_CI_AS migrated to utf8_general_ci
  - KODE_BARANG
    note  Collation SQL_Latin1_General_CP1_CI_AS migrated to utf8_general_ci
  - NAMA_BARANG
    note  Collation SQL_Latin1_General_CP1_CI_AS migrated to utf8_general_ci
  - SATUAN_BARANG
    note  Collation SQL_Latin1_General_CP1_CI_AS migrated to utf8_general_ci
  - suplier
    note  Collation SQL_Latin1_General_CP1_CI_AS migrated to utf8_general_ci
  - KODE_SUPLIER
    note  Collation SQL_Latin1_General_CP1_CI_AS migrated to utf8_general_ci
  - NAMA_SUPLIER
    note  Collation SQL_Latin1_General_CP1_CI_AS migrated to utf8_general_ci
  - ALAMAT_SUPLIER
    note  Collation SQL_Latin1_General_CP1_CI_AS migrated to utf8_general_ci
  - KOTA_SUPLIER
    note  Collation SQL_Latin1_General_CP1_CI_AS migrated to utf8_general_ci
  - TELEPON_SUPLIER
    note  Collation SQL_Latin1_General_CP1_CI_AS migrated to utf8_general_ci
  - customer
    note  Collation SQL_Latin1_General_CP1_CI_AS migrated to utf8_general_ci
  - KODE_CUSTOMER
    note  Collation SQL_Latin1_General_CP1_CI_AS migrated to utf8_general_ci
  - NAMA_CUSTOMER
    note  Collation SQL_Latin1_General_CP1_CI_AS migrated to utf8_general_ci
  - ALAMAT_CUSTOMER
    note  Collation SQL_Latin1_General_CP1_CI_AS migrated to utf8_general_ci
  - KOTA_CUSTOMER
    note  Collation SQL_Latin1_General_CP1_CI_AS migrated to utf8_general_ci
  - TELEPON_CUSTOMER
    note  Collation SQL_Latin1_General_CP1_CI_AS migrated to utf8_general_ci
  - pasok
    note  Collation SQL_Latin1_General_CP1_CI_AS migrated to utf8_general_ci
  - KODE_PASOK
    note  Collation SQL_Latin1_General_CP1_CI_AS migrated to utf8_general_ci
  - KODE_BARANG
    note  Collation SQL_Latin1_General_CP1_CI_AS migrated to utf8_general_ci
  - KODE_SUPLIER
    note  Collation SQL_Latin1_General_CP1_CI_AS migrated to utf8_general_ci
  - pembelian
    note  Collation SQL_Latin1_General_CP1_CI_AS migrated to utf8_general_ci
  - KODE_PEMBELIAN
    note  Collation SQL_Latin1_General_CP1_CI_AS migrated to utf8_general_ci
  - KODE_BARANG
    note  Collation SQL_Latin1_General_CP1_CI_AS migrated to utf8_general_ci
  - KODE_CUSTOMER
    note  Collation SQL_Latin1_General_CP1_CI_AS migrated to utf8_general_ci
3. Object Creation Issues
4. Migration Details
4.1. Table teguhth.barang (barang)
Columns:
  - KODE_BARANG CHAR(6)    
  - NAMA_BARANG VARCHAR(25)    
  - SATUAN_BARANG VARCHAR(20)    
  - STOK_BARANG SMALLINT    
Foreign Keys:
Indices:
  - PRIMARY (KODE_BARANG)
4.2. Table teguhth.suplier (suplier)
Columns:
  - KODE_SUPLIER CHAR(5)    
  - NAMA_SUPLIER VARCHAR(30)    
  - ALAMAT_SUPLIER VARCHAR(30)    
  - KOTA_SUPLIER VARCHAR(15)    
  - TELEPON_SUPLIER VARCHAR(15)    
Foreign Keys:
Indices:
  - PRIMARY (KODE_SUPLIER)
4.3. Table teguhth.customer (customer)
Columns:
  - KODE_CUSTOMER CHAR(6)    
  - NAMA_CUSTOMER VARCHAR(30)    
  - ALAMAT_CUSTOMER VARCHAR(30)    
  - KOTA_CUSTOMER VARCHAR(15)    
  - TELEPON_CUSTOMER VARCHAR(15)    
Foreign Keys:
Indices:
  - PRIMARY (KODE_CUSTOMER)
4.4. Table teguhth.pasok (pasok)
Columns:
  - KODE_PASOK CHAR(10)    
  - KODE_BARANG CHAR(6)    
  - KODE_SUPLIER CHAR(5)    
  - TANGGAL_PASOK DATE    
  - JUMLAH_PASOK SMALLINT    
Foreign Keys:
  - FK__pasok__KODE_BARA__164452B1 (KODE_BARANG) ON barang (KODE_BARANG)
  - FK__pasok__KODE_SUPL__173876EA (KODE_SUPLIER) ON suplier (KODE_SUPLIER)
Indices:
  - PRIMARY (KODE_PASOK, KODE_BARANG, KODE_SUPLIER)
4.5. Table teguhth.pembelian (pembelian)
Columns:
  - KODE_PEMBELIAN CHAR(10)    
  - KODE_BARANG CHAR(6)    
  - KODE_CUSTOMER CHAR(6)    
  - TANGGAL_PEMBELIAN DATE    
  - JUMLAH_PEMBELIAN SMALLINT    
Foreign Keys:
  - FK__pembelian__KODE___1A14E395 (KODE_BARANG) ON barang (KODE_BARANG)
  - FK__pembelian__KODE___1B0907CE (KODE_CUSTOMER) ON customer (KODE_CUSTOMER)
Indices:
  - PRIMARY (KODE_PEMBELIAN, KODE_BARANG, KODE_CUSTOMER)
II. Data Copy
  - `teguhth`.`pembelian`
            Succeeded : copied 15 of 15 rows from [teguhth].[dbo].[pembelian]    
  - `teguhth`.`suplier`
            Succeeded : copied 6 of 6 rows from [teguhth].[dbo].[suplier]    
  - `teguhth`.`customer`
            Succeeded : copied 6 of 6 rows from [teguhth].[dbo].[customer]    
  - `teguhth`.`barang`
            Succeeded : copied 6 of 6 rows from [teguhth].[dbo].[barang]    
  - `teguhth`.`pasok`
            Succeeded : copied 12 of 12 rows from [teguhth].[dbo].[pasok]    

D. Post Migration 

1. capture mysql for destination/Target

show databases;

use teguhth 

select * from barang;

2. capture MSSQL for source

select * from sys.databases
use teguhth 
select * from INFORMATION_SCHEMA.TABLES
exec sp_columns barang
select * from barang 

  

3. Test Insert table 
select * from barang where KODE_BARANG ='ELK-99';
select * from suplier where KODE_SUPLIER ='EJ-99';
select * from customer where KODE_CUSTOMER ='J-0099';
select * from pasok where KODE_PASOK ='PAS-E099';
select * from pembelian where KODE_PEMBELIAN ='BEL-E099';

insert into barang(KODE_BARANG,NAMA_BARANG,SATUAN_BARANG,STOK_BARANG) values('ELK-99','Barang - After Migration','BUAH',20);
insert into suplier(KODE_SUPLIER,NAMA_SUPLIER,ALAMAT_SUPLIER,KOTA_SUPLIER,TELEPON_SUPLIER) values ('EJ-99','Suplier - After Migration','JL THAMRIN 12','JAKARTA','(021) 850-2301');
insert into customer(KODE_CUSTOMER,NAMA_CUSTOMER,ALAMAT_CUSTOMER,KOTA_CUSTOMER,TELEPON_CUSTOMER) values('J-0099','Customer - After Migration','JL CIMANGGIS 34','JAKARTA','(021) 856-4209');
insert into pasok(KODE_PASOK,KODE_BARANG,KODE_SUPLIER,TANGGAL_PASOK,JUMLAH_PASOK) values('PAS-E099','ELK-99','EJ-99','1-Jan-02',8);
insert into pembelian(KODE_PEMBELIAN,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E099','ELK-99','J-0099','20-May-02',3);
select * from barang where KODE_BARANG ='ELK-99';
select * from suplier where KODE_SUPLIER ='EJ-99';
select * from customer where KODE_CUSTOMER ='J-0099';
select * from pasok where KODE_PASOK ='PAS-E099';
select * from pembelian where KODE_PEMBELIAN ='BEL-E099';
delete from barang where kode_barang = 'ELK-99';
delete from suplier where kode_suplier = 'EJ-99';
delete from customer where kode_customer = 'J-0099';
delete from pasok where kode_pasok = 'PAS-E099';
delete from pembelian where kode_pembelian = 'BEL-E099';
select * from barang where KODE_BARANG ='ELK-99';
select * from suplier where KODE_SUPLIER ='EJ-99';
select * from customer where KODE_CUSTOMER ='J-0099';
select * from pasok where KODE_PASOK ='PAS-E099';
select * from pembelian where KODE_PEMBELIAN ='BEL-E099';
log 
MariaDB [teguhth]>
MariaDB [teguhth]> select * from barang where KODE_BARANG ='ELK-99';
Empty set (0.03 sec)
MariaDB [teguhth]> select * from suplier where KODE_SUPLIER ='EJ-99';
Empty set (0.00 sec)
MariaDB [teguhth]> select * from customer where KODE_CUSTOMER ='J-0099';
Empty set (0.00 sec)
MariaDB [teguhth]> select * from pasok where KODE_PASOK ='PAS-E099';
Empty set (0.06 sec)
MariaDB [teguhth]> select * from pembelian where KODE_PEMBELIAN ='BEL-E099';
Empty set (0.00 sec)
MariaDB [teguhth]>
MariaDB [teguhth]> insert into barang(KODE_BARANG,NAMA_BARANG,SATUAN_BARANG,STOK_BARANG) values('ELK-99','Barang - After Migration','BUAH',20);
Query OK, 1 row affected (0.09 sec)
MariaDB [teguhth]> insert into suplier(KODE_SUPLIER,NAMA_SUPLIER,ALAMAT_SUPLIER,KOTA_SUPLIER,TELEPON_SUPLIER) values ('EJ-99','Suplier - After Migration','JL THAMRIN 12','JAKARTA','(021) 850-2301');
Query OK, 1 row affected (0.04 sec)
MariaDB [teguhth]> insert into customer(KODE_CUSTOMER,NAMA_CUSTOMER,ALAMAT_CUSTOMER,KOTA_CUSTOMER,TELEPON_CUSTOMER) values('J-0099','Customer - After Migration','JL CIMANGGIS 34','JAKARTA','(021) 856-4209');
Query OK, 1 row affected (0.03 sec)
MariaDB [teguhth]> insert into pasok(KODE_PASOK,KODE_BARANG,KODE_SUPLIER,TANGGAL_PASOK,JUMLAH_PASOK) values('PAS-E099','ELK-99','EJ-99','1-Jan-02',8);
ERROR 1292 (22007): Incorrect date value: '1-Jan-02' for column 'TANGGAL_PASOK' at row 1
MariaDB [teguhth]> insert into pembelian(KODE_PEMBELIAN,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E099','ELK-99','J-0099','20-May-02',3);
ERROR 1292 (22007): Incorrect date value: '20-May-02' for column 'TANGGAL_PEMBELIAN' at row 1
MariaDB [teguhth]>
MariaDB [teguhth]> select * from barang where KODE_BARANG ='ELK-99';
+-------------+--------------------------+---------------+-------------+
| KODE_BARANG | NAMA_BARANG              | SATUAN_BARANG | STOK_BARANG |
+-------------+--------------------------+---------------+-------------+
| ELK-99      | Barang - After Migration | BUAH          |          20 |
+-------------+--------------------------+---------------+-------------+
1 row in set (0.00 sec)
MariaDB [teguhth]> select * from suplier where KODE_SUPLIER ='EJ-99';
+--------------+---------------------------+----------------+--------------+-----------------+
| KODE_SUPLIER | NAMA_SUPLIER              | ALAMAT_SUPLIER | KOTA_SUPLIER | TELEPON_SUPLIER |
+--------------+---------------------------+----------------+--------------+-----------------+
| EJ-99        | Suplier - After Migration | JL THAMRIN 12  | JAKARTA      | (021) 850-2301  |
+--------------+---------------------------+----------------+--------------+-----------------+
1 row in set (0.00 sec)
MariaDB [teguhth]> select * from customer where KODE_CUSTOMER ='J-0099';
+---------------+----------------------------+-----------------+---------------+------------------+
| KODE_CUSTOMER | NAMA_CUSTOMER              | ALAMAT_CUSTOMER | KOTA_CUSTOMER | TELEPON_CUSTOMER |
+---------------+----------------------------+-----------------+---------------+------------------+
| J-0099        | Customer - After Migration | JL CIMANGGIS 34 | JAKARTA       | (021) 856-4209   |
+---------------+----------------------------+-----------------+---------------+------------------+
1 row in set (0.00 sec)
MariaDB [teguhth]> select * from pasok where KODE_PASOK ='PAS-E099';
Empty set (0.00 sec)
MariaDB [teguhth]> select * from pembelian where KODE_PEMBELIAN ='BEL-E099';
Empty set (0.00 sec)
MariaDB [teguhth]>
MariaDB [teguhth]> delete from barang where kode_barang = 'ELK-99';
Query OK, 1 row affected (0.05 sec)
MariaDB [teguhth]> delete from suplier where kode_suplier = 'EJ-99';
Query OK, 1 row affected (0.10 sec)
MariaDB [teguhth]> delete from customer where kode_customer = 'J-0099';
Query OK, 1 row affected (0.09 sec)
MariaDB [teguhth]> delete from pasok where kode_pasok = 'PAS-E099';
Query OK, 0 rows affected (0.00 sec)
MariaDB [teguhth]> delete from pembelian where kode_pembelian = 'BEL-E099';
Query OK, 0 rows affected (0.00 sec)
MariaDB [teguhth]>
MariaDB [teguhth]> select * from barang where KODE_BARANG ='ELK-99';
Empty set (0.00 sec)
MariaDB [teguhth]> select * from suplier where KODE_SUPLIER ='EJ-99';
Empty set (0.00 sec)
MariaDB [teguhth]> select * from customer where KODE_CUSTOMER ='J-0099';
Empty set (0.00 sec)
MariaDB [teguhth]> select * from pasok where KODE_PASOK ='PAS-E099';
Empty set (0.00 sec)
MariaDB [teguhth]> select * from pembelian where KODE_PEMBELIAN ='BEL-E099';
Empty set (0.00 sec)
MariaDB [teguhth]>