How to set up a secondary login broker for SQL-92 connections for Progress 9.1D and above

por | 23 septiembre, 2013
or Progress 9.1D and later, the method for using the secondary login broker for SQL connections has changed.

Databases that have both Progress ABL and SQL clients simultaneously connected should use a secondary login broker to avoid the following limitations caused by the use of a single broker:

– The need to establish the maximum number of remote ABL and SQL connections
– The need to allocate a specific pool of remote servers for ABL ( _mprosrv ) and SQL connections ( _sqlsrv2 )
– The need to specify a different range of TCP/IP ports for SQL remote servers

To start configuring a second login broker requires:

1. Define the number of remote servers for the database specified by the -Mn parameter using the formula:
-Mn = maximum ABL servers + maximum SQL Servers + number of Secondary Login Brokers .
The extra broker is added because each additional login broker will consume an entry on the server list, so for each broker started with the -m3 parameter add 1.

2. Define the total number of processes that will connect to the database using the formula:
-n = all processes that connect concurrently to the database including but not limited to:
PROMON sessions, APWs, BIWs, AIWs, PROWDOG, background writers, batch jobs, self-service and remote user connections.

Ensure that these values are balanced once set where:
-n >= sum(-Mpb x -Ma) for each login broker.

Optionally: -ServerType parameter was added in 9.1D to define the type of servers that each login broker will start.
The value can be 4GL, SQL, or Both.
Specifying 4GL indicates that the login broker will only support 4GL servers.
Specifying SQL indicates that the login broker will only support SQL servers.
Both indicates that the login broker supports both ABL and SQL servers.
If OpenEdge Management or OpenEdge Replication is being used, then the ABL Broker is required to be started as the primary login broker for the database.

In 9.1D and above the SQL engine is multithreaded. Due to this change SQLREUSE=false is no longer supported and the recommended settings for -Ma and -Mi should be set as follows:

-Mi database broker startup parameter is used to specify the number of remote users connected to a server before the broker spawns another server. For the SQL Server it is recommended to set the -Mi parameter to the same value as the -Ma parameter to reduce memory consumption if all the remote servers are not needed .
If performance is an issue test setting the -Ma for SQL to 5 and the -Ma for ABL to 10. These are starting recommendations to be adjusted up or down to achieve the desired resource allocation.

These parameters may be used on the command line or changed in the conmgr.properties file if the databases are managed under the AdminServer. Progress recommends using the Explorer tool to make the changes to the conmgr.properites file.

To configure database use Progress/OpenEdge Explorer tool or manually add each parameters to the startup of the database scripts.

To start the brokers using command line:

a) The first broker will be the login broker for the ABL clients.

In addition to the regular parameters of the PROSERVE command, specify:
+ -S <portnumber/name> (for the ABL clients to connect to the database),
+ -Mn (the total number or remote servers and secondary login brokers)
+ -Mpb (Maximum remote server per protocol, in this case the number of ABL remote servers for ABL remote clients to communicate with).
+ -Ma (to limit the number of the ABL remote users per remote server -Mpb)
+ -minport and -maxport (the port range >= -Mpb for the remote servers to listen on)
+ -Mi (to specify how many ABL connections must be concurrently connected to an existing remote server before the login broker spawns a new remote server)
+ -ServerType 4GL (to ensure only ABL connections take up the -Mpb remote server slots)

b) After starting the first broker, start a second broker for the SQL clients using another PROSERVE command specifying:
+ -m3 parameter to the command line to instruct that this is an additional login broker.
+ -S <portnumber/name> (for the SQL clients to connect to the database),
+ -Mpb (Maximum remote server per protocol, in this case the number of SQL remote servers for SQL remote clients to communicate with).
+ -Ma (to limit the number of the SQL remote users per remote server -Mpb)
+ -minport and -maxport (the port range >= -Mpb for the remote servers to listen on, )
+ -Mi (to specify how many SQL connections must be concurrently connected to an existing remote server before the login broker spawns a new remote server)
+ -ServerType SQL (to ensure only SQL connections take up the -Mpb remote server slots)

The following is an example of a database set for 65 users (40 4GL and 25 SQL). The first broker will attend ABL connections on port 9000 and the second broker will attend the SQL connections on port 8000.

proserve <dbname> -n 65 -Mn 10 -Mpb 4 -Ma 10 -Mi 3 -S 9000 -minport 9001 -maxport 9004 -ServerType 4GL
proserve <dbname> -m3 -Mpb 5 -Ma 5 -Mi 5 -S 8000 -minport 8001 -maxport 8005 -ServerType SQL

Database startup parameters such as -B, -L, -hash, -H, etc must be included for the primary login broker only and must not be repeated when starting the secondary login brokers.

To configure the brokers to be started by the AdminServer:

From the OpenEdge Console the following map the above parameters (and descriptions) specified above for command line configuration:

configuration.dbname.defaultconfiguration

General
(-n)    Max users:     65
(-Mn)    Max servers:   10

servergroup.dbname.defaultconfiguration.ABL

General
(-S )             Service name or port:     9000
(-Mpb)             Number of servers:        4
(-ServerType)     Client Type:              4GL

Advanced
(-Ma)         Maximum clients per server:   10
(-Mi)         Minimum clients per server:   3
(-maxport)     Maximum dynamic port:     9004
(-minport)     Minimum dynamic port:     9001

servergroup.dbname.defaultconfiguration.SQL

General
(-S )             Service name or port:     8000
(-Mpb)             Number of servers:        5
(-ServerType)     Client Type:              SQL

Advanced
(-Ma)         Maximum clients per server:   5
(-Mi)         Minimum clients per server:   5
(-maxport)     Maximum dynamic port:     8005
(-minport)     Minimum dynamic port:     8001

Workaround
Notes
Attachment
Disclaimer The origins of the information on this site may be internal or external to Progress Software Corporation (“Progress”). Progress Software Corporation makes all reasonable efforts to verify this information. However, the information provided is for your information only. Progress Software Corporation makes no explicit or implied claims to the validity of this information.

Any sample code provided on this site is not supported under any Progress support program or service. The sample code is provided on an «AS IS» basis. Progress makes no warranties, express or implied, and disclaims all implied warranties including, without limitation, the implied warranties of merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance of the sample code is borne by the user. In no event shall Progress, its employees, or anyone else involved in the creation, production, or delivery of the code be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the sample code, even if Progress has been advised of the possibility of such damages