Replication technologies in Informix Dynamic Server

Enterprise Replication (ER)

What is ER?

Databases store important data for your business, and business people
recognize that storing that data in at least two places is a wise
strategy. Wouldn't it be great if you could tell your database to
replicate the data you wanted to another server automatically? Well since
Version 7, IDS has allowed you to do that. This occurs through our
Enterprise Replication (ER) technology, which is both flexible and
powerful.

ER works by you first defining the servers among which you would like data
to be replicated. This creates a network topology — of root, leaf,
and non-root, non-leaf nodes — that will transfer the data. Each
ER node may be a single server or a cluster of servers, which this
article further discusses below. All the interconnected nodes together are
called an ER domain. The domain does not define what data will be
transferred, just the paths or routes along which the data will flow.

Next, you decide what data you would like to be replicated. This is defined
by an SQL query involving a server, database, table, and columns. The
query acts as a filter whose output decides the data to replicate and
proves to be a very flexible tool. If you'd like an entire table to be
replicated, your query would be
SELECT * FROM .... If you'd like just the first
three columns to be replicated, your query would now be
SELECT column1, column2, column3 FROM .... Or, if
you'd like only certain rows to be replicated, you just use a
WHERE clause in your query. ER can replicate
data with very broad or very fine filters. To help with replication ER
requires that the table have a primary key defined.

After the query is written, determine what nodes should participate in
replicating the data. Let's say you want nodes A, B, and C to all have the
same data in their Employee table. So A, B, and C are your
participants. ER can be configured so that if data changes are made
on any server, the other participants become updated too. This is called
an update anywhere scenario. What happens if you only want updates
to flow from A, into both B and C, but back never to A? ER gives you the
flexibility to do these primary-target scenarios as well. In
situations where data changes occur on more than one participant, it may
happen that a row changed on server B conflicts with a change occurring on
server C. ER allows you to define rules for automatically handling such
conflicts. These include comparing timestamps, running a stored procedure,
and ignoring conflicts.

Now that you know the query, the participants, and the scenario, take all this information and use
it to create something called a replicate. Think of a replicate as
a faucet that controls the flow of data from the query. The faucet can be
turned on or off, and you can have as many faucets as you want. Replicates
can be grouped into sets, which make it easier for users to control
multiple replicates. Also, you can use templates to help you quickly
create replicates for multiple tables. All this work is done through the
server utility cdr.

After a replicate has been created and started, how does the data actually
get moved? Well, first of all, the replication happens asynchronously.
This means there may be a delay between data being committed on one node
and it appearing on another node. And only committed data, of course, is
replicated. ER works by reading the logical logs, testing if a log record
needs to be replicated, and finally sending that information to the
appropriate participants.

A simple example

Let's set up a simple example of ER. We'll replicate the employees table of
the "db1" database in an update-anywhere fashion between server1 and
server2. (The following steps assume the informix account is used.)

Step 1: Prepare the SQLHOSTS files

The file for each server should contain an entry for both servers as well
as two group definitions. Each group represents an ER node:

Now you're replicating data! An insert, update, or delete to a row on
either server1 or server2 will soon appear on the other server. This is
only the beginning of using ER. To learn more about the technology and
useful commands, please see the "Replication" section of the IDS
InfoCenter (see Related topics).

High availability cluster

Starting in Version 11.1, IDS contains cluster-based replication
functionality. In a cluster, you gain scale-out, high availability,
disaster recovery, and load-balancing capabilities. This technology is
built right into the database and requires no additional hardware,
software, or network infrastructure. In contrast to ER, in a cluster, you
cannot control the granularity of data replication; the entire server's
data is always replicated. Cluster and ER technologies, as this article
shows later, can be used side by side.

A cluster consists of one primary server and a mix of three different types
of secondary servers:

Shared Disk (SD) secondary server

High-Availability Data Replication (HDR) server

Remote Standalone (RS) secondary server

Servers must be the same IDS version and run on the same operating system,
hardware, and disk architecture. In 11.1, the secondary servers only
allowed applications to read data. In 11.5, applicants can now also
insert, update, and delete data on secondary servers. Thus, all three
secondary server types can be used to increase the capacity of your
primary as well as balance the load. Table 1, below,
compares the server types. Table 2 lists some common
ONCONFIG settings.

Table 1. Overview of the
characteristics of cluster server types

Primary

SD secondary

HDR

RS secondary

First IDS version

11.1

6.00

11.1

Primary purpose

Increased capacity/scale-out

High availability/hot standby

Disaster recovery/remote backup

Maximum number of nodes per cluster

One

No limit

One

No limit

Possible type changes in one step

SD secondary, HDR, standard

Primary

Primary, RS secondary, standard

HDR, standard

Supported isolation levels

All

Dirty read, Committed Read, Committed Read Last Committed

Dirty read

Dirty read

Table 2.
ONCONFIG parameters common to cluster server
types

Configuration parameter

Server type

Supported values

Description

HA_ALIAS

SD and RS secondary servers

DBSERVERNAME or one of the DBSERVERALIAS name. Must be a network
protocol.

Used to define the name by which a secondary server is known
within a cluster. Gives you flexibility to use something other
than DBSERVERNAME.

Required to be set to 1 at HDR, RSS, and SD secondary servers.
Controls the default logging mode for explicit temporary tables
created with CREATE TEMP TABLE or SELECT INTO TEMP statements.
Secondary servers must not have logged temporary tables.

Let's get to know these server types a little more. Let's explore their
characteristics, their ONCONFIG parameters, and, along the way, build a
cluster with one of each type.

Shared Disk (SD) secondary

SD secondary servers share disk space — except temporary dbspaces
— with the primary server. This is typically done through a
network-based clustered file system. Adding a new SD secondary to a
cluster is very easy and can be done in a few seconds once the shared disk
is prepared. Because SD secondary nodes leverage the primary's disks and
can be brought up easily and quickly, they are well-suited for scale-out
scenarios. In SD secondary servers checkpoints are synchronized. This
means a checkpoint at the primary server completes only after the
checkpoint at the SD server completes. From IDS 11.5 onwards, an SD
secondary supports committed read and committed read last committed
isolation levels as well as dirty read. An SD secondary can be promoted to
a primary server with one single command:
onmode -d make primary <name of SD server>.
Because an SD secondary server is so close to the primary (in other
words,it shares the same disk), it is often the best type of server to
initially fail over to if the primary should encounter a problem.

This configuration parameter is used at the primary to decide how
long to wait for an acknowledgement from an SD server. If no
acknowledgement occurs, the primary acts to shut the SD server
down. The default value is 20 seconds.

Adding an SD secondary to a cluster

Let's take a standalone IDS server and turn it into a cluster. First let's
add an SD secondary server. (This scenario assumes that the shared disk
file system has already been set up and the informix account is used.)

Step 1: Prepare the SQLHOSTS file

Make sure the SQHOSTS file at both the primary and SDS node has entries
for the other server:

Figure 1. The cluster: Primary and one SD secondary

High-Availability Data Replication (HDR)

High-Availability Data Replication or HDR is the oldest replication
technology in IDS. It consists of a pair of servers — the primary
and the HDR secondary — and supports both synchronous and
asynchronous replication modes. In synchronous mode, transactions on the
primary server will not commit until it receives an acknowledgement from
the HDR secondary server. Thus the HDR secondary is immediately ready to
take the place of the primary server — what is called a "hot"
standby. In asynchronous mode, only checkpoints are synchronized between
the primary and HDR secondary. One characteristic of HDR is that it uses a
half duplex communication protocol and thus is sensitive to network
latency. This is not the case for SD and RS secondary servers.

In order for HDR to work, certain ONCONFIG
parameters must be exactly the same on both the primary and secondary. In
many cases, this means they cannot be changed while either server is
running. These parameters include DRAUTO,
DRINTERVAL,
DRTIMEOUT, settings related to the root
dbspace, settings related to logical logs, and more. To ensure these
settings match, one technique is to copy the onconfig of the primary to
the secondary, and then modify the few things that must be different, such
as DBSERVERNAME. You should plan for these
settings before bringing the primary up. This example just uses the
defaults.

Step 3: Backup the primary

On the primary, take a level zero backup:

ontape -s -L 0

Step 4: Register the HDR secondary server with the primary

On the primary, run:

onmode -d primary hdr

Step 5: Prepare the HDR secondary server's disk

The storage used on the HDR secondary must match that of the primary (for
example, number of dbspaces, number of chunks, chunk sizes, pathnames, and
offsets). Since this example uses the backup to restore the HDR secondary
server, it is only necessary that the chunk files exist and have the
proper permissions.

Step 6: Restore the backup on the HDR secondary server

On the HDR server, perform a physical restore of the level 0 backup:

ontape -p
Three questions will be asked. Answer as shown below:
Continue restore? (y/n) y
Do you want to back up the logs? (y/n) n
Restore a level 1 archive (y/n) n

Step 7: Bring the HDR secondary server into online mode

After the restore is complete, the HDR secondary will be in recovery mode.
Run the following command:

onmode -d secondary myprim

HDR secondary is up and running! Run
onstat -g dri at the primary and HDR secondary
to check HDR status. Our cluster now looks like this:

Figure 2. The cluster: Primary, one SD secondary, and one HDR secondary

Remote Standalone (RS) secondary

The primary purpose of the RS secondary server is to provide a disaster
recovery solution. As in HDR, the primary server sends all its logical log
records to the RS secondary server continuously, except now only in an
asynchronous manner. Unlike HDR, the communication occurs using a full
duplex protocol. Thus the RS secondary is less sensitive to network
latency and can more easily reside in a distant geography. One
characteristic of an RS secondary is that unlike in SD and HDR servers,
the primary server doesn't synchronize checkpoints with an RS secondary.
Thus it is not immediately ready to take the place of the primary; it must
be changed into an HDR server first. Should multiple failures in a cluster
occur, however, an RS node can prevent your database from being entirely
unavailable.

Table 5. Important
ONCONFIG parameters for the RS secondary

Configuration parameter

Server type

Supported values

Description

LOG_INDEX_BUILDS

Primary

0 - Disable index page logging1 - Enable index page logging

While creating a new index at primary server, Index Page
Logging(IPL) writes index pages to logical log file. Index page
logging must be enabled at primary server to add RS server to a
cluster.

Figure 3. The cluster: Primary, one SD secondary, one HDR secondary, and one RS secondary

Conclusion

In this article, you've learned about the flexibility of asynchronous
replication using ER and the high-availability features of a cluster. What
if you want to use both together? Well, you can! Remember you used the
"group" notations for the cluster SQLHOSTS files? That same group can
become an ER node just like server1 and server2 in the article
example. You use the same command to add a cluster as a node to an ER
domain as you do a standalone server:
cdr define server --sync=<sync node> --init <name of new group to add>.
When a cluster is an ER node, the primary performs the same
responsibilities that a standalone server node does. However, since it's a
cluster, the primary also replicates the data to all of its secondary
servers.

Now, there is more to the story — more about each of these
individual technologies, more about how they can work together to make
your business better, and more about related technologies like
the Connection Manager and Continuous Log Restore! We invite you to learn more
about IDS!

"What's new in IDS 11?"
(developerWorks, June 2007): Your reference for the new features in the
IDS 11. Explore various aspects of IDS, such as scalability, high
availability and performance, integrated solutions, and
administration-free zone.