[HANA] Table Replication

- 출처 : https://help.sap.com/viewer/6b94445c94ae495c83a19646e7c3fd56/2.0.04/en-US/59244f4e0c8d437db363e37dfeacac68.html

SAP Help Portal

 

Table replication

In a scale-out system tables (or selected columns of column store tables) may be replicated to multiple hosts. 

 

Sysnchronous and Asynchronous Replication

장단점 비교

Optimistic Synchronous Table Replication (OSTR)Asynchronous Table Replication (ATR)

Optimistic Synchronous Table Replication (OSTR)	Asynchronous Table Replication (ATR)
Pro: Symmetric and thus easy to use.	Pro: Little overhead at the source node. Replicating updates with less overhead at the source transactions.
Con: There is a performance penalty but only to the write transactions commit operations (DML and read transactions are not affected).	Cons: not easy to use due to asymmetry between source and replica Replicas have different (possibly outdated) state than their source tables. This incurs difficulty in its usage model. That is, the source and its replica are not symmetric or equivalent to each other and the application developers should explicitly hint which queries are fine with such staleness.

 

 

Sub-Table Replication 

선택된 특정 Column들 만으로 replication 적용 가능 (Column store table 대상)

ALTER SYSTEM ALTER CONFIGURATION ('indexserver.ini', 'system') SET ('table_replication', 'is_column_wise_replication_enabled') = 'true' WITH RECONFIGURE

 

 

Monitoring Views

M_TABLE_REPLICAS

 

 

---

 

Table replication Examples

Examples of Locating Replica Tables

 

Creating Tables with Replicas on All Hosts

SQL Command	Results
CREATE COLUMN TABLE MY_TABLE (I INT PRIMARY KEY) REPLICA AT ALL LOCATIONS	Creates a column store table with a replica on each available host.
ALTER TABLE MY_TABLE ADD REPLICA AT ALL LOCATIONS	Replicates an existing table on each available host
ALTER TABLE MY_TABLE2 DROP REPLICA AT ALL LOCATIONS	Drops all replicas

 

Creating Tables with Replicas on Specific Hosts

SQL Command	Result
CREATE ROW TABLE MY_TABLE5 (I INT PRIMARY KEY) AT LOCATION '<master_node>;	Creates a new row store table with a replica on a specified host (for example, master)
ALTER TABLE MY_TABLE5 ADD REPLICA AT LOCATION '<first_slave_node>';	Replicates an existing row store table at the specified location
ALTER TABLE MY_TABLE5 MOVE REPLICA FROM '<first_slave_node>' TO '<second_slave_node>';	Moves an existing replica from one specified location to another
ALTER TABLE MY_TABLE3 DROP REPLICA AT LOCATION '<second_slave_node>';	Drops the replica from the specified host

 

Sub-Table Replication

SQL Command	Result
ALTER TABLE SRC.TBL ADD SYNCHRONOUS REPLICA (B, C, E) AT '<host:port>';	Create a replica from an existing table keeping the name of the source table; this example creates a replica containing only columns B, C, E.
CREATE COLUMN TABLE REP.TBL LIKE SRC.TBL SYNCHRONOUS REPLICA (A, C, D) AT '<host:port>';	Create a replica with a new name (REP.TBL) from an existing table; this example creates a replica with columns A, C, D.
CREATE COLUMN TABLE SRC.TBL (A INT, B INT, C NVARCHAR(10)) SYNCHRONOUS REPLICA (A, B) AT '<host:port>';	Create a source and replica at the same time, in this case the data types are also required.
ALTER TABLE REP.TBL MOVE TO '<host:port>';	Move an explicitly named replica sub-table.
ALTER TABLE REP._SYS_REP_TBL#1 MOVE TO '<host:port>';	Move an explicitly named replica sub-table.
DROP TABLE REP.TBL;	Drop an explicitly named replica sub-table.

 

Partitioned Tables

SQL Command	Result
CREATE COLUMN TABLE SRC_TABLE (C1, C2, C3, … CN, primary key (C1, C2, C3)) PARTITION BY HASH (C1) PARTITIONS 32;	Create a source table (SRC_TABLE) with a number of columns. The table has 32 hash-partioned partitions based on the values of column 1 (C1).
CREATE COLUMN TABLE REP_TABLE1 LIKE SRC_TABLE REPLICA PARTITION BY HASH (C2) PARTITIONS 8;	Create a replica table (REP_TABLE1) which has an asymmetric partitioning scheme compared to its source table. All columns of the source table are replicated but the replica is partitioned on column 2 (C2) to create 8 partitions.
CREATE COLUMN TABLE REP_TABLE2 LIKE SRC_TABLE REPLICA (C1, C3) PARTITION BY RANGE (C3) (PARTITION '1' <= VALUES < '100’, PARTITION OTHERS);	Create another replica table (REP_TABLE2) which also has an asymmetric partitioning scheme compared to its source table. Only columns 1 and 3 of the source table are replicated and the replica is partitioned on column 3 (C3) by ranges (specifically one partition with all values between 1 and 100, and a partition for all other values.

 

 

 

Limitation

- HANA Scale-out landscape 내에서 존재함

- 다음 Table type은 설정 불가

   . History table
   . Flexible table
   . Temporary table
   . Proxy table
   . Extended Store table
   . Multistore table
   . System-versioned table
   . Tables with masked columns, tables with associations, tables with series data.

- Source table과 replica는 동일한 Node에 존재 불가

- 하나의 replica table은 하나의 replica Node에 존재

- Replica table에서는 write operation 불가 / DDL 불가

- DDL operations on a replication source table cannot be executed with a 'DDL auto-commit off' transaction. 

- Replicated table에 binary import 불가 / Export도 불가

-