11. Replication

11.1 Overview

Replication is maintaining identical copies of a defined set of data on more than one database, on local or remote sites. Replicator lets you transparently replicate data asynchronously by transaction. Replicating data maintains continuous access to your databases, protects against failures, improves performance, and helps you maintaining data across different database systems.

This chapter describes how to use and maintain Replicator.

11.2 Introducing Replicator

Replicator ensures that either the whole transaction is replicated, or none of it is. If a replicated transaction fails, it remains in the Replicator queue and will be tried again. Otherwise, Replicator sends the transaction to the target database. Therefore, if one replicated database goes down, all the transactions that should be replicated to it remain queued and will be sent again in the correct order as soon as it becomes available.

Replicator is a stand-alone process that sends changes to their respective targets. Once the transaction has been transmitted, the replicated transactions are secured on the target database and are removed from the distribution queue. Replicator queues are as many as the target databases.

Figure 11.1 illustrates the design concepts behind Replicator.

  

Figure 11.1: Table Replication

Some important points about Replicator:

• To each source database corresponds one Replicator.
• You may replicate either all the tables of a database or a subset of the database's tables.
• You specify that you want to replicate a specific table on a certain database, using the ``replicate begin'' command. Replicator tracks automatically every change in the transaction log of the database in question and whenever a commited transaction is recorded, Replicator sends it across to the target database.
• A table can only be replicated in one direction with a single or multiple targets.
• Replication of system catalogs is not supported.

11.3 What you can do with Replicator

Replicator allows users to accomplish any of the following tasks:

1.

Assign the ``replicate'' privilege to the target database. This task is mandatory before you begin any replication from the source database.

2.

Revoke the ``replicate'' privilege from the target database.

3.

Replicate a specific table.

4.

Terminate the replication of a specific table.

5.

Replicator with SSL

6.

View information about the tables of the source and the target database on Roadmap.

7.

Edit the Replicator port which is used for connections

How to assign the ``replicate'' privilege to the target database (GRANT command) User admin of the source database must notify admin of the target database to do the following steps.

1.

Connect to the target database (if you haven't already been connected).

2.

Execute the GRANT command (refer to Section 11.4) either through SQL Terminal or SQL Query Tool.

How to revoke the ``replicate'' privilege from the target database User admin of the source database must notify admin of the target database to do the following steps.

1.

Connect to the target database (if you haven't already been connected).

2.

Execute the REVOKE command (refer to Section 11.4) either through SQL Terminal or SQL Query Tool.

How to replicate a specific table
User admin of the source database should follow these steps:

1.

Ensure that the ``replicate'' privilege has been assigned to the target database.

2.

Connect to the source database (if you haven't already been connected).

3.

Execute the REPLICATE BEGIN command (refer to Section 11.4).

4.

View the Error Log of the source database (see Section 7.2.1) to ensure that no errors have occured during the execution of the command.

After you have executed the REPLICATE BEGIN command for a specific table, Replicator will detect the changes automatically at the source database and will send them to the target host. Replication will terminate when you execute the REPLICATE END command. One possible error that may occur during the table replication is a typo error when editing the REPLICATE BEGIN command. For example, if the target database is called ``test1'', we assume that by mistake you enter ``test2''. In this case, communication between Replicator (which runs on the source database) and Ovrimos core of the target database is terminated. Thus, it is recommended that you check the Error Log when you execute this command.

Replication takes place on a table basis. Thus, to be able to replicate either a whole database or some of its tables, you have to replicate one table at a time. To replicate more than one tables, repeat the above steps.

How to terminate the replication of a specific table
User admin of the source database should follow these steps:

1.

Connect to the source database (if you haven't already been connected).

2.

Execute the REPLICATE END command (refer to Section 11.4).

  
11.4 Replicator commands

GRANT
The effect of this command is to allow table replications from the source to the target database. GRANT will not succeed if the target host doesn't exist.

grant replicate to '<source database-name>' on '<source host>'

where <source host> can be either the name or the IP address of the host where the source database resides.

REVOKE
The effect of this command is to revoke the GRANT privilege from the target database.

revoke replicate from '<source database-name>' on '<source host>'

where <source host> can be either the name or the IP address of the host where the source database resides.

REPLICATE BEGIN
The effect of this command is to replicate a specific table from the source database to the target one.

replicate begin table <source table-name>
as <target table-name> to '<target database-name>' on '<target host>'

where <target host> can be either the name or the IP address of the host where the target database resides.

REPLICATE END
The effect of this command is to terminate the replication of a specific table of the source database.

replicate end table <source table-name>
as <target table-name> to '<target database-name>' on '<target host>'

where <target host> can be either the name or the IP address of the host where the target database resides.