Limiting Connections To Progress Database

Hologram

New Member
Hello,

I wonder if anyone can help?

I've written a Microsoft SSIS package to export data from two Progress databases (10.2 and 11.3) that connects via an ODBC 11.3 driver to export data into SQL Server.

However, the Progress administrator contacted me to let me know that on several occasions the SSIS package has created too many connection to the Progress db and they've needed to restart the Progress server to kill all the connections.

From a Progress perspective, is it possible to limit the number of connections a client can make to the database, or similar, to prevent swamping Progress with connection calls?

Many thanks,

Hologram

(ps I originally posted this to the data servers and ODBC forum but perhaps it is more relevant here in the administration forum - apologies for the double post.)
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
the Progress administrator contacted me to let me know that on several occasions the SSIS package has created too many connection to the Progress db and they've needed to restart the Progress server to kill all the connections.
I don't know why it would be necessary to stop the database in order to disconnect clients.

From a Progress perspective, is it possible to limit the number of connections a client can make to the database, or similar, to prevent swamping Progress with connection calls?
No, there are no per-user connection quotas in the OpenEdge database. One possible workaround is to configure a secondary SQL broker on a separate port with settings you are comfortable with (i.e. -Mpb, which is servers per broker, and -Ma, which is clients per server), and reserve its use exclusively for SSIS. So for example if you started a secondary broker like this:

proserve <dbname> -m3 -S <port number/service> -ServerType SQL -Mpb 1 -Ma 3

then SSIS would only be able to connect three SQL clients to that database concurrently, regardless of how hard it tried. Obviously you would have to reconfigure SSIS to point at the new port.

That said, the above is just a workaround for the problem. It seems to me the actual problem is the behaviour of the SQL client (SSIS). There must be a way to configure it to behave in a predictable fashion, where it doesn't use all available connection slots. The right solution, IMHO, would be to address this problem on that side.
 

TomBascom

Curmudgeon
The only reason that I can think of to feel like you need to stop and start the db because of too many connections would be a -ServerType BOTH broker with -Mi set to 1. That causes SQL connections to be spread across as many servers as possible rather than focusing them on just a few servers.

So, like Rob said, configure a dedicated SQL broker and you should be in good shape.
 
Top