Qlik Replicate: SQL Server idle connections force closed

Alan_Wang — Tue, 10 Oct 2023 14:16:45 GMT

Inactive TCP/IP connections to SQL Server can sometimes be closed by your router or firewall. This will give your task a recoverable environmental error. Parameters in the Windows Registry can be set to send transmissions for the connection to keep it active.

00026464: 2021-10-27T05:08:24 [SOURCE_CAPTURE ]E: RetCode: SQL_ERROR SqlState: 08S01 NativeError: 10054 Message: [Microsoft][SQL Server Native Client 11.0]TCP Provider: An existing connection was forcibly closed by the remote host. [1022502] (ar_odbc_stmt.c:3977)

Environment

SQL Server as a source
Windows environment

Resolution

Registry parameters can be added on the Replicate Server with Registry Editor.

Add the following dword keys and the specified decimal values:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\TcpWindowSize=256000
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\GlobalMaxTcpWindowSize=16777216
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\KeepAliveInterval=1000
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\KeepAliveTime=60000

Follow these steps to modify the registry manually:

Open Registry Editor and navigate to the correct location:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters
Right-click Parameters to bring up the option to add dword keys
Add the following parameters:
- KeepAliveTime
- KeepAliveInterval
- GlobalMaxTcpWindowSize
- TcpWindowSize
One by one, right-click each parameter and select Modify to modify their DWORD values:
- KeepAliveTime: 60000
- KeepAliveInterval: 1000
- GlobalMaxTcpWindowSize: 16777216
- TcpWindowSize: 256000
Restart the machine.

For more information on the Windows registry parameters, please refer to Microsoft documentation:

Description of Windows TCP features
KeepAliveTime

article Qlik Replicate: SQL Server idle connections force closed in Official Support Articles

Qlik Replicate: SQL Server idle connections force closed

Environment

Resolution