.::: Create a link or dblink to a remote server MariaDD MySQL to MSSQL SQL Server using ha_connect :::.

 A. Configure dblink MariaDB
1. install plugin ha_connect
INSTALL SONAME 'ha_connect';

2. install driver Driver=SQL Server Native Client 11.0 or mssqlodbc

SQL Server Native Client 11.0
https://www.microsoft.com/en-us/download/details.aspx?id=50402

or
mssqlodbc
https://www.microsoft.com/en-us/download/details.aspx?id=36434

3. create link server/dblink to connect mssql
CREATE SERVER dblink_mssql
                FOREIGN DATA WRAPPER `mssql`
                OPTIONS (
                USER 'sa',
                PASSWORD 'P@ssw0rd',
                HOST '10.10.10.7',
                port 1433,
                DATABASE 'teguhth' );

 
4. create table link for mariadb

CREATE TABLE dblink.barang_local ENGINE = CONNECT TABLE_TYPE=ODBC TABNAME='barang' CONNECTION='Driver=SQL Server Native Client 11.0;Server=10.10.10.7;Database=teguhth;UID=sa;PWD=passworddb';

5. result from create table barang_local
MariaDB [dblink]> show create table dblink.barang_local;
+--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table        | Create Table                                                                                                                                                                                                                                                                                                                                                                                                        |
+--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| barang_local | CREATE TABLE `barang_local` (
  `KODE_BARANG` char(6) NOT NULL,
  `NAMA_BARANG` varchar(25) DEFAULT NULL,
  `SATUAN_BARANG` varchar(20) DEFAULT NULL,
  `STOK_BARANG` decimal(6,0) DEFAULT NULL
) ENGINE=CONNECT DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci CONNECTION='Driver=SQL Server Native Client 11.0;Server=10.10.10.7;Database=teguhth;UID=sa;PWD=passworddb' `TABLE_TYPE`='ODBC' `TABNAME`='barang' |
+--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.009 sec)

MariaDB [dblink]>

6. check configuration and test select contain table 

 

B. Test Insert data in MSSQL

1. insert row in mssql
select @@servername,@@version
select *,'before insert row' from teguhth.dbo.barang
insert into barang(KODE_BARANG,NAMA_BARANG,SATUAN_BARANG,STOK_BARANG) values('ELK-09','KIPAS ANGIN','BUAH',38);
insert into barang(KODE_BARANG,NAMA_BARANG,SATUAN_BARANG,STOK_BARANG) values('ELK-10','KIPAS ANGIN','BUAH',38);
select *,'after insert row' from teguhth.dbo.barang
 

2. check row in mariadb
select @@hostname,@@version;
select *,'before insert row' from dblink.barang_local;
select *,'after insert row' from dblink.barang_local;