Kbase P143372: How to limit connections to the MS SQL Server through MS SQL Server DataServer?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
29/10/2009 |
|
Status: Verified
GOAL:
How to limit connections to the MS SQL Server through MS SQL Server DataServer?
GOAL:
How to manage connections to the MS SQL Server through MS SQL Server DataServer?
GOAL:
How to limit connections to MS SQL Server database with MS SQL Server DataServer?
FACT(s) (Environment):
Windows
MS SQL DataServer
OpenEdge 10.x
FIX:
OpenEdge OE10.0B MS SQL Server DataServer introduced a -Dsrv parameter called PRGRS_CACHE_CONN,n where n is the number of managed read-only connections. By default, the number of managed connections are set to 5. These read-only connections are managed by OE MS SQL Server DataServer. When a client connects to the MS SQL Server it initially opens up 6 connections - one is for master connection and the other ones are for read-only connections for NO-LOCK queries. If all 5 connections are exhausted by the application and needs more read-only connections, the DataServer requests ODBC_POOL for more. ODBC_POOL is the larger pool managed by the ODBC. All these read-only (managed pool or ODBC pool) connections are used by firehose cursors for NO-LOCK queries. Once opened, these read-only connection never close even though the firehose cursor is closed, therefore, they never go away for the life of a client session, but they are being re-used if needed. As a result, you may see too many connections are opened per client sessions as you run more and more of your applications.
In order to limit the number of connection to the MS SQL Server Database, another -Dsrv parameter is introduced named CONNECTION_POOLING. The ODBC connection pooling is on by default. In order to turn off or disable the ODBC connection pooling, you can use parameter CONNECTION_POOLING,0 with the -Dsrv switch. If the connection pooling is off, only read-only connections will be open is 5, by default. Based on the need for your application, you can then use PRGRS_CACHE_CONN parameter to increase the number of read-only connections. If you disable the ODBC connection pooling, you may notice performance degradation as the NO-LOCK queries needs to wait until one of the 5 read-only connections are available for reuse. Therefore, you need to make sure you have enough read-only connections specified with this parameter. If you have extended logging (-Dsrv qt_debug,extended) on the client connections, you can see the number of read-only connections are being opened and reused on the dataserv.lg file.
Usage of the parameters:
-Dsrv CONNECTION_POOLING,0,PRGRS_CACHE_CONN,20
With the above parameters, ODBC connection pooling will be disabled, and 20 managed read-only connections will be opened during an ABL client connection to the MS SQL Server database. The total connections will be then 21, where one is master connection and the other 20 are read-only connections for firehose cursor.