I'm building an API that using node-mssql package to connect to MSSQL, it provides connection pool functionality, so I can reuse the connection to improve performance.
It works well in local env, the connection is reused, and can be closed within configured time (like 30 seconds). But in production env (Azure App Service for container + Azure Hybrid Connection + On-Premised SQL Server), the connection can be reused, but can not be closed. So after the configured time has passed, if I send a new request, a new connection will be established, and the old connection always exist. However, if the app is running in local env, and connect the production db via VPN, it also works well.
My Code
return sql.connect(config).then((pool) => {
return pool.query('select * from location')
});
My Log
mssql:base pool(1): created +0ms
mssql:base pool(1): connecting +2ms
mssql:tedi pool(1): connection #1 created +0ms
mssql:tedi connection(1): establishing +0ms
mssql:tedi connection(1): established +42ms
mssql:base pool(1): connected +48ms
mssql:tedi connection(1): destroying +1ms
mssql:tedi connection(1): destroyed +2ms
mssql:base request(1): created +0ms
mssql:tedi pool(1): connection #2 created +7ms
mssql:tedi connection(2): establishing +1ms
mssql:tedi connection(2): established +19ms
mssql:tedi connection(2): borrowed to request #1 +0ms
mssql:tedi request(1): query select * from location +0ms
mssql:base connection(2): released +45ms
mssql:tedi request(1): completed +7ms
mssql:base pool(1): already connected, executing connect callback immediately +7s
mssql:base request(2): created +7s
mssql:tedi connection(2): borrowed to request #2 +7s
mssql:tedi request(2): query select * from location +0ms
mssql:base connection(2): released +13ms
mssql:tedi request(2): completed +7ms
mssql:tedi connection(2): destroying +37s
mssql:tedi connection(2): destroyed +1ms
You can find that the module destroyed the socket in 30 seconds and printed some log, but the connection actually was not being closed.
My Guess: Maybe the Azure Hybrid Connection is the root cause of the problem, but another app that built with netcore which is not hosted in container can access the DB properly, and no sleeping connection will be left.
Similar question which I've found: ODP.NET Connection pooling and Azure Hybrid Connection to on-prem database
Update: After digging in the code of node-mssql, I found that client will destroy the socket after a query is executed, seems that Azure Hybrid Connection Manager didn't close the socket on another side (On-Premised DB Server), so the connection was not closed.
