Saturday, November 3, 2012

Red Gate SQL Compare crashing after 20-30 minutes

Consider following situation:

I use Red Gate SQL Compare to compare database schema between
2 remote database servers. Usually it works fine and the process finishes
within couple of minutes.If you compare a DB where its objects encrypted,
for example, stored procedures, functions are encrypted, SQL Compare
has option to decrypt the encrypted objects and the comparing process
takes a little longer, but it works.

On some new installed Data Center the SQL Compare was getting the
following error after 20-30 minutes of comparing the local database and
the remote database on the new installed Data Center:

"A transport-level error has occurred when receiving results from the server.
(provider: TCP provider, error: 0 - An existing connection was forcibly closed
by the remote host.)"

Well, it took us some time to understand why on the new servers
we getting the "transport-level"/"time out" error above.
The bottom line: it happened due to "TCP Chimney" option that
was enabled in the Windows Server.
It means the following:
TCP Chimney Offload is a networking technology that helps
transfer the workload from the CPU to a network adapter
during network data transfer.
This should be a good thing, but in order to get it working correctly
and without any issues you need a NIC to support this feature
and proper drivers.Also it should be enabled in the OS and NIC
The error not happened in all rest data centers because this feature is
enabled/disabled by default in different Windows Servers.
For example, TCP Chimney is enabled by default in the
Windows Server 2003 SP2 and it disabled by default in the
Windows Server 2008.
To disable the TCP Chimney use the following:
netsh int tcp set global chimney=disabled
According to this article the TCP chimney can cause
also other problems related to SQL Server.
I hope this post will help you to save hours:)

1 comment:

  1. Just saved me some hours! thanks