Technical Article BIG-IP WAN Optimization Module (WOM) and IBM DB2 Replication December 27, 2012 by Nojan Moshiri 55 article application delivery ibm partner performance us 0 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 Feature HADR SQL Q Replication Scope of replication Entire DB2 Database Tables Data propagation method Log shipping Capture/Apply tables Synchronous? Yes No Asynchronous? Yes Yes Automatic client routing to standby? Yes Yes Operating systems Linux, Unix, Windows Linux, Unix, Windows, Z/OS Applications read from the standby? No Yes Applications write to the standby? No Yes SQL DDL replicated? Yes No Hardware supported Hardware, OS, version of DB2 must be identical Hardware, OS, version of Db2 may be different Tools for monitoring? Yes Yes Network compress or encryption? No Yes Partitioned DB support? No Yes 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). last modified: February 08, 2013 1 Comment(s): 0 I have made a minor edit to this blog, in the link to the HADR Redbook, I neglected to include ".com" in the URL. The URL has been corrected in the body and again here for reference:You can read more about HADR through the Redbook here: http://www.redbooks.ibm.com/abstracts/sg247363.html-Nojan You must be logged in to post comments.