Example of configuring connection management for a high-availability cluster

This example shows steps that are required to configure connection management for a high-availability cluster.

About this task

For this example, you have a high-availability cluster on a trusted network. The cluster consists of three servers:
  • A primary server (server_1)
  • A shared-disk secondary server (server_2)
  • An HDR secondary server (server_3)
The cluster supports the following application services:
  • Online transaction processing (OLTP), which runs only on the primary server
  • Payroll services, which can run on the primary server or HDR secondary server
  • Reporting services, which can run on any of the secondary servers
Your system has the following needs:
  • The database servers' workloads are balanced.
  • The Connection Managers control failover.
  • If failover occurs, the SD secondary server takes priority over the HDR secondary server.
  • If the primary server fails, the Connection Managers can still connect to the cluster after restarting.
  • The system can withstand the failure of a Connection Manager.
  • The system can withstand a network-interface card (NIC) failure on each host.

Procedure

To configure connection management:
  1. Install at least two network interface cards on each host.
    This prevents the failure of a network interface card from causing Connection Manager or database server failure.
  2. Install two Connection Managers. Install each Connection Manager onto a different host, and do not install the Connection Managers onto the hosts that database servers are installed on.
    This installation strategy prevents a Connection Manager from becoming a single point of failure, and prevents the simultaneous failure of database servers and Connection Managers if a host fails.

    You can install Connection Managers on application-server hosts if you want to prioritize an application server's connectivity to the primary cluster server.

  3. On each host Connection Manager host, set the INFORMIXDIR environment to the directory the Connection Manager was installed into.
    Run the following command:
    setenv INFORMIXDIR path
  4. Create a configuration file in each Connection Manager installation's $INFORMIXDIR/etc directory.
    The first Connection Manager's configuration file is named cm_1.cfg and has the following entries:
    NAME connection_manger_1
    LOG 1
    LOGFILE $INFORMIXDIR/tmp/my_cm1_log.log
    LOCAL_IP 192.0.2.0,192.0.2.1
    
    CLUSTER cluster_1
    {
       INFORMIXSERVER servers_1
       SLA oltp_1    DBSERVERS=primary
       SLA payroll_1 DBSERVERS=(PRI,HDR) \
                     POLICY=WORKLOAD
       SLA report_1  DBSERVERS=(SDS,HDR) \
                     POLICY=WORKLOAD
       FOC ORDER=ENABLED \
           PRIORITY=1
       CMALARMPROGRAM $INFORMIXDIR/etc/CMALARMPROGRAM.sh
    }
    The second Connection Manager's configuration file is named cm_2.cfg and has the following entries:
    NAME connection_manger_2
    LOG 1
    LOGFILE $INFORMIXDIR/tmp/my_cm2_log.log
    LOCAL_IP 192.0.2.2,192.0.2.3
    
    CLUSTER cluster_1
    {
       INFORMIXSERVER cluster_1
       SLA oltp_2    DBSERVERS=primary
       SLA payroll_2 DBSERVERS=(PRI,HDR)\
                     POLICY=WORKLOAD
       SLA report_2  DBSERVERS=(SDS,HDR) \
                     POLICY=WORKLOAD
       FOC ORDER=ENABLED \
           PRIORITY=2
       CMALARMPROGRAM $INFORMIXDIR/etc/CMALARMPROGRAM.sh
    }
    The configuration file specifies the following information and behavior:
    • Logging is enabled, and the log files are $INFORMIXDIR/tmp/my_cm1_log.log and $INFORMIXDIR/tmp/my_cm2_log.log.
    • connection_manager_1 monitors 192.0.2.0 and 192.0.2.1 and connection_manager_2 monitors 192.0.2.2 and 192.0.2.3 for network failure.
    • When the Connection Managers start, they each search their sqlhosts files for cluster_1 entry, and then connect to the servers in that group.
    • CONNECT TO @oltp_1 and CONNECT TO @oltp_2 connection requests are directed to the primary server.
    • CONNECT TO @payroll_1 and CONNECT TO @payroll_2 connection requests are directed to whichever of the primary and HDR secondary servers has the lowest workload.
    • CONNECT TO @report_1 and CONNECT TO @report_2 connection requests are directed to the secondary server that has the lowest workload.
    • The connection between connection_manager_1 and the primary server is prioritized over the connection between connection_manager_2 and the primary server. Failover that would break the connectivity between connection_manager_1 and the primary server is blocked.
    • If failover processing fails after eight attempts, $INFORMIXDIR/etc/CMALARMPROGRAM.sh is called.
    Certain parameters and attributes are not included in this configuration file, so the Connection Manager has the following default behavior:
    • The EVENT_TIMEOUT parameter is not set, so the Connection Managers wait 60 seconds for primary-server events before failover processing begins.
    • The MODE attributes of the SLA parameters are not set, so the Connection Managers return connection information for server_1, server_2, and server_3 to client applications, rather than acting as proxy servers.
    • The SECONDARY_EVENT_TIMEOUT parameter is not set, so the Connection Managers wait 60 seconds for secondary-server events before the Connection Manager disconnects from the secondary server.
    • The HOST, NETTYPE, SERVICE, and SQLHOSTSOPT attributes of the SLA parameters are not set, so each Connection Manager uses connection information in local and remote sqlhosts files.
    • The SQLHOSTS parameter is not set, so each Connection Manager first searches its local sqlhosts file, and then remote database server sqlhosts files for connectivity information related to server_1, server_2, and server_3.
    • The WORKERS attributes of the SLA parameters are not set, so four worker threads are allocated to each of the SLAs.
  5. Set the onconfig file DRAUTO configuration parameter on all database servers to 3
    DRAUTO 3
    This setting specifies that a Connection Manager controls failover arbitration.
  6. Set the onconfig file HA_FOC_ORDER configuration parameter on server_1 to SDS,HDR
    HA_FOC_ORDER SDS,HDR

    After the Connection Managers start, and connect to server_1, the HA_FOC_ORDER value replaces the value of the ORDER attributes in each Connection Manager's configuration file.

    If server_1 fails, the Connection Managers attempt failover to the SD secondary server. If the SD secondary server is also unavailable, the Connection Managers attempt failover to the HDR secondary server.

  7. Optional: Configure the cmalarmprogram script on each Connection Manager host.
    Event alarms can be sent to specified email addresses.
  8. Add entries to the sqlhosts files on server_1 and server_2's host and on server_3's host.
    #dbservername  nettype    hostname   servicename   options
     server_1      onsoctcp   host_1     port_1
     server_2      onsoctcp   host_1     port_2
     server_3      onsoctcp   host_2     port_3
    
  9. Create a sqlhosts file on each Connection Manager.
    #dbservername  nettype    hostname   servicename   options
     cluster_1     group      -          -             c=1,e=server_3
     server_1      onsoctcp   host_1     port_1        g=cluster_1
     server_2      onsoctcp   host_1     port_2        g=cluster_1
     server_3      onsoctcp   host_2     port_3        g=cluster_1

    If a Connection Manager restarts after a primary-server failure, it is able to connect to other database servers in the cluster because the cluster_1 group is defined.

  10. Create a sqlhosts file on each client host.
    #dbservername  nettype    hostname   servicename   options
     oltp          group      -          -             c=1,e=oltp_2
     oltp_1        onsoctcp   cm_host_1  cm_port_1     g=oltp
     oltp_2        onsoctcp   cm_host_2  cm_port_2     g=oltp
    
     report        group      -          -             c=1,e=report_2
     report_1      onsoctcp   cm_host_1  cm_port_3     g=report
     report_2      onsoctcp   cm_host_2  cm_port_4     g=report
     
     payroll       group      -          -             c=1,e=payroll_2
     payroll_1     onsoctcp   cm_host_1  cm_port_5     g=payroll
     payroll_2     onsoctcp   cm_host_2  cm_port_6     g=payroll

    If a Connection Manager fails, client applications can still connect to the other Connection Manager because the oltp, report, and payroll groups are defined.

    • CONNECT TO @oltp connection requests are directed through one of the Connection Managers to the primary server.
    • CONNECT TO @payroll connection requests are directed through one of the Connection Managers to whichever of the primary and HDR secondary servers has the lowest workload.
    • CONNECT TO @report connection requests are directed through one of the Connection Managers to the secondary server that has the lowest workload.
  11. Set each INFORMIXSQLHOSTS environment variable to the sqlhosts file location by running the setenv command on each Connection Manager and client host.
    setenv INFORMIXSQLHOSTS path_and_file_name
  12. Run the oncmsm utility on each Connection Manager host, to start each Connection Manager.
    On the host of connection_manager_1:
    oncmsm -c cm_1.cfg
    On the host of connection_manager_2:
    oncmsm -c cm_2.cfg
  13. Check each Connection Manager's log file to verify that the Connection Manager started correctly.