This post has been republished via RSS; it originally appeared at: Azure Database Support Blog articles.
“Communication Link Failure” is often due to outdated drivers or poor network configuration settings.
In this scenario SSIS packages were used to load data from SQL on-premises to Azure SQL DB. After a while, the bulk insert fails with below errors:
Error: 2020-08-25 10:44:08.16
Code: 0xC0202009
Source: Extract Location Hierarchy Data to Datamart OLE DB Command [156]
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft OLE DB Driver for SQL Server" Hresult: 0x80004005 Description: "Communication link failure".
An OLE DB record is available. Source: "Microsoft OLE DB Driver for SQL Server" Hresult: 0x80004005 Description: "TCP Provider: An existing connection was forcibly closed by the remote host.
Error: 2020-08-25 10:44:08.23
Code: 0xC0047022
Source: Extract Location Hierarchy Data to Datamart SSIS.Pipeline
Description: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "OLE DB Command" (156) failed with error code 0xC0202009 while processing input "OLE DB Command Input" (161). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
End Error"
There are several options to be considered to address this issue.
1 - Disable TLS_DHE ciphers on client machine
Unless you have a specific requirement for a legacy application, disable all the TLS_DHE ciphers, as they are outdated and not recommended to be used.
Option #1 - Follow the steps described in the documentation below:
Applications experience forcibly closed TLS connection errors when connecting SQL Servers in Windows
Option #2 - Alternatively, you can download IIS Crypto Tool from here and configure the best practices on your client and server.
Steps to configure best practices:
- Open IIS Crypto
- Click on Best Practices
- Go to Cipher Suits > Click on Best Practices Again
- Click Apply
- Restart the server (so that the changes are applied in the registries)
2 - Configure client machine
Step #1 Ensure NIC Drivers are up to date
Step #2 Disable TCP chimney at NIC Level
TCP Chimney Offload is a feature that provides capability to offload TCP/IP packet processing from the processor to the network adapters and some other balancing options. This has been known to cause issues on the SQL Server side causing network errors. Further information on this can be found in the following blog post as well.
TCP Chimney Offload – Possible Performance and Concurrency Impacts to SQL Server Workloads
Make sure that under Advanced Settings of NIC below options are DISABLED:
- IPv4 Checksum Offload
- IPv4 Large Send Offload
- Checksum Offload
- Large Send Offload
Steps to disable TCP chimney features at NIC level:
- Click Start -> Run -> type ncpa.cpl -> click OK
- Right-click a network adapter object -> click Properties
- Click Configure -> Advanced tab
- In the Property list: click receive side scaling -> Disable in the Value list -> OK
- In the Property list: click large send offload ->Disable in the Value list-> OK
- In the Property list: click ipv4 checksum offload -> Disable in the Value list -> OK
- In the Property list: click TCP checksum offload (IPv4) -> Disable in the Value list -> OK
The other properties to look for and disable are:
- Offload Receive IP Checksum
- Offload Receive TCP Checksum
- Offload TCP Segmentation
- Offload Transmit IP Checksum
- Offload Transmit TCP Checksum
Step #3 Configure TCP Chimney Offload, RSS and NetDMA in the operating system.
Open an elevated cmd and type below commands.
To determine the current status of TCP Chimney Offload and RSS:
netsh int tcp show global
To disable TCP Chimney Offload:
netsh int tcp set global chimney=disabled
To disable RSS:
netsh int tcp set global rss=disabled
To disable NetDMA:
netsh int tcp set global netdma=disabled
Restart the machine.
3 - Tune SSIS Package
Another recommendation to alleviate the connection issue is to actually tune your SSIS package to perform more efficiently. There’s lots of articles around this aspect, but the top settings are DefaultBufferMaxRows, DefaultBufferSize, and Rows per batch settings. However, this needs to be performed over many trials, while capturing logging data and adjusted accordingly. We usually see General Network Errors associated with poor performing packages, that degrade over time because of the data/transactions involved.
This blog gives a quick rundown about tuning your SSIS package for a better performance.
Here’s our documentation on top 10 SSIS Best Practices which will also go over different settings you can test.