How to set up a secondary login broker to attend SQL-92 connections for Progress 9.1C and below

How to set up a secondary login broker for SQL-92 connections for Progress 9.1A, 9.1B and 9.1C
Creating a secondary login broker for SQL-92
How to configure secondary login brokers?
How to start multiple login brokers for a database?
How to spawn multiple SQL brokers?

This Article applies to Progress 9.0x, Progress 9.1A, 9.1B and 9.1C. A secondary login broker is used to address following issues which cannot be solved by having only a single login broker:

The need to establish the maximum number of remote 4GL and SQL-92 connections.
The need to allocate a specific pool of remote servers for 4GL (_mprosrv) and SQL-92 connections (_sqlsrv2).
The need to specify a different range of TCP/IP ports for SQL-92 remote servers.
The exhaustion of remote servers caused by extra SQL-92 connections.

To configure a second login broker first define the total number of remote servers that the database will use.  Use the formulas below to calculate the total number of remote servers for the database (the value for the parameter -Mn) and the maximum number of concurrent connected processes ( users) :

-Mn = Max. 4GL servers + Max. SQL-92 servers + number of Secondary Login Brokers

-n = includes all local users, remote users, background writers (APWs, BIWs, and AIWs), PROWDOG processes, PROMON sessions, plus 1 for each secondary login broker.

For versions 9.1C and prior:
- The maximum number of SQL-92 servers is the maximum number of SQL-92 clients.
- Remote SQL-92 servers can handle only one connection at a time.
- The primary broker has to be used for SQL-92 connections, because the polling feature of the SQL-92 servers is only active for the primary broker.

Configure the startup parameters on the database use the Progress Explorer tool or add the parameters to the database startup command.


To use the Explorer tool:

1. At database configuration (defaultConfiguration), select Properties.

2. Select the General option and insert the configured values into the " Maximum users " field  and the " Maximum servers " field  calculated from the above formula. 
The " Maximum users " field equates to the  ( -n ) startup parameter and is equals the total number of concurrent connections configured for the database.  
The " Maximum servers " field  equates to the ( -Mn ) startup parameter and equals the total number of remote servers.

3. Select "Properties" in the default server group ( defaultServerGroup ) created for the database. Use this configuration for the SQL-92 broker.

4. Define the port number for the broker and the total number of SQL-92 users in the " Number of Servers " field.

5. Right click on the defaultConfiguration and select the "New" option to add another server group ( the default is ServerGroup-1, rename it as required ).

6.  Define the port number for the second login broker. This will be the broker for 4GL connections.

7. Set the maximum number of 4GL servers in the " Number of Servers " field and the " Maximum clients per server ". These two numbers limit the number of remote 4GL connections allowed for this database.

8.  A different range of TCP/IP ports for the remote servers to the primary login broker can be configured by changing the values of the  " Maximum dynamic port " and " Minimum dynamic port " fields.

Both server groups start automatically when the database is started.

To modify the database startup parameter in database startup scripts:

1. In addition to the regular parameters of the proserve command, add the -Mn (with the value calculated by the formula), -Ma ( to limit the number of SQL-92 remote users per remote server), and -Mpb ( maximum remote servers per protocol ). The -Mpb parameter is used to set the number of remote servers per broker type out of the total remote servers (-Mn) pool. 

2. Issue the proserve command above to start the first broker for SQL connections.

3. After the first broker is started, start a second broker using -m3 parameter (for second login broker). Use -N, -S –Mpb and -Ma for the number of remote 4GL users.

Example:  The command line to start a database having 50 users (40 4GL and 10 SQL-92). 

• The primary login broker will attend 10 client server connections where SQL-92 users connect to the database on port 9000 and
• The secondary login broker will attend 40 client server connections where 4GL users connection to the database on port 8000.

$   proserve dbname -n 51 -Mn 21 -Mpb 10 -Ma 1 -N tcp -S 9000
$   proserve dbname -m3 -Mpb 10 -Ma 4 -N tcp -S 8000

To specify a different port range for the remote servers, add -minport and -maxport on the startup of the broker.

$   proserve dbname -n 51 -Mn 21 -Mpb 10 -Ma 1 -N tcp -S 9000 -minport 3000 -maxport 3010
$   proserve dbname -m3 -Mpb 10 -Ma 4 -N tcp -S 8000 -minport 3050 -maxport 3090