Connection manager configuration

In this example, we will configure two connection managers to monitor HDR cluster, and support client connection routing and automated failover functionality.

We will configure OLTP SLA group to redirect clients to current primary server and REPORT SLA group to redirect reporting applications to HDR secondary server.

SQLHOSTS file for the OneDB Primary + HDR cluster:

$ cat $ONEDB_ SQLHOSTS
g_cluster  group     -            -   
# primary server connection details:
primary  onsoctcp  pri_machine    pri_port g=g_cluster
#HDR server connection details
hdr      onsoctcp  hdr_machine    hdr_port g=g_cluster

Configuration file for first connection manager (cm1):

$cat $ONEDB_HOME/etc/cm1.cfg
NAME     cm1
LOGFILE /opt/onedb/cm1.log
CLUSTER my_cluster1
{
  ONEDB_SERVER pri,hdr
  SLA oltp1 DBSERVERS=primary
  SLA report1 DBSERVERS=HDR,primary
  FOC ORDER=ENABLED TIMEOUT=5 PRIORITY=1
  CMALARMPROGRAM $ONEDB_HOME/etc/cmalarmprogram.sh
}

SQLHOSTS file for cm1 connection manager

$ cat $ONEDB_ SQLHOSTS
# primary server connection details:
primary  onsoctcp  pri_machine    pri_port
#HDR server connection details
hdr      onsoctcp  hdr_machine    hdr_port
#host and port number details SLAs 
oltp1    onsoctcp  cm1_machine    cm1_port1
report1  onsoctcp  cm1_machine    cm1_port1

Command to start connection manager

$ oncmsm -c $ONEDB_HOME/etc/cm1.cfg

Check connection manager log /opt/onedb/cm1.log file to verify connection manager started correctly. Also verify connection manager status from primary and HDR servers using onstat command ‘onstat -g cmsm’.

Configuration file for second connection manager (cm2)

$cat $ONEDB_HOME/etc/cm2.cfg
NAME     cm2
LOGFILE /opt/onedb/cm2.log
CLUSTER my_cluster2
{
  ONEDB_SERVER pri,hdr
  SLA oltp2 DBSERVERS=primary
  SLA report2 DBSERVERS=HDR,primary
  FOC ORDER=ENABLED TIMEOUT=5 PRIORITY=2
 CMALARMPROGRAM $ONEDB_HOME/etc/cmalarmprogram.sh
}

SQLHOSTS file for cm2 connection manager

$ cat $ONEDB_ SQLHOSTS
# primary server connection details:
primary  onsoctcp  pri_machine    pri_port
#HDR server connection details
hdr      onsoctcp  hdr_machine    hdr_port
#host and port number details SLAs 
oltp2    onsoctcp  cm2_machine    cm2_port1
report2  onsoctcp  cm2_machine    cm2_port1

Command to start cm2 connection manager

$ oncmsm -c $ONEDB_HOME/etc/cm2.cfg

Check connection manager log /opt/onedb/cm2.log file to verify connection manager started correctly. Also verify connection manager status from primary and HDR servers using onstat command ‘onstat -g cmsm’.

SQLHOSTS file for client applications

$ cat $ONEDB_ SQLHOSTS
# oltp SLA group
oltp             group     -              -              c=1
oltp1            onsoctcp  cm1_machine    cm1_port1      g=oltp
oltp2            onsoctcp  cm2_machine    cm2_port2      g=oltp
# report SLA group
report             group     -              -              c=1
report1            onsoctcp  cm1_machine    cm1_port1      g=report
report2            onsoctcp  cm2_machine    cm2_port2      g=report

Applications connecting to “oltp” group gets redirected to primary server in the high availability cluster, and applications connecting to “report” group gets redirected to secondary server, and if secondary server isn’t reachable then “report” applications gets redirected to primary server.