Kbase P7843: How to set up a secondary login broker for SQL-92 connections for Progress 9.1D and above
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
4/27/2011 |
|
Status: Verified
GOAL:
How to set up a secondary login broker for SQL-92 connections for Progress 9.1D and above
GOAL:
How to configure a secondary SQL-92 Broker for Progress 9.1D and above
GOAL:
How to spawn multiple brokers for the database
GOAL:
How to configure multiple brokers for the database
GOAL:
How to configure separated broker for SQL-92 and 4GL connections?
GOAL:
How to know what to set for -Mn, -Ma and -Mpb using secondary login broker
GOAL:
How to configure a primary and secondary database broker in progress Explorer
GOAL:
How to configure a second broker using Progress Explorer Tool
GOAL:
How to start up a SQL and 4GL broker of a database
GOAL:
Where to enter the -Mpb, -Ma and -Mi parameters for a primary and secondary database broker in Progress Explorer
GOAL:
How to properly set the database to receive sql-92 connections
FACT(s) (Environment):
All Supported Operating Systems
Progress 9.1D
Progress 9.1E
OpenEdge 10.x
FIX:
For Progress 9.1D and later, the method for using the secondary login broker for SQL-92 connections has changed.
Databases that have both Progress 4GL and SQL-92 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 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
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 4GL servers + maximum SQL Servers + number of Secondary Login Brokers .
2. Define the total number of processes that will connect to the database using the formula:
-n = all local processes including: PROMON sessions, APWs, BIWs, AIWs, PROWDOG, background writers, batch jobs, and remote user connections, + 1 for each additional login broker.
The extra broker is added because each additional login broker will consume an entry on the server list. For each broker started with the -m3 parameter add 1.
In 9.1D and above the SQL-92 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-92 Server it is recommended to set the -Mi parameter to the same value as the -Ma parameter to reduce memory consumption if all the servers are not needed.
If performance is an issue test setting the -Ma for SQL-92 to 5 and the -Ma for 4GL to 10. These are starting recommendations to be adjusted up or down to achieve the desired resource allocation.
The default value for the -Ma parameter is 5 and -n parameter is 21. Set these according to support the desired configuration.
-ServerType parameter was added in 9.1D to define the type of servers that each broker will start.
The value can be 4GL, SQL, or Both.
Specifying 4GL indicates that the broker will only support 4GL servers.
Specifying SQL indicates that the broker will only support SQL-92 servers.
Both indicates that the broker supports both 4GL and SQL-92 servers.
This parameter may be used on the command line or changed in the conmgr.properties file.
If Fathom Management or Fathom Replication is being used the 4GL Broker is required to be started as the primary broker for the database.
Progress recommends using the Explorer tool to make the changes to the conmgr.properites file.
To configure database use Progress Explorer tool or manually add each parameters to the startup of the database scripts.
_______________________________________________________________________________
To configure the brokers using Progress Explorer:
a) At database configuration (defaultConfiguration) and select Properties.
b) In the "General" option, set the "Maximum users" field (-n) which is the total number of users on the database and the "Maximum servers" field (-Mn) which is the total number of remote servers calculated from the above formula. Click OK to save.
c) Right click on the defaultServerGroup and select Properties.
d) In the "General" option, define the port number (-S) for this broker, set the number of 4GL servers to be used in the "Number of Servers" field (-Mpb) and select 4GL only for "Client type".
e) In the "Advanced" option, -Ma is set in the "Maximum clients per server" field and -Mi is set in the. "Minimum clients per server" field.
f) Click OK to save.
g) Right click on the defaultConfiguration and select the "New" option to add another server group (the default name is ServerGroup-1).
h) In the "General" option, define the port number (-S) for this broker, set the number of SQL Servers to be used in the "Number of Servers" field (-Mpb) and select SQL only for "Client type".
i) In the "Advanced" option, set -Ma is set in the "Maximum clients per server" field and -Mi is set in the "Minimum clients per server" field. This will be the broker for SQL-92 connections.
Optionally, set "Maximum dynamic port" (-maxport) and "Minimum dynamic port" (-minport) fields to change the range of TCP/IP ports to be used for the remote servers as necessary.
g) Click OK to save.
Both server groups will automatically start when the database is started.
_______________________________________________________________________________
To start the brokers using command line:
a) The first broker will be the connection broker for the 4GL clients.
In addition to the regular parameters of the PROSERVE command, specify the -N, -S, -Ma (to limit the number of the 4GL remote users) and -Mpb (Maximum remote server per protocol, in this case the number of 4GL remote servers).
b) After starting the first broker, start a second broker for the SQL-92 clients using another PROSERVE command adding the -m3 parameter (for second login broker).
Also use -N, -S, -Ma (to limit the number of the SQL remote users) and -Mpb (Maximum remote server per protocol, in this case the number of SQL remote servers).
The following is an example of a database set for 65 users (40 4GL and 25 SQL-92). The first broker will attend 4GL connections and the second broker will attend the SQL-92 connections.
proserve sp2k -n 66 -Mn 10 -Ma 10 -Mpb 4 -N tcp -S 9000 -ServerType 4GL
proserve sp2k -m3 -Ma 5 -Mpb 5 -N tcp -S 8000 -ServerType SQL
Add -minport and -maxport on each line to specify a different port range for the remote servers.
Parameters such as -B, -L, -hash, -H, etc should be include for the primary broker only and need not be repeated when starting the additional brokers..