JCC Consultants were asked, recently, to discuss using Hot Standy (HS) or the JCC Continuous LogMiner Loader (JCC LML) to replicate to an Rdb database to be used for reporting.
The two tools – HS and JCC LML – have very different goals. HS is to support recovery from disasters. HS maintains one physical copy of the source (master) database. The purpose of the JCC LML is to replicate data changes transactionally to another relational database. The target can assume multiple different personalities. HS and JCC LML are complementary solutions, not competitive. Some customers use both approaches to satisfy different goals.
Running reports out of the target database when HS is the transport mechanism is potentially problematic. The reason is that the standby database is not, at any one point in time, transactionally consistent. That is, it may contain information in an index for a row that is not yet in the database and vice versa. Similarly constraints in the source are not necessarily reflected in the target. We have worked with customers who have experienced this unfortunate situation. The results are not pretty. All sorts of strangeness can occur, including process failure when Rdb detects an inconsistent database.
The reason for the difficulty is that the whole machinery of locking is not really applied to the target of HS. This is one of the ways that HS picks up its efficiency.
On the other hand, JCC LML does all of its work transactionally. That means that all queries made in the target will see a consistent database. Of course this means that the JCC LML does not see any part of a transaction until it is actually committed. As a result we say that the JCC LML does its work in near real-time.
HS management procedures are designed to support quick and accurate failover to the target in case of disaster. HS also supports monitoring its status. For recovery from disaster that is what is needed.
JCC LML management procedures are designed to permit interruption of processing and smooth resumption without any loss of data. JCC LML management procedures support a range of architectures from simple replication to a collection of targets with a variety of purposes. JCC LML manages the LogMiner embedded in Rdb, interactions with database backup, and all of the continuous operation. Operation can be interrupted if there is a need and can be resumed without loss of data or transactional inconsistency. JCC LML manages the log files for the LogMiner and the Loader. JCC LML monitoring is complete and configuarble, can send operator alerts, and is sufficient to reveal some issues downstream from the JCC LML.
There are also differences in the impact that the two products have on the source (master) database. To minimize the performance impact on the master HS database, we find that cold synchronization provides the best results. However, this exacerbates the likelihood that the HS target will not be appropriate for reporting. Also note, in this context, that the JCC LogMiner Loader has such a minimal performance impact on the source database that our customers can seldom discern it.
In addition, the Loader approach is scalable. That is, you may replicate a single source database to multiple targets (of differeing architectures) on different machines. This allows the reporting workload to be spread across multiple targets. Since the indexing in each target may be tailored to the workload on that target you get a very scalable solution.
While you can use the Loader to write to an Rdb target, the Loader also offers the option, at no incremental cost, of writing to additional different database systems as targets. We have customers who are replicating to Rdb, Oracle, Teradata, SQL Server and MySQL.