High Availability Options for Db2 LUW

You may also like...

11 Responses

  1. Rui Chen says:

    Hi Ember, thanks for the article, really nice summarization of available HA/DR solutions!

    Got a couple of questions wondering if you could share some insight:
    1. for HADR, is vIP really necessary, besides reducing client side re-routing complexity, and split-brain? in multi-subnet deployment (eg. muti-Availability Zone in AWS), it seems TSA may not be able to manage the vip through IBM.ServiceIP (https://developer.ibm.com/answers/questions/358520/in-a-db2-hadr-environment-does-tsamp-allow-for-nod/ and https://developer.ibm.com/answers/questions/258835/can-tsamp-move-or-failover-an-ibmservice-ip-resour.html?childToView=423565#answer-423565), nor does it really check if the vip is really attached to host. IBM.Application may still be a potential option, which requires customized scripting…
    2. assuming performant shared storage is not available (eg. in AWS excluding third party software), do you have recommendations for log file management, especially on hardening archive logs out of Primary? We could use cronjob and do customized log-shipping, but there’s still the risk losing archive logs that are not shipped yet, and also available in archive log dir (LOGARCHMETH1). (Well, we still haven’t totally figured out how active log purging works in detail on Standby, but according to IBM site Standby log files can be purged once Standby gets the confirmation from Primary that the log file has been archived…. what exactly does “has been archived” mean? but that’s tangent to may main questions here….) We could turn on both LOGARCHMETH1 and LOGARCHMETH2, but that only provide protection within a single AWS AZ, and won’t survive AWS AZ-wise EBS failure. One idea we are considering is to use EFS or block storage replication (eg. drbd) to make LOGARCHMETH2 contents synchronously available across AZ. We did the calculation, and overall the EFS and drbd transfer rate should be able to catch up with our logging rate.
    3. In general, could you point us to some best practice reference on multi-AZ HADR deployment on AWS?

    Thanks again for your help, and this awesome site!

    • Ember Crooks says:

      1) The VIP is not required for HADR. You can also use ACR to automatically reroute connections to the other database server in the event of the failover. Using ACR requires either the use of type 2 jdbc drivers or any kind of architecture that goes through a full Db2 client on the application server. Some applications that do not meet that criteria (such as using type 4 jdbc drivers with WebSphere) offer the option within the application for an alternate database that the application will use if it cannot contact the primary. Overall, the value of automated failover comes into question. You can absolutely use HADR without automating failover, but that means an actual DBA must be involved if there is a need to bring up the standby. Some applications, that meets the RTO, but for many it does not.

      2) If you’re trying to externalize logs as fast as possible, you can consider using MIRRORLOGPATH to write to some external storage. However, this is likely to have a severe performance impact, as any commit will not be considered successful until the confirmation is received that the record has been written to both places. Often the write performance on any external storage is going to make that bad. I would do everything in my power to talk you out of roll-your-own log shipping. The RPO is just not there in most solutions that I have seen.

      An archive is a specific concept related to a log file. Assuming you don’t use infinite logging (with LOGARCHMETH set to -1 – never use this, please), a log file is archived when all transactions in it and all previous log files have been committed or rolled back, or on database backup, or when the ARCHIVE LOG command is issued. It represents Db2 being done with the file. In a slow database, it could be days since that log file was first opened, so using any archiving as a log shipping method does not provide a reasonable RPO.

      Db2 deletes any log files it is done with on the standby – you cannot archive logs from the standby to anywhere.

      3) First of all, if you want both HA and DR, then have one standby in the same AZ for HA, and then have one or two standbys in the other AZ. This requires manual failover when one AZ must pick up for another, but those are presumably rare and often scheduled events, right? If you are only trying to achieve DR with the multi-zone HADR, then two servers will work fine, but you’ll want to be careful in selecting the SYNCMODE – it’s likely that NEARSYNC will not work and you’ll want either ASYNC or SUPERASYNC. That increases your risk for a bad RPO. I don’t have a reference for AWS. I’ll reach out to a contact and see if IBM has anything.

      • Rui Chen says:

        Hi Ember,
        Really appreciate your reply, and many helpful tips in there! Just to provide a little bit more context for you and future readers. btw, we currently use luw V10.5FP8 on Linux.

        ACR or ClientAffinity may not work for us, since we are considering using ROS (https://www.ibm.com/developerworks/community/wikis/home?lang=en#!/wiki/DB2HADR/page/Client%20Reroute section Reads on Standby)…..

        For client re-route, our current thought is to make HADR nodes publish failover/role-switch event to some config-hub, which would then push new Primary/Standby connection info to application. Application then updates the connectionPools with new connection info.

        Another less favored option is still using vIP exposed to application, but vIP just won’t be managed by IBM.ServiceIP. The second approach is less favored since a lot of customized toolings are required for the orchestration, and RTO would be at least the time to failover the vIP, plus the vIP failover operation would be SPOF by itself.

        We are not trying to externalize active logs, which should be synchronously available on Standby anyway (assuming PEER state, and of course there’s always the risk of data loss when falling out of PEER….. Actually, MIRRORLOGPATH, using locally mounted EBS, may not be a totally bad idea, which provides an extra copy of active logs/protection on Primary within the same AZ when HADR pair falls out of PEER state). The goal here is to make archived logs survive AWS AZ-wise EBS failure. We don’t rely on log-shipping to meeting the RPO. Log-shipping uses cheaper storage to reduce cost, and servers mostly for log file management purpose. It should be relatively rare when we really need those archived logs, except when QRepl needs them, or when we need to reinit extra Standby, but just in case….

        Agree with not using infinite logging, which is not allowed by HADR anyway.(https://www.ibm.com/support/knowledgecenter/en/SSEPGG_10.5.0/com.ibm.db2.luw.messages.sql.doc/doc/msql01768n.html reason code 2, and https://www.ibm.com/developerworks/community/blogs/IMSupport/entry/weekly_tip_from_experts_why_infinite_logging_is_not_supported_in_hadr_configuration?lang=en)

        3) Ideally we’d love to have multiple Standby, and to have NEARSYNC (HA) across AZ, but if the perf is not satisfying, then we can only have HA within the same AZ….

        wooo, that’s a long one…. Big thanks again for your detailed explanation!

        • Ember Crooks says:

          Be careful with ROS – everyone I’ve ever seen use it extensively hated it. It is simply not the reporting solution that some want to make it. (see High Level Overview of Reads on the HADR Standby.

          Db2 writes to the transaction log files before a commit is considered successful, so I suspect MIRRORLOGPATH there would cause serious performance issues. If it’s a last-line-of defense approach, using LOGARCHMETH2 might be a decent option, assuming you spin through logs on a fairly regular basis. You’d likely want to keep your logfilsiz low.

          If you’re considering NEARSYNC for cross-AZ stuff, I’d try the HADR tools first to see the performance impact. Generally, the primary and the standby for SYNC or NEARSYNC need to be within 100KM of each other on a high speed network to avoid performance impact. Perhaps the AZ model works that way within the same region, but I don’t know if it is guaranteed to.

          • Rui Chen says:

            Yep, we got warned about ROS many times, and that’s why we still keep QRepl…. but it’s so attempting to use ROS, since we already pay for it……

            found this link suggesting the loggw double flush introduced by MIRRORLOGPATH is serial, which sounds less ideal (doubled commit time for each TX….) https://developer.ibm.com/answers/questions/365855/impact-of-log-mirroring-on-performance.html but otherwise MIRRORLOGPATH sounds something nice to have.

            couple of follow up question:
            2.1) is “keep your logfilsiz low” a general best practice, or specific to LOGARCHMETH2? Our logfilsz=16384 (namely, 64MB log file size). How do we estimate if the logfilsiz is appropriate?

            2.2) Could you please elaborate a little bit more on the meaning of “spin through logs on a fairly regular basis”? not sure i understand (#newbie)…. is that something tie to LOGARCHMETH2?

            love the HADR tooling. We use db2logscan daily and model the network perf using bandwidth 100MB/sec (according to the simulator and other references, eg. https://stackoverflow.com/questions/18507405/ec2-instance-typess-exact-network-performance ) and ping time at 0.3ms (we found 2 AZs seem close enough, but haven’t found any official document from AWS on average ping-time history across AZs. btw, distance of 100KM is equivalent to about at least 0.67ms ping time), and rarely see ??, though ? still pops up from time to time, but we assume that’s acceptable.

          • Ember Crooks says:

            2) Right, mirroring logs can add slowness a the most critical point in an OLTP environment. If you’re only using it for a worst-case scenario, and you have a regular HA solution in place, I’d recommend not using MIRRORLOGPATH. However, I know talented DBAs who ALWAYS mirror logs because they are afraid of corruption.
            2.1 & 2.2) I say that about log files because if you wanted to use LOGARCHMETH2 to externalize log files, you wouldn’t want a log file open for days. Generally you want to look at how often you’re archiving and aim for about 4 archives per hour on average if you can. That’s not a hard and fast number, so it’s not bad if you’re not hitting it, and every database has peaks and valleys. There are other things that impact the best size – sometimes log files are larger to accommodate ridiculously large transactions. You also keep LOGPRIMARY on the smaller side, while LOGSECOND is often larger.

  2. Rui Chen says:

    Awesome, big thanks Ember for all the details!

  3. Doug Pederson says:

    What are your thoughts on setting up a VIP on a load balancer, instead of doing this with db2haicu? I would prefer to avoid TSAMP if possible because of what I have heard about it. The requirements I have is no automated failover, just manual. The main concern I have is the connections are so varied that I don’t think ACR will work well. I think my best option is to setup a VIP so the clients have one place to connect to, and then if I do a manual failover I can redirect the VIP to the new Primary database. I am not sure the best way to configure this.

    • Ember Crooks says:

      As long as you’re OK with manual failover, having a VIP via some other method is perfectly fine. You might configure ACR anyway for the clients that support it, but point to the VIP – this means supporting apps can retry against the VIP, which could be useful. You can even use a tool like Power-HA, RHCS, or MSCS to automate IP failover along with DB2 failover, though that is vastly more complicated than using TSAMP. From a usability perspective one of the awesome things about using TSAMP is that I can just issue the normal HADR takeover commands and TSAMP takes care of the IP and such.

      You obviously run the risk of the VIP failing over without the DB or vice-versa.

      In the real world, there are a kaleidoscope of technologies that are often combined in different ways to make HA work.

  4. Milind Taralkar says:

    Hi Amber,

    My DR DB is remote catchup state. It has a huge gap logs of around 780

    PRIMARY_LOG_FILE,PAGE,POS = S0021359.LOG, 116449, 34233627172131
    STANDBY_LOG_FILE,PAGE,POS = S0020570.LOG, 8190, 32968617411284

    Without droping and restoring of DB on DR is there any possible way to bring DR DB back to peer state.

    • Ember Crooks says:

      I suspect you need to reinitialize (restore DB on DR). Is the log gap dropping at all? Have you tried stopping and starting HADR? Have you looked in detail at the diagnostic logs from both the primary and the standby. Is your network speed high to your DR site?

Leave a Reply

Your email address will not be published. Required fields are marked *