How to limit the number of connections to a SQL Server server from my tomcat deployed java application?

Question

I have an application that is deployed on tomcat on server A and sends queries to a huge variety of SQL Server databases on an server B.

I am concerned that my application could overload this SQL Server database server and would like some way to preventing it making requests to connect to any database on that server if some arbitrary number of connections were already in existence and unclosed.

I am looking at using connection pooling but am under the impression that this will only pool connections to a specific database on the SQL Server server, I want to control the total of these combined connections that will occur to many different databases (incidentally I can only find out the names of individual db's dynamically as they change day to day). Will connection pooling take care of this for me, are am I looking at this from the wrong perspective?

I have no access to the configuration of the SQL Server server.

Links to tutorials or working examples of your suggested solution are most welcome!

Answer 1

You're correct. A database pool will limit connections to either the database, or to all databases, depending on how the pool is configured.

Here are several open source packages that implement database connection pools. One of them probably does what you want.

Answer 2

connection pooling ... this will only pool connections to a specific database on the mssql server

AFAIK, this is correct. Your connection pool will only limit the number of connections to the specific database it is defined for.

want to control the total of these combined connections that will occur to many different databases

I dont think you can control the number of connections to all databases from the pool. You've written you don't have access to change things on the MSSQL server. so creating SYNONYMs to the various databases on MSSQL itself is not an option.

You can write your own class within the application, say a ConnPoolManager, which has an internal counter prior to getting and releasing Connections from any of the pools. This class should cache all the JNDI lookups to each pool. For the app to get a connection to ANY pool, it goes through the ConnPoolManager and if the counter shows the maxlimit is not yet crossed, only then does it fetch the connection. Else it throws some exception for you to try later.

There might be a design pattern for this on the lines of Business Delegate.

Having said that, I think a bigger problem for you will be

incidentally I can only find out the names of individual db's dynamically as they change day to day

since you will be expected to create new or edit the connection pool settings in Tomcat each day? This is a maintenance nightmare in the future.

Answer 3

Have you considered using DBCP? See here for more info