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
- A primary server (server_1)
- A shared-disk secondary server (server_2)
- An HDR secondary server (server_3)
- 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
- 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
- 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.
- 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.
- On each host Connection Manager host, set the ONEDB_HOME environment
to the directory the Connection Manager was installed into.
Run the following command:
setenv ONEDB_HOME path
- Create a configuration file in each Connection Manager
installation's $ONEDB_HOME/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 $ONEDB_HOME/tmp/my_cm1_log.log LOCAL_IP 192.0.2.0,192.0.2.1 CLUSTER cluster_1 { ONEDB_SERVER 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 $ONEDB_HOME/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 $ONEDB_HOME/tmp/my_cm2_log.log LOCAL_IP 192.0.2.2,192.0.2.3 CLUSTER cluster_1 { ONEDB_SERVER 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 $ONEDB_HOME/etc/CMALARMPROGRAM.sh }
The configuration file specifies the following information and behavior:- Logging is enabled, and the log files are $ONEDB_HOME/tmp/my_cm1_log.log and $ONEDB_HOME/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
andCONNECT TO @oltp_2
connection requests are directed to the primary server.CONNECT TO @payroll_1
andCONNECT TO @payroll_2
connection requests are directed to whichever of the primary and HDR secondary servers has the lowest workload.CONNECT TO @report_1
andCONNECT 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, $ONEDB_HOME/etc/CMALARMPROGRAM.sh is called.
- 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.
- 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. - 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.
- Optional: Configure the cmalarmprogram script
on each Connection Manager host.Event alarms can be sent to specified email addresses.
- 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
- 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.
- 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.
- Set each ONEDB_ SQLHOSTS environment
variable to the sqlhosts file location by running
the setenv command on each Connection Manager and
client host.
setenv ONEDB_ SQLHOSTS path_and_file_name
- 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
- Check each Connection Manager's log file to verify that the Connection Manager started correctly.