Defining sqlhosts information for connection management high-availability replication systems

You must define sqlhosts network-connectivity information for connection management of high-availability replication systems.

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 three 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)
Cluster 3:
  • server_9 (primary)
  • server_10 (SD secondary)
  • server_11 (HDR secondary)
  • server_12 (RS secondary)

Procedure

  1. On the host of each Connection Manager and database server, create sqlhosts file entries for each server.
    For example:
    #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
    server_4       onsoctcp  host_3     port_4
    
    server_5       onsoctcp  host_4     port_5
    server_6       onsoctcp  host_4     port_6
    server_7       onsoctcp  host_5     port_7
    server_8       onsoctcp  host_6     port_8
    
    server_9       onsoctcp  host_7     port_9
    server_10      onsoctcp  host_7     port_10
    server_11      onsoctcp  host_8     port_11
    server_12      onsoctcp  host_9     port_12
    
  2. On the host of each Connection Manager and database server, create a sqlhosts file group entry for each replication-server entry and 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=server_4
     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
     server_4      onsoctcp  host_3     port_4       g=cluster_1
    
     cluster_2     group     -          -            i=2,c=1,e=server_8
     server_5      onsoctcp  host_4     port_5       g=cluster_2
     server_6      onsoctcp  host_4     port_6       g=cluster_2
     server_7      onsoctcp  host_5     port_7       g=cluster_2
     server_8      onsoctcp  host_6     port_8       g=cluster_2
    
     cluster_3     group     -          -            i=3,c=1,e=server_12
     server_9      onsoctcp  host_7     port_9       g=cluster_3
     server_10     onsoctcp  host_7     port_10      g=cluster_3
     server_11     onsoctcp  host_8     port_11      g=cluster_3
     server_12     onsoctcp  host_9     port_12      g=cluster_3
    
  3. 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,cluster_3
       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
    }
    
    CLUSTER my_cluster_3
    {
       ONEDB_SERVER cluster_3
       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,cluster_3
       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
    }
    
    CLUSTER my_cluster_3
    {
       ONEDB_SERVER cluster_3
       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
    
  4. 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.