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

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

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

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

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.