.::: Migration MySQL/MariaDB to Microsoft SQL Server (MSSQL) :::.

Pre Migration

1. Download & Install SQL Server Migration Assistant for MySQL 

https://docs.microsoft.com/id-id/sql/ssma/sql-server-migration-assistant?view=sql-server-2017

2. Download Driver Connector ODCB ( version 5.2 - 5.3.3) & Install 

https://dev.mysql.com/downloads/connector/odbc/

3. Optional Backup Database MySQL/MariaDB

4. Capture database & table MySQL/MariaDB (sample existing (Source) dbname = teguhth). select data type mysql

select * from INFORMATION_SCHEMA.TABLES where table_schema = 'teguhth'

5. Create new destination database SQL Server (sample new(destination) dbname = ls)

select * from INFORMATION_SCHEMA.TABLES

Activity Migration 

1. Open SQL Server Migration Assistant for MySQL & create new project

2. fille form & Ok 

  Name : SqlMigration-teguhthMySQL-to-lsMSSSQL

   Migration to : SQL Server 2012 (choose for version MSSQL)   

3. On SQL Server Migration Assistant for MySQL, press button "Connect to MySQL" an fill form & connect 

  Mode : Standard mode
  Drive : MySQK ODCB 5.3
  Server Source: localhost
  Server Port: 3306
  User : root
  password: <xxxx>

4. Filter objects and ok

5. on MySQL Metadata Explorer, select tables from database that will be migration

6. On SQL Server Migration Assistant for MySQL, press button "Connect to SQL Server" an fill form & connect

   Server Name : localhost
   Database destination : ls   
   Authentication  : SQL Server Authentication
   User Name : sa
   password: <xxxx>

   

7. on MySQL Metadata Explorer, select tables from database that will be migration, press button Convert Schema

 

 

8. on SQL Server Metadata Explorer, select tables from database that will be Migrated (restore) and syncronize with Database 

Connection to SQL Server established successfully.
Connection string: Data Source=127.0.0.1;Integrated Security=False;User ID=sa;Pooling=False;Encrypt=False;Application Name="SSMA for MySql"
Synchronizing objects with database...
Analyzing metadata...
Preparing category Tables of schema ls.teguhth...
Preparing table ls.teguhth.barang...
Preparing category Triggers of table ls.teguhth.barang...
Preparing category Indexes of table ls.teguhth.barang...
Preparing table ls.teguhth.customer...
Preparing category Triggers of table ls.teguhth.customer...
Preparing category Indexes of table ls.teguhth.customer...
Preparing table ls.teguhth.pasok...
Preparing category Triggers of table ls.teguhth.pasok...
Preparing category Indexes of table ls.teguhth.pasok...
Preparing index ls.teguhth.pasok.KODE_BARANG...
Preparing index ls.teguhth.pasok.KODE_SUPLIER...
Preparing table ls.teguhth.pembelian...
Preparing category Triggers of table ls.teguhth.pembelian...
Preparing category Indexes of table ls.teguhth.pembelian...
Preparing index ls.teguhth.pembelian.KODE_BARANG...
Preparing index ls.teguhth.pembelian.KODE_CUSTOMER...
Preparing table ls.teguhth.suplier...
Preparing category Triggers of table ls.teguhth.suplier...
Preparing category Indexes of table ls.teguhth.suplier...
Starting Phase #0
Synchronizing ls.teguhth ...
Loading to database new table ls.teguhth.barang ...
Loading to database new table ls.teguhth.customer ...
Loading to database new table ls.teguhth.pasok ...
Loading to database new table ls.teguhth.pembelian ...
Loading to database new table ls.teguhth.suplier ...
Loading to database new table ls.teguhth.barang primary keys ...
Loading to database new table ls.teguhth.customer primary keys ...
Loading to database new table ls.teguhth.pasok primary keys ...
Loading to database new table ls.teguhth.pembelian primary keys ...
Loading to database new table ls.teguhth.suplier primary keys ...
Loading to database index ls.teguhth.pasok.KODE_BARANG ...
Loading to database index ls.teguhth.pembelian.KODE_BARANG ...
Loading to database index ls.teguhth.pembelian.KODE_CUSTOMER ...
Loading to database index ls.teguhth.pasok.KODE_SUPLIER ...
Loading to database new table ls.teguhth.pasok foreign keys ...
Loading to database new table ls.teguhth.pembelian foreign keys ...
Loading to database new table ls.teguhth.barang columns default ...
Loading to database new table ls.teguhth.customer columns default ...
Loading to database new table ls.teguhth.pasok columns default ...
Loading to database new table ls.teguhth.pembelian columns default ...
Loading to database new table ls.teguhth.suplier columns default ...
Synchronization operation is complete.

9. on MySQL Metadata Explorer, select tables from database that will be migration, press button Migration Data and connect 

   Fill for MySQL/MariaDb  
   Mode : Standard mode
   Drive : MySQK ODCB 5.3
   Server Source: localhost
   Server Port: 3306
   User : root
   password: <xxxx>

   Fill for SQL Server 
   Server Name : localhost
   Database destination : ls   
   Authentication  : SQL Server Authentication
   User Name : sa
   password: <xxxx>

10. Waiting until success

Migrating data...
Analyzing metadata...
Preparing table teguhth.barang...
Preparing table teguhth.customer...
Preparing table teguhth.pasok...
Preparing table teguhth.pembelian...
Preparing table teguhth.suplier...
Preparing data migration package...
Starting data migration Engine
Starting data migration...
The data migration engine is migrating table '`teguhth`.`barang`': > [ls].[teguhth].[barang], 6 rows total
The data migration engine is migrating table '`teguhth`.`customer`': > [ls].[teguhth].[customer], 6 rows total
The data migration engine is migrating table '`teguhth`.`pasok`': > [ls].[teguhth].[pasok], 12 rows total
The data migration engine is migrating table '`teguhth`.`pembelian`': > [ls].[teguhth].[pembelian], 15 rows total
The data migration engine is migrating table '`teguhth`.`suplier`': > [ls].[teguhth].[suplier], 6 rows total
Table `teguhth`.`pasok` data migration: 12 rows processed.
Completing migration of table `teguhth`.`pasok`...
Table `teguhth`.`pembelian` data migration: 15 rows processed.
Completing migration of table `teguhth`.`pembelian`...
Table `teguhth`.`barang` data migration: 6 rows processed.
Completing migration of table `teguhth`.`barang`...
Table `teguhth`.`suplier` data migration: 6 rows processed.
Completing migration of table `teguhth`.`suplier`...
Migration complete for table '`teguhth`.`pasok`': > [ls].[teguhth].[pasok], 12 rows migrated (Elapsed Time = 00:00:00:10:662).
Migration complete for table '`teguhth`.`pembelian`': > [ls].[teguhth].[pembelian], 15 rows migrated (Elapsed Time = 00:00:00:10:646).
Migration complete for table '`teguhth`.`barang`': > [ls].[teguhth].[barang], 6 rows migrated (Elapsed Time = 00:00:00:10:703).
Migration complete for table '`teguhth`.`suplier`': > [ls].[teguhth].[suplier], 6 rows migrated (Elapsed Time = 00:00:00:10:613).
Table `teguhth`.`customer` data migration: 6 rows processed.
Completing migration of table `teguhth`.`customer`...
Migration complete for table '`teguhth`.`customer`': > [ls].[teguhth].[customer], 6 rows migrated (Elapsed Time = 00:00:00:10:675).
Data migration operation has finished.
5 table(s) successfully migrated.
0 table(s) partially migrated.
0 table(s) failed to migrate.

Post Migration 

1. Open Microsoft SQL Studio & connect Database SQL Server

2. Open MySQL Workbench & connect MariaDB 

3. Test Insert table to MSSQL

select * from teguhth.barang where KODE_BARANG ='ELK-99';

select * from teguhth.suplier where KODE_SUPLIER ='EJ-99';

select * from teguhth.customer where KODE_CUSTOMER ='J-0099';

select * from teguhth.pasok where KODE_PASOK ='PAS-E099';

select * from teguhth.pembelian where KODE_PEMBELIAN ='BEL-E099';

insert into teguhth.barang(KODE_BARANG,NAMA_BARANG,SATUAN_BARANG,STOK_BARANG) values('ELK-99','Barang - After Migration','BUAH',20);

insert into teguhth.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 teguhth.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 teguhth.pasok(KODE_PASOK,KODE_BARANG,KODE_SUPLIER,TANGGAL_PASOK,JUMLAH_PASOK) values('PAS-E099','ELK-99','EJ-99','1-Jan-02',8);

insert into teguhth.pembelian(KODE_PEMBELIAN,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E099','ELK-99','J-0099','20-May-02',3);

select * from teguhth.barang where KODE_BARANG ='ELK-99';

select * from teguhth.suplier where KODE_SUPLIER ='EJ-99';

select * from teguhth.customer where KODE_CUSTOMER ='J-0099';

select * from teguhth.pasok where KODE_PASOK ='PAS-E099';

select * from teguhth.pembelian where KODE_PEMBELIAN ='BEL-E099';

delete from teguhth.barang where kode_barang = 'ELK-99';

delete from teguhth.suplier where kode_suplier = 'EJ-99';

delete from teguhth.customer where kode_customer = 'J-0099';

delete from teguhth.pasok where kode_pasok = 'PAS-E099';

delete from teguhth.pembelian where kode_pembelian = 'BEL-E099';

select * from teguhth.barang where KODE_BARANG ='ELK-99';

select * from teguhth.suplier where KODE_SUPLIER ='EJ-99';

select * from teguhth.customer where KODE_CUSTOMER ='J-0099';

select * from teguhth.pasok where KODE_PASOK ='PAS-E099';

select * from teguhth.pembelian where KODE_PEMBELIAN ='BEL-E099';