Document Properties
Kbid
30134T
Last Modified
16-Dec-2022
Added to KB
24-Feb-2022
Public Access
Everyone
Status
Online
Doc Type
Guidelines
Product
ICM 7.10
Guide - 7.10.20+ Avoid MSSQL Reconnect Issues

Introduction

Most reconnection issues with the MSSQL Database are caused by too many TCP connections to the database. The SQL server has no free ports during the connection setup phase. The error looks like

The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption.


The follow changes can reduce the number of failed connection setups for the application code:

  1. Enable reconnect in case of a failure
  2. Reduce the number of new connections by configuring (optimizing) the JDBC-connection pool

Enable Reconnect

Since version 9.4.0 the JDBC driver contains an additional parameter for reconnect.

The mssql-jdbc driver is updated in all LTS versions of ICM. To improve the rollout speed of the change, the project can apply a version filter to update the library without updating the whole project. Please keep in mind to remove the "patch" after migrating to the latest LTS version.

LibraryOld VersionNew Version

com.microsoft.sqlserver:mssql-jdbc

8.4.1.jre89.4.0.jre8

Following adaptions are recommended:

PropertyDefaultValue
connectRetryCount13
connectRetryInterval1020
loginTimeout1530

See the documentation of the jdbc-driver to configure the reconnect capability.

How to get the used version

All libraries are listed at <IS_SHARE>/ivy.xml. The line can be found easily.

cat ivy.xml | grep "mssql-jdbc"


<dependency org="com.microsoft.sqlserver" name="mssql-jdbc" rev="9.4.0.jre8" conf="development-cartridges, production-cartridges, test-cartridges" transitive="false"/>

Availability in ICM versions

ICMmssql-jdbc

7.10.26.19-LTS

9.4.0.jre8
7.10.32.9-LTS9.4.0.jre8
7.10.37.09.4.0.jre8
7.10.38.3-LTS9.4.0.jre8

Version Provider

In case the project can't update to an newer ICM soon. The version can be defined at project too. The project can declare a specific filter for project-specific dependencies, please add this section to the build.gradle of the customization. The build.gradle can contain the following block:

build.gradle
versionRecommendation {
    provider {
        // thirdparty.version to resolve version conflicts of custom cartridges
        properties('thirdparty', file('thirdparty.version')) {}
    }
}

Afterwards versions can be adapted in the Java-like property file:

thirdparty.version
com.microsoft.sqlserver:mssql-jdbc=9.4.1.jre8

The documented version of the JDBC driver could be updated meanwhile. See https://mvnrepository.com/artifact/com.microsoft.sqlserver/mssql-jdbc. Keep in mind that the version needs to have a JRE8 extension.

Configuration JDBC Connection Pool

ICM uses an Apache connection pool which has 3 important configurations:

PropertyDescriptionoldnewfor larger installations
intershop.jdbc.mssql.dbcp.pool.MaxIdleIn case the pool has more than this amount of idle connections, the pool will automatically close connections, which are "closed" by the application.51645
intershop.jdbc.mssql.dbcp.pool.MinIdleIn case the pool has less than this amount of idle connections, the pool will automatically create new connections without an explicit connection request5415
intershop.jdbc.mssql.dbcp.pool.MaxTotalMax amount of connections handle by the pool150150150

The value of MaxIdle should be significant larger than MinIdle, so that used connections can be stored in the pool without closing them. The MinIdle value can be increased in case the application needs very fast a larger amount of connections.

Transient Lock

During the staging process, the TransientLock is set for locking resources. These connection between ICM and the SQLServer can be closed by infrastructure components like network or by the SQL Server it self, in case these connections are not used for a long time. To avoid such situations the "TransientLock" can be configured with an heartbeat.

PropertyDescriptionoldnew
intershop.jdbc.connection.Heartbeat.Intervalenables and declare period between two heartbeats in seconds030

ICM Adaptions

VersionDescription
7.10.38.14-LTSChanges the default of intershop.jdbc.connection.Heartbeat.Interval for TransientLock to 30
Disclaimer
The information provided in the Knowledge Base may not be applicable to all systems and situations. Intershop Communications will not be liable to any party for any direct or indirect damages resulting from the use of the Customer Support section of the Intershop Corporate Web site, including, without limitation, any lost profits, business interruption, loss of programs or other data on your information handling system.
The Intershop Knowledge Portal uses only technically necessary cookies. We do not track visitors or have visitors tracked by 3rd parties. Please find further information on privacy in the Intershop Privacy Policy and Legal Notice.
Home
Knowledge Base
Product Releases
Log on to continue
This Knowledge Base document is reserved for registered customers.
Log on with your Intershop Entra ID to continue.
Write an email to supportadmin@intershop.de if you experience login issues,
or if you want to register as customer.