Defining sqlhosts information for connection management of high-availability replication systems that use secure ports

You must define sqlhosts network-connectivity information for connection management of high-availability replication systems. If Connection Managers, database servers, or client applications are outside of a trusted network, you must also create an encrypted password file for security.

About this task

To use a file other than $ONEDB_HOME/etc/sqlhosts on a specific host, set the host's ONEDB_ SQLHOSTS environment variable to the alternative file.

For this example, you are setting up Enterprise Replication between the primary servers of two high-availability clusters.

Cluster 1:
  • server_1 (primary)
  • server_2 (SD secondary)
  • server_3 (HDR secondary)
  • server_4 (RS secondary)
Cluster 2:
  • server_5 (primary)
  • server_6 (SD secondary)
  • server_7 (HDR secondary)
  • server_8 (RS secondary)

Procedure

  1. On the host of each Connection Manager and database server, create sqlhosts file entries for each database server.
    For example:
    #dbservername  nettype   hostname  servicename  options
     server_1      onsoctcp  host_1    port_1       s=6
     server_2      onsoctcp  host_1    port_2       s=6
     server_3      onsoctcp  host_2    port_3       s=6
     server_4      onsoctcp  host_3    port_4       s=6
    
     server_5      onsoctcp  host_4    port_5       s=6
     server_6      onsoctcp  host_4    port_6       s=6
     server_7      onsoctcp  host_5    port_7       s=6
     server_8      onsoctcp  host_6    port_8       s=6
    
    
  2. On the host of each Connection Manager and database server, create a sqlhosts file alias entry for each database server.
    For example:
    #dbservername  nettype   hostname  servicename  options
     server_1      onsoctcp  host_1    port_1       s=6
     a_server_1    onsoctcp  host_1    port_9
     
     server_2      onsoctcp  host_1    port_2       s=6
     a_server_2    onsoctcp  host_1    port_10
     
     server_3      onsoctcp  host_2    port_3       s=6
     a_server_3    onsoctcp  host_2    port_11
     
     server_4      onsoctcp  host_3    port_4       s=6
     a_server_4    onsoctcp  host_3    port_12
    
     server_5      onsoctcp  host_4    port_5       s=6
     a_server_5    onsoctcp  host_4    port_13
    
     server_6      onsoctcp  host_4    port_6       s=6
     a_server_6    onsoctcp  host_4    port_14
    
     server_7      onsoctcp  host_5    port_7       s=6
     a_server_7    onsoctcp  host_5    port_15
    
     server_8      onsoctcp  host_6    port_8       s=6
     a_server_8    onsoctcp  host_6    port_16
    
    The aliases are used by the cdr utility, which cannot connect to a secure port.
  3. On the host of each Connection Manager and database server, create a sqlhosts file group entry for each cluster. Add group options to each database server entry. Use the i=unique_number group-entry option to assign an identifier to the group for Enterprise Replication. Use the c=1 group-entry option for cluster groups, so that connection-attempt starting points in the list of group members is random. Use the e=last_member group-entry option so that the entire sqlhosts is not scanned for group members.
    For example:
    #dbservername  nettype   hostname  servicename  options
     cluster_1     group     -          -           i=1,c=1,e=a_server_4
     server_1      onsoctcp  host_1    port_1       s=6,g=cluster_1
     a_server_1    onsoctcp  host_1    port_9       g=cluster_1
     server_2      onsoctcp  host_1    port_2       s=6,g=cluster_1 
     a_server_2    onsoctcp  host_1    port_10      g=cluster_1
     server_3      onsoctcp  host_2    port_3       s=6,g=cluster_1
     a_server_3    onsoctcp  host_2    port_11      g=cluster_1
     server_4      onsoctcp  host_3    port_4       s=6,g=cluster_1
     a_server_4    onsoctcp  host_3    port_12      g=cluster_1
    
     cluster_2     group     -          -           i=1,c=1,e=a_server_8
     server_5      onsoctcp  host_4    port_5       s=6,g=cluster_2
     a_server_5    onsoctcp  host_4    port_13      g=cluster_2
     server_6      onsoctcp  host_4    port_6       s=6,g=cluster_2
     a_server_6    onsoctcp  host_4    port_14      g=cluster_2
     server_7      onsoctcp  host_5    port_7       s=6,g=cluster_2
     a_server_7    onsoctcp  host_5    port_15      g=cluster_2
     server_8      onsoctcp  host_6    port_8       s=6,g=cluster_2
     a_server_8    onsoctcp  host_6    port_16      g=cluster_2
    
    A password file that is encrypted through the onpassword utility is required for connectivity through secure ports. The entries in the previously shown sqlhosts file are represented in the following password file.
    cluster_1   a_server_1  user_1  password_1
    cluster_1   a_server_2  user_2  password_2
    cluster_1   a_server_3  user_3  password_3
    cluster_1   a_server_4  user_4  password_4
    
    cluster_2   a_server_5  user_5  password_5
    cluster_2   a_server_6  user_6  password_6
    cluster_2   a_server_7  user_7  password_7
    cluster_2   a_server_8  user_8  password_8
    
    server_1    a_server_1  user_1  password_1
    server_2    a_server_2  user_2  password_2
    server_3    a_server_3  user_3  password_3
    server_4    a_server_4  user_4  password_4
    server_5    a_server_5  user_5  password_5
    server_6    a_server_6  user_6  password_6
    server_7    a_server_7  user_7  password_7
    server_8    a_server_8  user_8  password_8
    
    a_server_1  a_server_1  user_1  password_1
    a_server_2  a_server_2  user_2  password_2
    a_server_3  a_server_3  user_3  password_3
    a_server_4  a_server_4  user_4  password_4
    a_server_5  a_server_5  user_5  password_5
    a_server_6  a_server_6  user_6  password_6
    a_server_7  a_server_7  user_7  password_7
    a_server_8  a_server_8  user_8  password_8
    
  4. In each database server's onconfig file, set the DBSERVERALIASES parameter to that database server's aliases.
    For example:
    The onconfig file entry for server_1:
    DBSERVERALIASES a_server_1
  5. On the host of each client application, create an sqlhosts file entry for each service-level agreement (SLA) in each Connection Manager configuration file.
    The first Connection Manager's configuration file has the following entries:
    NAME connection_manager_1
    
    REPLSET my_replset
    {
       ONEDB_SERVER cluster_1,cluster_2
       SLA sla_1 DBSERVERS=ANY
    }
    
    CLUSTER my_cluster_1
    {
       ONEDB_SERVER cluster_1
       FOC ORDER=ENABLED \
           PRIORITY=1
    }
    
    CLUSTER my_cluster_2
    {
       ONEDB_SERVER cluster_2
       FOC ORDER=ENABLED \
           PRIORITY=1
    }
    The second Connection Manager's configuration file has the following entries:
    NAME connection_manager_2
    
    REPLSET my_replset
    {
       ONEDB_SERVER cluster_1,cluster_2
       SLA sla_2 DBSERVERS=ANY
    }
    
    CLUSTER my_cluster_1
    {
       ONEDB_SERVER cluster_1
       FOC ORDER=ENABLED \
           PRIORITY=2
    }
    
    CLUSTER my_cluster_2
    {
       ONEDB_SERVER cluster_2
       FOC ORDER=ENABLED \
           PRIORITY=2
    }
    Add the following entries to each client application's host sqlhosts file:
    #dbservername  nettype   hostname   servicename  options
     sla_1         onsoctcp  cm_host_1  cm_port_1
     sla_2         onsoctcp  cm_host_2  cm_port_2
  6. On the host of each client application, create sqlhosts file group entries for each group of SLA entries, and add group options to the SLA entries. Use the c=1 group-entry option, so that connection-attempt starting points in the list of group members is random. Use the e=last_member group-entry option so that the entire sqlhosts is not scanned for group members.
    For example:
    #dbservername  nettype   hostname   servicename  options
     g_sla         onsoctcp  -          -             c=1,e=sla_2
     sla_1         onsoctcp  cm_host_1  cm_port_1     g=g_sla
     sla_2         onsoctcp  cm_host_2  cm_port_2     g=g_sla
    

Results

Client connection requests to @g_sla are directed to one of the Connection Managers. If connection_manager_1 receives the request, it uses sla_1 to provide the client application with connection information for the primary server. If connection_manager_2 receives the request, it uses sla_2 to provide the client application with connection information for a replication server.