In this post I will share some of the configuration details of accelerating DB2 replication using two popular replication technologies, SQL Replication (and optionally SQL Replication with MQ) and high availability disaster recovery (HADR) Replication.
This work started when I had the great pleasure of working with DB2 for the last several quarters. I explored the latest features in DB2 version 9.7 and version 10 and through the IBM Information On Demand Conference I was able to vet and validate my ideas with the great technical resources available at the conference. I would like to thank Martin Schlegel and Mohamed El-Bishbeashy for their great training to validate my independent research.
F5 now has solutions for the most common types of DB2 Replication, SQL Replication and HADR, enabling faster replication over longer distances, maximizing bandwidth and allowing to connections with more latency to be used.
HADR or SQL Replication?
The choice between using HADR or SQL replication is one that will require a lot of investigation on the part of any organization. While HADR is easier to setup and maintain, SQL Replication provides some excellent benefits. In either case, my testing showed that BIG-IP WOM can bring benefits to either solution.
Why use WAN Optimization technology?
* Encrypt: Hardware based encryption secures data transfers if they are not already encrypted (HADR) and offloads CPU intensive tasks from servers if they are already encrypted (SQL Rep).
* Compression: Hardware based compression reduces the amount of bandwidth needed and effectively speeds transfers.
* Deduplication: Data dedup reduces the amount of bandwidth needed and effectively speeds transfers.
Comparison of HADR versus SQL with MQ Replication
Below are some of the comparison points between HADR versus SQL Q Replication, you can read more about the differences through various IBM Redbooks found on IBM.com/db2
SQL Q Replication
Scope of replication
Entire DB2 Database
Data propagation method
Automatic client routing to standby?
Linux, Unix, Windows
Linux, Unix, Windows, Z/OS
Applications read from the standby?
Applications write to the standby?
SQL DDL replicated?
Hardware, OS, version of DB2 must be identical
Hardware, OS, version of Db2 may be different
Tools for monitoring?
Network compress or encryption?
Partitioned DB support?
DB2 Configuration for HADR
Without sounding too biased, purely from an implementation standpoint I felt that HADR was by far the more simple solution to setup and maintain.
Trust between hosts
Route between hosts
DNS or /etc/hosts configuration
Identical DB2 instance users and DB2 fences uses and identical UID and GID on both hosts
Identical home directories
Identical port number and name
Automatic instance start must be turned off
The actual configuration for HADR is to modify the database configuration to set the proper parameters. Just for reference, these parameters include:
Configuration for SQL, optionally with MQ, has two parts, first, the MQ setup should be completed if that is utilized. The second part, creation of Capture and Apply tables is universal whether Q-Replication is used or not, only the BIG-IP configuration would change if Q-Replication is utilized.
Define WebSphere MQ queue managers
Define WebSphere MQ Channels
Define WebSphere MQ local and remote queues
Configuration of Capture and Apply Tables:
Create source and target control tables
Enable both databases for replication
Create replication queue maps if MQ replication is being utilized
Create Q subscriptions if MQ replication is being utilized
BIG-IP configuration for the WOM module is straightforward whether HADR or SQL Replication is being used. In the case of SQL Replication with MQ, the configuration is different, as compression and encryption should be turned off on MQ first for maximum results. This is a scenario I have not yet tested so for this configuration I am focusing directly on SQL replication and HADR replication.
In my tests I enabled both data duplication and compression as I tested with two BIG-IPs over a simulated WAN using a LanForge Virtual Appliance. I looked at bandwidths from 45 Mbps and 100 ms of latency up to 622 Mbps and 20 ms of latency. My dataset was large blobs of text data.
In the diagram at the start of the article you can see that this is a symmetric solution requiring BIG-IPs on either end and in each data center. A license for the WOM module is required:
Symmetric deployment requires BIG-IPs in each data center
WAN Optimization Module (WOM) license
After completing the initial configuration of the WOM, the steps require are only to create an optimized application entry for DB2.
Your database port, along with the DB2 control port should be optimized. In my case as below, that would be port 50,000 and port 523.
I selected memory based dedup and saw the best results in this mode in my tests.