BIG-IP WAN Optimization Module (WOM) and IBM DB2 Replication

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?

Security

* 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).

Bandwidth

* 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

Configuration Basics

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.

Prerequisites:

• Clock synchronization
• 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

Configuration:

The actual configuration for HADR is to modify the database configuration to set the proper parameters.  Just for reference, these parameters include:

• HADR_LOCAL_HOST
• HADR_REMOTE_HOST
• HADR_LOCAL_SVC
• HADR_REMOTE_SVC
• HADR_REMOTE_INST
• HADR_SYNCMODE
• HADR_PEER_WINDOW
• HADR_TIMEOUT

You can read more about HADR through the Redbook here: http://www.redbooks.ibm.com/abstracts/sg247363.html

DB2 Configuration for SQL Q-Replication

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

You can read more about SQL Replication here: http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.ii.doc/start/cgpch200.htm

BIG-IP Configuration

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:

BIG-IP Prerequisites:

• Symmetric deployment requires BIG-IPs in each data center
• WAN Optimization Module (WOM) license

BIG-IP Configuration:

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.

For detailed information about basic BIG-IP WOM configuration, see the following chapter of BIG-IP Documentation http://support.f5.com/kb/en-us/products/wan_optimization/manuals/product/wom_config_11_0_0/1.html (Free login account may be required).