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

por | 18 marzo, 2016
0 comentarios

For 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 login 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 ABL and SQL remote servers

To start configuring a Secondary login broker (-m3):

  1. Define the total number of remote servers for the database specified by the -Mn parameter using the formula:
-Mn
Maximum ABL servers (-Mpb) + maximum SQL Servers (-Mpb) + number of Secondary Login Brokers (-m3) .
The extra server is added because each additional login broker (-m3) will use an entry on the server list, so for each broker started with the -m3 parameter add 1.
  1. 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.
  1. Optionally:
-ServerType
This 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.
Specifying «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, so -ServerType needs to be either ABL or BOTH.
In Progress 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
This 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.

When the database is shutdown, all login brokers will be terminated as part of the shutdown process.

To configure database startup parameters use Progress/OpenEdge Explorer tool if databases are managed by the AdminServer or manually add each parameter to the the database startup scripts.

To start the brokers using command line:

  1. In this example, the first broker will be the primary login broker for the ABL clients.
In addition to the existing database startup parameters of the PROSERVE command, specify:
  • -S <portnumber/name> (login broker listening port for ABL clients to connect client/server to the database),
  • -Mn (Total number or remote servers and secondary login brokers)
  • -Mpb (Maximum remote servers 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 (The minimum usable port number)
  • -maxport (The maximum usable port number)
  • -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)
  1. 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> (login broker listening port for 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 (The minimum usable port number)
  • -maxport (The maximum usable port number)
  • -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 configured for 70 concurrent user connections (40 4GL, 25 SQL and 5 ‘other’ defined above).

  • The first login broker will attend ABL connections on port 9000 by spawning remote ABL (_mprosrv) servers to service these on the -minport -maxport range and
  • The second login broker will attend the SQL connections on port 8000 by spawning remote SQL servers (_sqlsrv2) to service these on the -minport -maxport range .
proserve <dbname> -n 70 -Mn 10 -Mpb 4 -Ma 10 -Mi 3 -S 9000 -minport 9001 -maxport 9004 -ServerType 4GL -PendConnTime 30
proserve <dbname> -m3 -Mpb 5 -Ma 5 -Mi 5 -S 8000 -minport 8001 -maxport 8005 -ServerType SQL
Other 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 using OpenEdge Management or OpenEdge Explorer see article 000020228, How to set the values for the startup parameters -Ma, -Mn and -n