Defining sqlhosts information for connection management of grids and replicate sets that use secure ports

You must define sqlhosts network-connectivity information for connection management of replicate sets or grids. 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.

Procedure

On the host of each Connection Manager and replication server, create sqlhosts file entries for each replication server, and specify the s=6 secure-port option.

For example:

#dbservername  nettype   hostname   servicename  options
 server_1      onsoctcp  host_1     port_1       s=6
 server_2      onsoctcp  host_2     port_3       s=6
 server_3      onsoctcp  host_3     port_5       s=6
 server_4      onsoctcp  host_4     port_7       s=6

On the host of each Connection Manager and replication server, create a sqlhosts file alias entry for each replication server.

For example:

#dbservername  nettype   hostname  servicename  options
 server_1      onsoctcp  host_1    port_1       s=6
 a_server_1    onsoctcp  host_1    port_2

 server_2      onsoctcp  host_2    port_3       s=6
 a_server_2    onsoctcp  host_2    port_4

 server_3      onsoctcp  host_3    port_5       s=6
 a_server_3    onsoctcp  host_3    port_6

 server_4      onsoctcp  host_4    port_7       s=6
 a_server_4    onsoctcp  host_4    port_8

The aliases are used by the cdr utility, which cannot connect to a secure port.

On the host of each Connection Manager and replication server, create a sqlhosts file group entry for each replication server and alias pair. Use the i=unique_number group-entry option to assign an identifier to the group for Enterprise Replication. Add group options to each replication server and alias entry. 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_server_1    group     -          -            i=1,e=a_server_1
 server_1      onsoctcp  host_1     port_1       g=g_server_1,s=6
 a_server_1    onsoctcp  host_1     port_2       g=g_server_1

 g_server_2    group     -          -            i=2,e=a_server_2
 server_2      onsoctcp  host_2     port_3       g=g_server_2,s=6
 a_server_2    onsoctcp  host_2     port_4       g=g_server_2

 g_server_3    group     -          -            i=3,e=a_server_3
 server_3      onsoctcp  host_3     port_5       g=g_server_3,s=6
 a_server_3    onsoctcp  host_3     port_6       g=g_server_3

 g_server_4    group     -          -            i=4,e=a_server_4
 server_4      onsoctcp  host_4     port_7       g=g_server_4,s=6
 a_server_4    onsoctcp  host_4     port_8       g=g_server_4

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.

 g_server_1  a_server_1  user_1  my_password_1
 server_1    a_server_1  user_1  my_password_1
 a_server_1  a_server_1  user_1  my_password_1

 g_server_2  a_server_2  user_2  my_password_2
 server_2    a_server_2  user_2  my_password_2
 a_server_2  a_server_2  user_2  my_password_2

 g_server_3  a_server_3  user_3  my_password_3
 server_3    a_server_3  user_3  my_password_3
 a_server_3  a_server_3  user_3  my_password_3

 g_server_4  a_server_4  user_4  my_password_4
 server_4    a_server_4  user_4  my_password_4
 a_server_4  a_server_4  user_4  my_password_4

In each replication server's onconfig file, set the DBSERVERALIASES parameter to that database server's aliases.
The onconfig file entry for server_1:
```
DBSERVERALIASES a_server_1
```
The onconfig file entry for server_2:
```
DBSERVERALIASES a_server_2
```
The onconfig file entry for server_3:
```
DBSERVERALIASES a_server_3
```
The onconfig file entry for server_4:
```
DBSERVERALIASES a_server_4
```

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 g_server_1,g_server_2,g_server_3,g_server_4
   SLA sla_1 DBSERVERS=ANY
}

The second Connection Manager's configuration file has the following entries:

NAME connection_manager_2

REPLSET my_replset
{
   ONEDB_SERVER g_server_1,g_server_2,g_server_3,g_server_4
   SLA sla_2 DBSERVERS=ANY
}

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         group     -          -            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 the primary server.