SAP Adapter : JCO_ERROR_RESOURCE Connection pool is exhausted

I came across an interesting issue recently faced by one of my colleagues. He was trying to integrate between MSSQL server and SAP R/3 system using SOA 11G. He used to get a batch of order data from MSSQL server and had to post these to SAP BAPI and receive order number in response. Things were working fine as long as the batch size was small. But when he started getting batch size >1000, the SAP adapter started throwing errors.

Exception occured when binding was invoked. Exception occured during invocation of JCA binding: "JCA Binding execute of Reference operation failed due to: JCA Binding Component connection issue. JCA Binding Component is unable to create an outbound JCA (CCI) connection. The JCA Binding Component was unable to establish an outbound JCA CCI connection due to the following issue: java.lang.IllegalStateException: Problem activating adapter. (java.lang.IllegalArgumentException: com.ibi.sap.SapAdapterException: com.sap.mw.jco.JCO$Exception: (106) JCO_ERROR_RESOURCE: Connection pool c22bbdb28dd27119d92c36c8ce75ad9e_p1 is exhausted. The current pool size limit (max connections) is 10 connections.). Check logs for more information ".

The default connection pool size for SAP adapter is 2 and this can be changed from the Application explorer. As can be seen above the connection pool size was increased to 10 but still this had failed. Basically when 10 concurrent threads are running and 11th thread tries to fetch the connection and unable to get it, that particular instance of BPEL fails with above error.

We tried a couple of things:

1. Tried controlling the number of records being polled from SQL server using distributed polling feature and controlling the number of records read per transaction. However incase of high load there were some BPEL instances which were still failing.

2. Second option tried was instead of directly connecting to the SAP R/3 system we tried connecting to a Load balancer infront of the SAP servers. This helped in distributing the load across SAP servers. For doing this configure the SAP target as shown below:

Fill the fields marked with *. Message Server is the load balancer name. Also note you need to edit the /etc/services file using root user and add below entry.
sapms<R/3 name> <port>/tcp

This ensured that the message server will share the load across SAP servers at runtime.