One View of IBM's Replication Options

Written by Sean Byrd

Senior IT Specialist

March 25, 2008

Updated October 1, 2009

 

Note: In no way should this document be considered an official IBM statement. These are views and opinions based on my experience and understanding of the various IBM replication offerings.

For a high level review of the considerations discussed in this article, a summary table is available at the end of the article.

No matter where your data is currently stored, at some point someone will ask you to move it. Whether it is for load balancing, high availability, a new data mart, or an operational data store, you will need to decide on a method to move your data. IBM has a variety of replication offerings available, for a wide range of sources and targets. There is still much confusion on all sides about capabilities and applicable architectures. This article will hopefully help you recognize topics that need to be taken into consideration when looking for a replication solution.

Solutions

IBM has three replication methods that we will consider in this article.

· Change Data Capture

· Q Replication

· SQL Replication

Q Replication and SQL Replication are both components of IBM’s InfoSphere Replication Server product, but need to be discussed separately because of their distinct capabilities and uses. SQL Replication is IBM’s heritage replication product formerly known as DataPropagator on the mainframe and DPropR in the Linux, UNIX, Windows (LUW) world. InfoSphere Change Data Capture (CDC) is the DataMirror heritage product formerly known as Transformation Server.

Informix replication is a special case. If you have Informix databases as sources and targets, you need to use Informix Enterprise Replication. While you can use CDC or SQL Replication to accomplish the data movement, Enterprise Replication should be your first consideration.

Sources and Targets

Where your data is coming from is the first thing to consider when looking at data replication options. Some programs are limited in the data sources they can work with, or there are performance penalties associated with specific sources.

The next point of consideration will be, “Where will the data land?” There are many more choices here across the products because traditionally it is much simpler to translate writes into a database than reading logs, as most databases have been designed to make it easy for applications to input data. Let’s look at some source/target options for our replication solutions.

·         Q Replication is the most limited option in that it requires a DB2 or Oracle source. In this case the DB2 can be z/OS or LUW based DB2, but not iSeries. Oracle on all platforms is supported. Q Replication can replicate to limited non-DB2 targets including Oracle, Microsoft SQL Server, Informix Dynamic Server and Sybase.

·         SQL Replication has a wider range of sources and targets. DB2 (including iSeries databases), Oracle, Informix Dynamic Server, Microsoft SQL Server and Sybase Adaptive Server Enterprise can all be used as sources and targets. In addition Informix Extended Parallel Server and Teradata are supported as targets only.

·         CDC is the most versatile product as far as sources and targets are concerned. Sources for CDC include Oracle, DB2 (on any OS, mainframe or distributed, i/z/p/xSeries), MS SQL Server, Informix, SolidDB and Sybase. Targets include all of the sources as well as Teradata, Netezza, DataStage, CognosNow, Greenplum, MQ and JMS.

Capture

How the respective capture program tracks changes is a determining factor on throughput, but more importantly may be an invasive aspect in some architectures.

·         CDC has log read based capture for most sources, verify with your IBM sales rep if the database and platform you are interested in is one of the few exceptions. CDC transmits changes directly, so disk space considerations on sources are not a concern.

·         Q Replication uses calls to DB2’s log read API to track committed changes to the database. Oracle's LogMiner API is used to capture committed changes in Oracle. The capture program does not store the changes locally, but instead places them on an MQ queue for transport.

·         In discussing SQL Replication we must differentiate between DB2 and non-DB2 sources. For DB2 sources SQL Replication uses the same log read API to capture changes. Non-DB2 databases require triggers on the source database to capture changes (This includes Oracle. The log capture capability used by Q Replication is not available for SQL Replication). In some environments this may be too invasive for consideration. In either case, the changes are captured in Change Data tables. These are tables local to the source that contain a record of all committed changes occurring on the source. This is a major consideration if disk space is limited.

Transport

At some point the source and target have to communicate with one another. How this is accomplished can determine throughput, availability and recoverability of a replication solution.

·         CDC uses a direct TCP/IP connection between source and target. As transactions are transmitted directly, there is no need to worry about disk space for transaction storage. However, because CDC relies upon an open connection, both source and target applications must be “up” in order for transactions to be transmitted. In the case of an outage, upon restart, the logs will be processed until the transactions are again current.

·         Q Replication uses MQ Series queues as the transport mechanism. Changes are placed on the queues as MQ messages and read from the queues by the Apply program. In the case of an outage of the target server, the MQ queues should be sized (message depth and disk space) to accommodate enough messages that Capture can continue running until the target database is once again available.

·         SQL Replication uses a DRDA connection for Apply to read the Change Data tables. In the case of target server outages, the change tables on the source server can grow considerably. Plan for disk space accordingly.

In each of these cases the application on the source server ensures the changes are captured for transmission. The transmission protocol ensures the messages get to the target. The target side applications ensure the changes are written to the database. Each of the solutions is tolerant of outages to a degree, but outages must be handled differently in each case because of transmission methods.

Throughput and Latencies

Throughput will ultimately be limited by network bandwidth, however if you can take full advantage of the available bandwidth you can move a lot of data. Latency is the time it takes for a change at the source to be completed at the target. Each component of an architecture (source, target, transmission) will contribute latency and naturally the larger the distance between source and target, and the slower the network, the greater the latency will be.

Whenever you talk about performance numbers – YOUR MILEAGE WILL VARY! These numbers are not benchmarks, or guarantees. These are general comparison numbers.

·         CDC can achieve in excess of 10000 tps independent of source and target with sub-second latencies.

·         Q Replication can achieve throughputs in excess of 50,000 transactions per second (tps) with sub-second latencies, even with 1000 miles between source and target.

·         SQL Replication can achieve in excess of 10000 tps for Homogeneous Replication. Heterogeneous Replication (non-DB2 source or target) throughputs will be more in the range of 1000 tps. Latencies for SQL replication will generally be more than 5 seconds and can be much more.

Architectures

Different products are better at different things and the realm of replication is no exception. Each of IBM’s products lends itself to particular replication scenarios. I want to point out here several “sweet spots” where the replication methods demonstrate their strengths.

·         CDC is ideal if you don't know what your source and target will be.

·         Q Replication is specifically designed for high volume, low latency environments where the data source is DB2 or Oracle.

·         SQL Replication is (in my opinion) the strongest replication solution for intermittently connected environments – i.e. where the target database is only able to connect periodically. The only requirement on the source is adequate disk space and the transactions are waiting for whenever the target is able to make a connection.

·         All three solutions provide adequate support for unidirectional replication, while CDC and Q Replication are better solutions for bi-directional or multi-site replication.

·         Fan out architectures – i.e. one source being replicated to multiple targets can be a complex topic. Here's how the three methods are distinct in this environment.

o        CDC will need a distinct application running on the source for each target because of the need to maintain an open TCP/IP connection. So for one source replicating to three targets, you will need three applications running on the source and one application for each target. This will require scaling a source server to accommodate the support for each additional target.

o        Q Replication requires a distinct set of queues for each target. So for one source replicating to three targets you will have three sets of replication queues on the source and one set of replication queues on each target. Again, the source server will need to be scaled in order to accommodate the required support load, in addition, the logistics of managing the multiple queue sets can become cumbersome.

o        In my opinion, SQL Replication is the strongest solution for fan out architectures. A single source, with a single set of capture tables can support one, three, 200 or more targets, with the only additional overhead on the source server being resources to accommodate a simple database connection. Another point is that both CDC and Q Replication (as well as SQL Replication) can replicate to a special type of target table known as a Consistent Changed Data (CCD) table. CCD tables are unique in that they can act as sources for SQL Replication without the necessity of running a Capture program. This allows for rapid replication from a source to a staging server where as many SQL Replication targets as needed can connect.

Price

Price is always a consideration, but your decision in the end should be based on value.

CDC, Q Replication as part of InfoSphere Replication Server are priced by Processor Value Unit, so the number of processors where you are running the applications, sources and targets, will determine your price.

SQL Replication running as Heterogeneous Replication (non-DB2 sources or targets) is also part of InfoSphere Replication Server, and priced accordingly. SQL Replication between DB2 family databases is free on LUW platforms.

Summary

There are many things to consider when deciding on a replication architecture. We haven’t listed all of them here. While no single factor listed here may be a deciding factor, they should all be taken into account when looking at purchasing a replication solution.

Solution Comparison

 

Solution Comparison

 

CDC

Q Rep

Q Rep to non-DB2

SQL Rep DB2 to DB2

SQL Rep non-DB2

Sources

DB2,

Oracle,

SQL Server,

Sybase,

Informix,

SolidDB

DB2 except for iSeries,

Oracle

DB2 except for iSeries,

Oracle

DB2

Oracle,

SQL Server,

Informix,

Sybase

Targets

DB2,

Oracle,

SQL Server,

Sybase,

Teradata,

Netezza,

Informix,

SolidDB,

CognosNow,

Greenplum,

MQ,

JMS

DB2

Oracle

Oracle,

SQL Server,

Informix,

Sybase

DB2

Oracle,

SQL Server,

Informix IDS and EPS,

Sybase,

Teradata

Capture

Log based*

Log based

Log based

Log based

Trigger based

Transport

TCP/IP

MQ Series

MQ Series

DRDA

DRDA

Throughput

10K+tps

50K+tps

10K+tps

10K+tps

1K tps

Latency

sub-second

sub-second

sub-second

seconds

seconds

Unique Considerations

non-DB2

High volume

 

Fan out,

Intermittent connections

Fan out,

Intermittent connections

Price

$15K/100 PVU

$15K/100 PVU

$15K/100 PVU

FREE

$15K/100 PVU

*See qualification in Capture section of document.