[MariaDB] MSSQL DBLINK ( Using Connection Engine )

오늘은 MariaDB에서 MSSQL로의 DBLINK에 관하여 글을 쓸 예정이다. 최근 고객사에서 MariaDB에서 MSSQL로 DBLINK를 맺어달라 문의를 받아서 해보게 되었다. 

MariaDB의 Connect Engine을 이용해서 DBLINK을 맺을 예정이다.

 

0. Connect Engine?

- Connect Storage Engine은 MariaDB 10.2 부터 반영되었다.

- MariaDB에서 외부 로컬 또는 원격 데이터를 액세스할 수 있게 해주는 Storage Engine이다.

- ODBC,JDBC를 통해 다른 DBMS 또는 제품(Excel)에서 추출한 데이터를 액세스 할 수 있다. 

- Connect Stroage Engine은 테이블 파티셔닝, MariaDB 가상 칼럼을 지원하며 ROWID, FILEID 및 SERVID와 같은 특수 칼럼을 정의할 수 있다.

 

1. MariaDB 서버 MSSQL ODBC INSTALL

 

HOSTNAME	PUBLIC IP	DATABASE VERSION
sql-server	152.70.39.47	2019 EE
MariaDB	193.123.238.187	10.3.7-MariaDB

** 1.REPO 설치 **
[root@shard2 ~]#  curl https://packages.microsoft.com/config/rhel/7/prod.repo > /etc/yum.repos.d/mssql-release.repo
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100   193  100   193    0     0    278      0 --:--:-- --:--:-- --:--:--   278

** 2.ODBC 충돌을 막기위해 **
[root@shard2 ~]# sudo yum remove unixODBC-utf16 unixODBC-utf16-devel
Loaded plugins: fastestmirror, langpacks
No Match for argument: unixODBC-utf16
No Match for argument: unixODBC-utf16-devel
No Packages marked for removal

** 3.ODBC 설치 **
[root@shard2 ~]# sudo ACCEPT_EULA=Y yum -y install msodbcsql17 mssql-tools

[root@shard2 ~]# cat /etc/odbcinst.ini
[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.8.so.1.1
UsageCount=1

** 4.ODBC.INI 수정 **
[root@shard2 ~]# vi /etc/odbc.ini 
[connect]
Driver=ODBC Driver 17 for SQL Server
Description=MSSQL Server
Trace=No
Server=152.70.39.47
Database=LEE
Port=1433

** 5.ODBC Connect TEST **
[root@shard2 ~]# isql connect sa test
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>

SQL> select * from LEE.dbo.test;
+------------+---------------------+
| a          | b                   |
+------------+---------------------+
| 1          | aSd                 |
| 1          | aSd                 |
| 1          | aSd                 |
| 1          | aSd                 |
| 1          | aSd                 |
| 1          | aSd                 |
| 1          | aSd                 |
| 1          | aSd                 |
| 1          | aSd                 |
| 1          | aSd                 |
| 1          | aSd                 |
+------------+---------------------+

 

2. MariaDB Connect Engine 설치

- Connect Storage Engine이 있는지 확인해보고 Connect Storage Engine 플러그인을 설치하였다.  

** 0.설치되있는 Engine 확인 **
MariaDB [(none)]> show engines;
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                                          | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| CSV                | YES     | CSV storage engine                                                               | NO           | NO   | NO         |
| MRG_MyISAM         | YES     | Collection of identical MyISAM tables                                            | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables                        | NO           | NO   | NO         |
| Aria               | YES     | Crash-safe tables with MyISAM heritage                                           | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                                            | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                                               | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, foreign keys and encryption for tables | YES          | YES  | YES        |
| SEQUENCE           | YES     | Generated tables filled with sequential values                                   | YES          | NO   | YES        |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
8 rows in set (0.000 sec)

** 1.Connect Engine 설치 **
[root@shard2 ~]# ln -s /usr/lib64/libodbc.so /usr/lib64/libodbc.so.1

MariaDB [(none)]> install soname 'ha_connect';
Query OK, 0 rows affected (0.012 sec)

MariaDB [(none)]> show engines;
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                                          | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| CSV                | YES     | CSV storage engine                                                               | NO           | NO   | NO         |
| MRG_MyISAM         | YES     | Collection of identical MyISAM tables                                            | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables                        | NO           | NO   | NO         |
| Aria               | YES     | Crash-safe tables with MyISAM heritage                                           | NO           | NO   | NO         |
| CONNECT            | YES     | Management of External Data (SQL/NOSQL/MED), including many file formats         | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                                            | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                                               | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, foreign keys and encryption for tables | YES          | YES  | YES        |
| SEQUENCE           | YES     | Generated tables filled with sequential values                                   | YES          | NO   | YES        |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+

 

3. DBLINK TABLE 생성

** 1.MSSQL TABLE 확인 ( MSSQL Server에서 실행 ) **
use lee;
select * from INFORMATION_SCHEMA.tables;

** 2.DBLINK TABLE 생성 **
MariaDB [(none)]> create database dblink;
Query OK, 1 row affected (0.001 sec)

MariaDB [(none)]> use dblink;
Database changed

MariaDB [dblink]> create table ms_test engine=connect table_type=odbc tabname='dbo.test' connection='DSN=connect;UID=sa;PWD=test' dbname=LEE;
Query OK, 0 rows affected (0.024 sec)

MariaDB [dblink]> select * from ms_test;
+------+------+
| a    | b    |
+------+------+
|    1 | aSd  |
|    1 | aSd  |
|    1 | aSd  |
|    1 | aSd  |
|    1 | aSd  |
|    1 | aSd  |
|    1 | aSd  |
|    1 | aSd  |
|    1 | aSd  |
|    1 | aSd  |
|    1 | aSd  |
+------+------+
11 rows in set (0.008 sec)

- create table [table명] engine=connect table_type=odbc tabname='[mssql table명]' connection='DSN=connect;UID=[MSSQL USER];pwd=[MSSQL PW]' dbame=[MSSQL DB명];  

이런 식으로 MySQL DBLINK TABLE을 구성할 수 있다.

 

*TIP : 위에 create table을 하나하나 적어주기에는 너무 많은 시간이 걸린다. MSSQL에서 dynamic query를 이용해서 SQL문을 만들어주면 보다 편하게 DBLINK TABLE을 생성할 수 있다. *

select 'create table ' + table_name + ' engine=connect table_type=odbc  tabname='''+table_schema+'.'+table_name+'''  connection=''DSN=connect;UID=farmedu_mig;PWD=nhit1234'' dbname='+TABLE_CATALOG+';'
from INFORMATION_SCHEMA.TABLES;