Feedback
 
Did this article resolve your question/issue?

   

Your feedback is appreciated.

Please tell us how we can make this article more useful. Please provide us a way to contact you, should we need clarification on the feedback provided or if you need further assistance.

Characters Remaining: 1025

 


Article

How to set up a secondary login broker for SQL-92 connections for Progress/OpenEdge database?

Information

 
Article Number000021731
EnvironmentProduct: Progress
Version: 9.1D, 9.1E
Product: OpenEdge
Version: 10.x, 11.x
OS: All supported platforms
Other: SQL-92
Question/Problem Description
How to set up a secondary login broker for SQL-92 connections for Progress 9.1D and above
How to configure a secondary SQL-92 Broker for Progress 9.1D and above
How to spawn multiple brokers for the database
How to configure multiple brokers for the database
How to configure separated broker for SQL-92 and 4GL connections?
How to know what to set for -Mn, -Ma and -Mpb using secondary login broker
How to configure a primary and secondary database broker in progress Explorer
How to configure a second broker using Progress Explorer Tool
How to configure a second broker using the OpenEdge Explorer (OEM) Tool
How to configure a second broker using the OpenEdge Management (OEE) tool
How to start up a SQL and 4GL broker of a database
Where to enter the -Mpb, -Ma and -Mi parameters for a primary and secondary database broker in Progress Explorer
How to properly set the database to receive sql-92 connections
Steps to Reproduce
Clarifying Information
Error Message
Defect/Enhancement Number
Cause
Resolution
For Progress 9.1D and later, the method for using the secondary login broker for SQL connections has changed. For previous versions refer to Article 000001432, How to set up a secondary login broker to attend SQL-92 connections for Progress 9.1C and below  

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 needed because each additional login broker (-m3) will use an entry on the server list, so add 1 for each broker started with the -m3 parameter.
  1. Define the total number of users (processes) that can connect concurrently to the database:
-n
This parameter represents the maximum number of users and processes that can connect concurrently to the database, including but not limited to:
PROMON sessions, APWs, BIWs, AIWs, WDOG, AIMGT, RFUTIL (for AI handling), background writers, batch jobs, self-service and remote user connections.
Ensure that these values are balanced, so that -n >= sum(-Mpb x -Ma) for all login brokers.
  1. -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 ABL, 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 refer to Article
 
Workaround
Notes
Attachment 
Last Modified Date3/26/2019 10:15 AM