문서유형ㅣ기술정보
분야ㅣ마이그레이션
적용제품버전ㅣTibero 7.2.4
문서번호ㅣTMITI028
개요
MS SQL server를 TableMigrator를 이용하여 이관하는 방법에 대해 기술하였습니다.
테스트 구성 환경
| 서버명 | OS version | DB Version | DB Name | Port | Username | IP |
|---|---|---|---|---|---|---|
| Tibero | CentOS 8.4.2105 | Tibero 7.2.4 | tibero | 8629 | sys | 192.168.41.110 |
| MS SQL Server | Ubuntu 9.4.0 | MS SQL Server 2022 | test | 6133 | test | 192.168.41.182 |
방법
1. MS SQL Server 설정
이관 대상 유저 생성 및 테이블 생성
MS SQL Server에 이관 대상 유저 및 테이블을 생성합니다.
$ sqlcmd -S localhost -U sa -P "Tibero123#" -C
1> CREATE DATABASE test;
2> go
1>
2> use test;
3> go
Changed database context to 'test'.
1>
2>
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'test')
EXEC('CREATE SCHEMA test AUTHORIZATION test');
3> go
1>
2> CREATE TABLE test.mssql_table (id INT IDENTITY(1,1), hire_date DATE);
3> go
1>
2> insert into test.mssql_table (hire_date) values ('2025-11-01'), ('2019-10-26'), ('2022-01-14');
3> go
(3 rows affected)
1>
2> select * from test.mssql_table;
3> go
id hire_date
----------- ----------------
1 2025-11-01
2 2019-10-26
3 2022-01-14
(3 rows affected)
2. Tibero 설정
이관 대상 유저 및 테이블 생성
먼저 MS SQL Server 이관 대상 테이블을 확인하고 해당 테이블과 호환성이 유지되도록 테이블을 생성합니다.
[tibero@kimmi ~]$ tbsql sys/tibero tbSQL 7 TmaxTibero Corporation Copyright (c) 2020-. All rights reserved. Connected to Tibero. SQL> create user mssql_test identified by mssql_test; User 'MSSQL_TEST' created. SQL> create table mssql_test.test (a number, hire_date date); Table 'MSSQL_TEST.TEST' created. SQL> grant dba to mssql_test; Granted.
3. TableMigrator 설정
TableMigrator 사용 전 Source DB, Target DB에 대한 접속 설정을 해주어야 합니다. TableMigrator 압축 해제 후, 설정 아래와 같이 설정합니다.
- TableMigrator 파일 위치 : $TB_HOME/client/bin/table_migrator.zip
- TableMigrator 설정 파일 샘플 : migrator.properties.eg
-- TableMigrator 위치 확인 $ cd $TB_HOME/client/bin $ ls -alrt table_migrator* -rw-r--r--. 1 tibero dba 6373537 10월 13 11:23 table_migrator.zip -- TableMigrator 수행 위치 변경 $ cp table_migrator.zip $HOME/table_migrator $ cd $HOME/table_migrator $ unzip table_migrator.zip -- 설정 파일 샘플 복사 $ cp migrator.properties.eg mig.pro
3.1 MS SQL Server JDBC 파일 다운로드 및 설정
MS SQL Server 버전에 맞는 jar 파일 다운로드 후 TableMigrator 폴더에 업로드 합니다.
$ cd $HOME/table_migrator $ ls -alrt mssql* -rw-r--r--. 1 tibero dba 1246658 11월 25 16:35 mssql-jdbc-13.2.1.jre8.jar -- 업로드한 jar 파일명 입력 $ vi migrator.sh java -classpath migrator_cli.jar:mssql-jdbc-13.2.1.jre8.jar:mariadb-java-client-3.5.6.jar:ojdbc8.jar:osdt_core.jar:osdt_cert.jar:oraclepki.jar:postgresql-42.2.27.jre6.jar:msllogger-18.jar:toolcom.jar:internal-jdbc-8.jar:antlr-4.7.2-complete.jar com.m.migrator.Main "$@"
3.2 Source DB 설정
| 속성명 | 설정값 | 비고 |
|---|---|---|
| SOURCE_TYPE | GENERIC | 고정값 |
| SOURCE_DRIVER | com.microsoft.sqlserver.jdbc.SQLServerDriver | 고정값 |
| SOURCE_URL | jdbc:sqlserver://192.168.41.182:1633;databaseName=test;encrypt=true;trustServerCertificate=true | IP, Service Port, DB명은 환경에 맞게 변경 |
| SOURCE_USER | test | 접속할 유저명 |
| SOURCE_PASSWORD | Test123# | 접속할 유저의 패스워드 |
$ vi mig.pro ####################### # Source DB Connection ####################### SOURCE_TYPE=GENERIC SOURCE_DRIVER=com.microsoft.sqlserver.jdbc.SQLServerDriver SOURCE_URL=jdbc:sqlserver://192.168.41.182:1633;databaseName=test;encrypt=true;trustServerCertificate=true SOURCE_USER=test SOURCE_PASSWORD=Tibero123# SOURCE_SCHEMA=test
3.3 Target DB 설정
| 속성명 | 설정값 | 비고 |
|---|---|---|
| TARGET_TYPE | DEFAULT | 고정값 |
| TARGET_DRIVER | com.m.internal.jdbc.MInternalDriver | 고정값 |
| TARGET_URL | jdbc:internal:thin:@192.168.41.110:8629:tibero0 | IP, Service Port, DB명은 환경에 맞게 변경 |
| TARGET_USER | sys | 접속할 유저명 |
| TARGET_PASSWORD | tibero | 접속할 유저의 패스워드 |
vi mig.pro ####################### # Target DB Connection ####################### TARGET_TYPE=DEFAULT TARGET_DRIVER=com.m.internal.jdbc.MInternalDriver TARGET_URL=jdbc:internal:thin:@192.168.41.110:8629:tibero0 TARGET_USER=sys TARGET_PASSWORD=tibero TARGET_SCHEMA=mssql_test #TARGET_TABLE=mssql_test.mssql_table INSERT_BATCH=Y INSERT_THREAD_COUNT=1 EXTRACT_PARALLEL=N SELECT_FETCH_SIZE=1024 BATCH_THRESHOLD=128 LOG_FILE_NAME=tableMigrator.log LOG_LEVEL=INFO CHECK_COLUMN_METADATA=N SELECT_AS_BYTE=N INSERT_METHOD=DPL INSERT_COLUMN_NAME_CASE_SENSITIVE=Y
4. TableMigrator 수행
TableMigrator 수행 시 이관할 <테이블명>.<유저명> 입력하여 데이터 이관을 시작합니다.
-- 수행 명령어 [tibero@kimmi table_migrator]$ sh migrator.sh PROPERTY_FILE=mig.pro SOURCE_TABLE=test.mssql_table TARGET_TABLE=test.mssql_table ## [WARNING] Ignored parameters: INSERT_THREAD_COUNT === Parameters === PROPERTY_FILE=mig.pro SOURCE_TYPE=GENERIC SOURCE_DRIVER=com.microsoft.sqlserver.jdbc.SQLServerDriver SOURCE_URL=jdbc:sqlserver://192.168.41.182:1633;databaseName=test;encrypt=true;trustServerCertificate=true SOURCE_USER=test SOURCE_PASSWORD=************ SOURCE_SCHEMA=test SOURCE_SCHEMAORIGN=test SOURCE_TABLE=test.mssql_table SELECT_SUBPARTITION= BATCH_THRESHOLD=128 EXTRACT_PARALLEL=N TARGET_TYPE=DEFAULT TARGET_DRIVER=com.m.internal.jdbc.MInternalDriver TARGET_URL=jdbc:internal:thin:@192.168.41.110:8629:tibero0 TARGET_USER=sys TARGET_PASSWORD=************ TARGET_SCHEMA=mssql_test TARGET_TABLE=test.mssql_table SELECT_FETCH_SIZE=1024 SELECT_AS_BYTE=N INSERT_METHOD=DPL INSERT_BATCH=Y INSERT_PARALLEL=N INSERT_THREAD_COUNT=1 INSERT_COLUMN_NAME_CASE_SENSITIVE=Y INSERT_ZERO_LENGTH_STRING_AS_NULL=Y LOG_FILE_NAME=tableMigrator.log LOG_LEVEL=INFO CHECK_COLUMN_METADATA=N ================== 17:50:30.759 [main] INFO - ******************************************************************************** * * Start Log for table migrator. * Vendor: tibero * Product: table migrator * Version: 303667 * * Trace is started on 17:50:30.757 * Log level: INFO * ******************************************************************************** 17:50:30.761 [main] INFO - PROPERTY_FILE=mig.pro SOURCE_TYPE=GENERIC SOURCE_DRIVER=com.microsoft.sqlserver.jdbc.SQLServerDriver SOURCE_URL=jdbc:sqlserver://192.168.41.182:1633;databaseName=test;encrypt=true;trustServerCertificate=true SOURCE_USER=test SOURCE_PASSWORD=************ SOURCE_SCHEMA=test SOURCE_SCHEMAORIGN=test SOURCE_TABLE=test.mssql_table SELECT_SUBPARTITION= BATCH_THRESHOLD=128 EXTRACT_PARALLEL=N TARGET_TYPE=DEFAULT TARGET_DRIVER=com.m.internal.jdbc.MInternalDriver TARGET_URL=jdbc:internal:thin:@192.168.41.110:8629:tibero0 TARGET_USER=sys TARGET_PASSWORD=************ TARGET_SCHEMA=mssql_test TARGET_TABLE=test.mssql_table SELECT_FETCH_SIZE=1024 SELECT_AS_BYTE=N INSERT_METHOD=DPL INSERT_BATCH=Y INSERT_PARALLEL=N INSERT_THREAD_COUNT=1 INSERT_COLUMN_NAME_CASE_SENSITIVE=Y INSERT_ZERO_LENGTH_STRING_AS_NULL=Y LOG_FILE_NAME=tableMigrator.log LOG_LEVEL=INFO CHECK_COLUMN_METADATA=N 17:50:30.843 [main] INFO - connect to the source - url : jdbc:sqlserver://192.168.41.182:1633;databaseName=test;encrypt=true;trustServerCertificate=true id : test 17:50:31.017 [main] INFO - connect to the target - url : jdbc:internal:thin:@192.168.41.110:8629:tibero0 id : sys 17:50:31.070 [main] INFO - connect to the target - url : jdbc:internal:thin:@192.168.41.110:8629:tibero0 id : sys SOUCE_SCEMA : test SOUCE_TABLE : mssql_table TARGET_SCEMA : test TARGET_TABLE : mssql_table 17:50:31.105 [main] INFO - extract thread count : 1 17:50:31.105 [main] INFO - start extracting thread... 17:50:31.106 [main] INFO - start loading thread... [E0]1764060631106 - STARTED [L0]1764060631137 Loader started [E0]1764060631143 - TOTAL Extracted ROWS: 3 (source schema = test, source table = mssql_table) 17:50:31.137 [L0] INFO - [L0]1764060631137 Loader started 17:50:31.144 [L0] INFO - DPL Loading Information 17:50:31.144 [L0] INFO - table name : test.mssql_table 17:50:31.144 [L0] INFO - columns [2] - "ID" "HIRE_DATE" [L0]1764060631169 TOTAL Loaded ROWS: 3 (target schema = test, target table = mssql_table) 17:50:31.170 [L0] INFO - [L0]1764060631170 TOTAL Loaded ROWS: 3 (target schema = test, target table = mssql_table) 17:50:31.170 [L0] INFO - Loader finally 17:50:31.170 [L0] INFO - Loader complete 17:50:31.177 [main] INFO - extract conneciton close 17:50:31.179 [main] INFO - loading data commit and connection close Loading is ended. 17:50:31.180 [main] INFO - Loading is ended. Elapsed Time (milliseconds) : 419 17:50:31.180 [main] INFO - Elapsed Time (milliseconds) : 419 ADD BATCH : 0 17:50:31.180 [main] INFO - ADD BATCH : 0 EXECUTE BATCH : 0 17:50:31.180 [main] INFO - EXECUTE BATCH : 0 [VERIFICATION] Source Info : test.mssql_table : 3 Rows, Target Info : test.mssql_table : 3 Rows [OK]
5. 데이터 확인
-- MS SQL Server
1> SELECT id, hire_date FROM test.mssql_table;
2> go
id hire_date
----------- ----------------
1 2025-11-01
2 2019-10-26
3 2022-01-14
(3 rows affected)
-- Tibero
SQL> select * from test.mssql_table;
id hire_date
----------- ----------------
1 2025-11-01
2 2019-10-26
3 2022-01-14
3 rows selected.