No SQL servers are available

[kosj]

I just set up a secondary broker, not sure if i did it correctly. Running progress 9.1c using Syteline version 5. I am creating a bunch of programs using vb.net that access the database through ODBC connection. I created the seconday broker because we were having issues with connections but every once in awhile we still do. When creating the DSN for the ODBC connection should i reference the database or the secondary broker?

 

[Rob Fitzpatrick]

Can you be more specific about "issues with connections"? Does that mean the client doesn't connect?

In your DSN you specify (among other things) the DB server host name, port number, logical database name, and the user id of the DB user that has SQL permissions for the desired access (select, etc.).

"Port number" is the TCP port that the SQL broker is bound to on the database server, i.e. the value specified after the -S broker startup parameter.

If you are getting 8839 errors (which you should see in your database log file), i.e. no SQL servers available, I think the issue is not that you can't connect to the broker, but rather that you can't make the subsequent connection to a SQL server. Also, this could be caused by a bug in older versions of Progress. However the first thing I would check is that your brokers are configured correctly, that the servers that should be running are running, and that there is actually an available login slot for the connecting client on one of those servers.

What are the command lines you use to start your brokers?

 

[kosj]

I don't think I have my parameters set up properly to handle the number of SQL connections. Not sure how to determine how many we need. I am trying to connect to 3 seperate databases through ODBC, should there be a secondary broker for each? Currently have it set for only one (ton.db).
Below are the command lines for starting the brokers

call e:\dlc\bin\PROSERVE -pf e:\syteline\db\ton.pf -Mpb 12
call e:\dlc\bin\PROSERVE e:\syteline\db\ton -S TONSQL -N tcp -H symix -Ma 20 -Mpb 20 -m3

In my services file I have an entry for both brokers. My DSN is only for the primary broker and has the correct host, port number and db name. I have a aspx web page that are vendors log into and retrieve and update the database. Sometimes they cannot because the number of connections has exceeded.

 

[Rob Fitzpatrick]

Your parameter file for the primary broker (e:\syteline\db\ton.pf) contains parameters as well. I also need those.

If you are trying to connect to a Progress database via ODBC, you need to be running a SQL broker for it. Obviously, if the three are on the same machine they will each need to listen on a different port.

Do you want to be able to connect 400 concurrent SQL clients to this database?

 

[kosj]

Paremater file:
-Ma 50
-Mi 7
-Mn 21
-n 61
-L 20000
-TB 4
-TM 15
-inp 5000
-Mm 1024
-mmax 16384
-D 150
-n 180
-rand 2
-lkwtmo 60
#
# MAIN,Ton
-db E:\syteline\db\TON
-B 5000
-N TCP
-H symix
-S TON

So I need to have a secondary broker for each database I am trying to connect to? My parameters are all wrong...defiantely don't want 400 concurrent clients. At least 50 concurrent.

 

[Rob Fitzpatrick]

My parameters are all wrong...

I agree with that.

Honestly, I see several issues here, not just related to remote client configuration. I think you could do with some consulting help. (It's not a shameless plug if I say it. )

If I were you, I would look at the Progress docs for your version and read up on database startup parameters. Specifically, look at each of the params you are specifying, what they mean, and what their default values are. Some of your problems should then become apparent, as parameter configuration is a balancing act between several interdependent constraints and requires knowledge of what you want to do and what you can do, given your available resources and licensing.

As an example, the -Mn 21 on your primary broker says "the maximum number of servers plus secondary brokers that this database can spawn is 21". It is global, across all brokers. The -Mpb parameters are per-broker, and specify the maximum number of servers that broker can spawn. So the -Mn value should equal the sum of each broker's -Mpb value plus the total number of secondary brokers, i.e. 12 + 20 + 1 = 33. So the -Mn value of 21 is preventing 11 of those 32 servers from ever being able to run, which would explain the "no servers available" errors you were seeing.

Also, -Ma is another per-broker setting. It is the maximum number of remote clients that can connect to a given server, so that broker's maximum contribution of remote clients is the product of -Ma and -Mpb. In the case of your secondary broker, this is 20 X 20 = 400. However -n (maximum number of database connections, self-service and remote) is only 61, so it will be another constraint on client count long before you hit your theoretical Ma x Mpb limit. And like I said, you also have to factor in what you have actually paid for (i.e. your client and server user counts).

There are some other issues, but it is hard to do a great job of this remotely. Like I said I think that obtaining some outside consulting help would pay dividends for you, and would take a bunch of support issues off your plate (which cost real money) that shouldn't be there in the first place.

 

[kosj]

Hey Rob...just wanted to thank you so much for your time and advice. Went through and reviewed all our parameter files and made changes. So far everything seems to be working much better. Thanks again!!!!

 

[Rob Fitzpatrick]

Cheers. Glad to hear it.