Connection-Pooling doesn't work if not using PooledConnection.

[sdonovanuk]

Driver version

11.2.3

SQL Server version

Microsoft SQL Server 2019 (RTM-CU20) (KB5024276) - 15.0.4312.2 (X64)
Apr 1 2023 12:10:46
Copyright (C) 2019 Microsoft Corporation
Developer Edition (64-bit) on Windows Server 2019 Datacenter 10.0 (Build 17763: ) (Hypervisor)

Client Operating System

MacOS Sonoma 14.1.1 (though I doubt this is relevant).

JAVA/JVM version

17

Problem description

I think there is a bug in the connection pooling logic (or, I don't understand it).

Background: we're using Spring Boot.

The problem starts @ SQLServerDataSource#getConnectionInternal(..).

The only way that pooledConnection is populated, is it called from SQLServerPooledConnection#createNewConnection(..). Without pooledConnection being used, it doesn't use connection pooling.

In our case, we're connecting to a large fleet of SQL Servers. We do code akin to the following:

var dataSource = new SQLServerDataSource();
// populate dataSource
return new JdbcTemplate(dataSource)
		.queryForObject("SELECT @@SERVERNAME", String.class)

Even if we use SQLServerConnectionPoolDataSource, pooling is never used, because the code-path from JdbcTemplate#queryForObject, never calls PooledConnection#getConnection.

Ergo, connection pooling logic is only 'active' if you inject a PooledConnection, and the problem with that, is that it's then static -- i.e. bound to a single SQL Server. As mention, we connect to a fleet of servers.

I think it's a bug if (i) you create an instance of SQLServerConnectionPoolDataSource, (ii) use it and (iii) it doesn't use connection-pooling.

Please advise?

Thanks!

[praskutti]

I believe the driver by itself does not give you pooled connections. Per this documentation : Using Connection Pooling. In our implementation (Spring Framework 5 apps on linux+ SQL 2019 on windows), we use HikariCP as our connecting pooling library. This now uses pooling with any of the different ways Spring allows to connect to SQL Server.

[Jeffery-Wasty]

Hi @sdonovanuk,

The above poster is correct, the driver itself does not implement connection pooling, instead "The JDBC driver implements the JDBC 3.0 required interfaces to enable the driver to participate in any connection-pooling implementation that is provided by middleware vendors and is JDBC 3.0-compliant" (as per our documentation). Hikari is a popular choice for connection pooling use with the driver.

[Jeffery-Wasty]

Closing issue due to lack of further conversation.

[sdonovanuk]

Apologies for not replying sooner. You were, of course, correct. Switched to DBCP2 (which we use for other things), but will try Hikari. Thank you!