I have a Webserver and a SQL Server inside an Amazon VPC.
Previously, a Webserver outside the VPC could connect to the SQL Server fine.
Now, sometime, when running SQL Scripts (the deployment script reruns all the stored procedures on install), some of the create procedure scripts fail with an error
"An exception occurred while executing a Transact-SQL statement or batch. A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 -The semaphore timeout period has expired)"
Once it starts failing, it will continue to fail until a reboot. It is always the same scripts that fail. Rebooting both servers clears the problem, but that's not a long term solution. Windows Firewalls off on both machines. Windows Server 2012 on both machines. It might be correlated with a pending Windows Update on the Web Server; every time it's stopped there have been updates pending. Server Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) Jun 17 2011 00:54:03 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.2 (Build 9200: ) (Hypervisor)
If I open up the Security Group for the SQL Server, I can connect to it directly and run all the scripts from my development machine; they still fail when I run them from the Web Server, so I'm pretty sure it's the Web Server that's the problem. I'm just not sure how or why.
The scripts are automatically generated from the database; they're basically identical, and don't contain syntax errors, and work when run from elsewhere. But some of them die when being run from the Web Server.
What could the problem be?
Looks like a combination of 2 things:
Setting a lower MTU and enabling IMCP seems to have fixed it, so far, anyway.