TimesTen Database Replication Learning: 10. Defining Classic Copy

Designing Highly available systems

The target of replication is:
1. Provide one or more replication databases to ensure that the data can be used by the application
2. Provide a replication database for recovery
3. Load Balancing
4. Non-disruptive software upgrades and maintenance

Classic replication Scheme supports the following forms:
Unidirectional-Is this and the ASP different and will switch roles?
Bidirectional split Workload-bidirectional, is an interoperable, two databases responsible for different workloads
Bidirectional distributed workload-two-way, double-live
Propagation-Data distribution type

As a highly available system, the following aspects need to be considered

2. Considering failover and recovery scenarios
   When the master database fails, it is necessary to automatically redirect the app to an available database, usually through clustering software or customer application logic. These are in chapter, "Managing Database Failover and Recovery". discussed in
   A bidirectional scheme that replicates the entire database can take advantage of automatic restoration of a failed master. What is the remark:
   For one-way replication, if master fails, or waits for master to recover from Subscriber (the app does not need redirect but has an interrupt), or use the ALTER Replication defines subscriber as master (application requires redirect, less interrupts).
   The advantage of two-way replication is that when a master fails, for split workload, the app simply redirects (can use automatic client failover) and, for distributed workload, no redirection, But you need to deal with data conflicts. The new master can be set with ALTER replication during

4. Making decisions about performance and recovery tradeoffs
   You should try to simplify the process of error switching and recovery, which is related to the replication topology
   Like ASP, classic replication also supports asynchronous, semi-synchronous, and full-synchronous replication. Full synchronization has the greatest impact on performance
   Return receipt can used in more configurations, whereas return Twosafe can is only used in a bidirectional configuratio N or an active standby pair

6. Distributing workloads
   Configure bidirectional replication, which is typically used to read more applications. If you write more, you need to consider performance impact and prevent update conflicts

Defining classic Replication Scheme

Classic and ASP scheme can not coexist, if the ASP has been defined, then create the classic copy times wrong:

cachedb1> CREATE REPLICATION repscheme  ELEMENT e TABLE a3                                                              ON"timesten-hol"                                               ON"timesten-hol"                                           RETURN8126andfor the store.

A copy scheme contains multiple element, which defines the copied object, each element has datastore, TABLE, or sequence three types
A replicated table can only be in one element, and only one role can be specified
ALTER replication can subsequently add duplicate objects in scheme.
Note that ALTER replication is only for classic copy.

Copy the entire library, defining the DATASTORE element, as
ELEMENT DS1 DATASTORE
MASTER masterds on "System1"
Subscriber subscriberds on "SYSTEM2"
You can copy only some objects in datastore or specify that some objects are not copied, such as
EXCLUDE TABLE Ttuser.tab1, TTUSER.TAB2
EXCLUDE SEQUENCE ttuser.seq1
Or
INCLUDE TABLE TTUSER.TAB3
INCLUDE SEQUENCE TTUSER.SEQ2, TTUSER.SEQ3

Copy a table, defining table elements, such as:
ELEMENT a TABLE ttuser.tab1
MASTER masterds on "System1"
Subscriber subscriberds on "SYSTEM2"
ELEMENT b TABLE ttuser.tab2
MASTER masterds on "System1"
Subscriber subscriberds on "SYSTEM2"

You can also copy a set of tables that are associated with a foreign key and a primary key

can copy sequence

Copying materialized view is not directly supported, but can be indirectly supported by duplicating the fact table associated with the materialized view

Requirements for tables in classic copy

The requirements for ASP or cache groups are the same:
* A PRIMARY KEY or
* A unique index over non-nullable columns
In addition, tables with compressed columns cannot be copied.
VARCHAR2, NVARCHAR2, VARBINARY and Tt_varchar must be less than 4M
BLOB must be less than 16M

Limitations of multi-master replication

Multi-master replication is most typically a two-way replication, not limited to only two of the master.

Check table for replication conflicts

In bidirectional replication, conflict detection can be detected by:
Such conflicts can is detected and resolved on a table-by-table basis by including timestamps in the replicated tables and Configuring the replication scheme with the optional CHECK CONFLICTS clause in each table ' s element description.

Set the transmit of the datastore element durability

A master database configured for asynchronous or return receipt replication are durable by default. This means, that log records is committed to disk when transactions is committed. The master database can be set to nondurable by including the transmit nondurable clause in the element description.

Transaction Records in the master database log buffer is, by default, flushed to disk before they is forwarded to SUBSCR Ibers. If the entire master database is replicated (ELEMENT was of type DATASTORE), you can improve replication performance by Eli Minating the master ' s Flush-log-to-disk operation from the replication cycle. This is do by including a transmit nondurable clause in the element description. The transmit setting have no effect on the subscriber. The transaction records on the Subscriber database is always flushed to disk.

Master databases configured for return TWOSAFE replication is nondurable by default and cannot is made durable. Setting transmit durable on a database that's configured for return TWOSAFE replication have no effect on return Twosafe t Ransactions.

The above paragraph is more important, meaning that if it is a copy, regardless of whether the database is set to durable or non-durable, for asynchronous and return receipt replication, the disk will be written locally before copying. But not every commit will write a disk, but accumulate to a certain number or every 100ms write a disk

See: A description of how the Log Buffer flusher works (Doc ID 392247.1)

The log can also is flushed to disk by a variety of operations beyond an application issuing a Commit, such as a durable C Ommit of another transaction, switching to a new log file or checkpointing the database. Activity by the replication agent can also cause log flushing. With the default transmit durable replication option while 256K of transactions is packaged, a local D Urable commit is also performed. This can mean replication causes the log buffer to is flushed more frequently. Replication also does a durable commit every Ms. The reason for this durable commit are to minimize the windows for data Loss and the overhead for master catchup. The log buffer can also is flushed by a read-only transaction, if it calls Ttdurablecommit () (Note:a read-only Transactio n that sets durablecommit=1 in its connect string would not has this effect).

and return twosafe at this time transmit non-durable. Therefore, the return Two-safe performance is better than the return receipt.

Using the return service in classic copy

Three kinds, NO return, return RECEIPT, return Twosafe

Examples of classic copying

First of all, the classic copy and ASP replication can not coexist, ASP standby is cloned by Ttrepadmin. Classic replication is a two pre-built standalone database, and tables need to be pre-built

One-way replication

Cachedb1>CREATE TABLE A1 (a int, primary key (a)); cachedb1>CREATE REPLICATION repscheme ELEMENT e TABLE A1 MASTER CACHEDB1 on "Timesten-hol"Subscriber CACHEDB2 on "Timesten-hol"    RETURNRECEIPT; Perform the above two operations on CACHEDB2 to start the replication agent on CACHEDB1 and cachedb2 at the same time (note that if the data on both sides is inconsistent before initiating replication, there is no effect on the number of records, for example) CACHEDB1>Call Ttrepstart; then: CACHEDB1>Insert intoA1 VALUES (1); cachedb2> Select *from A1;< 1 >Cachedb2>Repschemes; Replication Scheme Tthr.Repscheme:element:eType: Table tthr.A1 Master STORE:CACHEDB1 onTimesTen-holTransmit durable subscriber STORE:CACHEDB2 onTimesTen-hol ReturnReceipt STORE:CACHEDB1 onTimesTen-holPort: (Auto)LogFail Threshold: (None) Retry Timeout: -Seconds Compress traffic:disabled STORE:CACHEDB2 onTimesTen-holPort: (Auto)LogFail Threshold: (None) Retry Timeout: -Seconds Compress traffic:disabledReturnService Wait Time:TenSecondsReturnService onReplication stop:disabledReturnService Failure Policy: (None)

One-to-many replication

 CREATE REPLICATION twosubscriberselement e TABLE ttuser.tab MASTER masterds  on "Serv Er1 " subscriber subscriber1ds  on " Server2 ", Subscriber2ds  on " Server3 ";If you use a different return service, you can use the following example: CREATE REPLICATION twosubscriberselement e TABLE ttuser.tab MASTER masterds  on "Serv Er1 " subscriber subscriberds1  on " Server2 " RETURN RECEIPT  by REQUEST subscriber Subs Criber2ds  on "Server3" RETURN receiptstore masterds failthreshold ;

Different tables are copied to different destinations

such as table A from masterds to Backup1ds,b table from Masterds to Backup2ds

CREATEREPLICATION Twobackupselement ATABLETTUSER.TAB1 MASTER Centralds on "Finance"Subscriber backup1ds on "Backupsystem"ELEMENT bTABLETTUSER.TAB2 MASTER Centralds on "Finance"Subscriber backup1ds on "Backupsystem"ELEMENT DTABLETTUSER.TAB3 MASTER Centralds on "Finance"Subscriber BACKUP2DS on "Backupsystem"ELEMENT DTABLETTUSER.TAB4 MASTER Centralds on "Finance"Subscriber BACKUP2DS on "Backupsystem";

Cascading (propagation) replication

CREATE REPLICATION propagatorELEMENT a TABLE ttuser.tab  MASTER centralds ON "finance"  SUBSCRIBER propds ON "nethandler"ELEMENT b TABLE ttuser.tab  PROPAGATOR propds ON "nethandler"  SUBSCRIBER backup1ds ON "backupsystem1",             backup2ds ON "backupsystem2";

Bidirectional replication

Perform the following actions on both CACHEDB1 and CACHEDB2: CREATE TABLE A2 (a int, primaryKey(a)); CREATE REPLICATION r1element elem1 TABLE A2 MASTER cachedb1 on "Timesten-hol"Subscriber CACHEDB2 on "Timesten-hol" RETURNReceiptelement elem2 TABLE A2 MASTER cachedb2 on "Timesten-hol"Subscriber CACHEDB1 on "Timesten-hol" RETURNRECEIPT; start rep agentcachedb1> on both CACHEDB1 and CACHEDB2Select* fromA2;cachedb2>Select* froma2;cachedb1> Insert intoA2 VALUES (1);cachedb2>Select* froma2;<1>cachedb2> Insert intoA2 VALUES (2);cachedb1>Select* froma2;<1><2>

In bidirectional replication, there are two modes of application, one is split workload, that is, accessing different data on both sides. Another is distributed workload, that is, double live, both sides access the same data, but must be applied to ensure conflict detection and resolution.

Hybrid replication

For example, some tables are two-way replication, and some are one-way. Must be defined in a replication scheme. Cannot define two schemas, otherwise error:
8169:store CACHEDB1 on Timesten-hol already transmits to Store CACHEDB2 on Timesten-hol; At the most one of the path allowed from one store to another

Here is an example:

CREATE REPLICATION r1element elem1 TABLE A2 MASTER cachedb1 on "Timesten-hol"Subscriber CACHEDB2 on "Timesten-hol" RETURNReceiptelement elem2 TABLE A2 MASTER cachedb2 on "Timesten-hol"Subscriber CACHEDB1 on "Timesten-hol" RETURNReceiptelement e TABLE A1 MASTER CACHEDB1 on "Timesten-hol"Subscriber CACHEDB2 on "Timesten-hol"    RETURNReceipt;cachedb1>Repschemes; Replication Scheme Tthr.R1:element:eType: Table tthr.A1 Master STORE:CACHEDB1 onTimesTen-holTransmit durable subscriber STORE:CACHEDB2 onTimesTen-hol ReturnReceipt element:elem1Type: Table tthr.A2 Master STORE:CACHEDB1 onTimesTen-holTransmit durable subscriber STORE:CACHEDB2 onTimesTen-hol ReturnReceipt element:elem2Type: Table tthr.A2 Master STORE:CACHEDB2 onTimesTen-holTransmit durable subscriber STORE:CACHEDB1 onTimesTen-hol ReturnReceipt STORE:CACHEDB1 onTimesTen-holPort: (Auto)LogFail Threshold: (None) Retry Timeout: -Seconds Compress traffic:disabledReturnService Wait Time:TenSecondsReturnService onReplication stop:disabledReturnService Failure Policy: (None) STORE:CACHEDB2 onTimesTen-holPort: (Auto)LogFail Threshold: (None) Retry Timeout: -Seconds Compress traffic:disabledReturnService Wait Time:TenSecondsReturnService onReplication stop:disabledReturnService Failure Policy: (None)

Summarize

TimesTen supports two replication strategies, one ASP and one classic copy.
While ASP is recommended, classic replication supports dual-live, which is not available to ASP, and classic replication has finer granularity and more flexibility to define more complex topologies, while the ASP's replication topology is fixed.

Reference

• A description of how the Log Buffer flusher works (Doc ID 392247.1)

TimesTen Database Replication Learning: 10. Defining Classic Copy