February 11, 2011 at 7:42 am
Hello all,
We have a MOSS 2007 custom web part based on a user control that queries a SQL database cluster. When the connection to the database is made, we regularly experience the error below and have had to create a work-around in the code to retry the connection. We have 3 environments - development, staging and production; the error only occurs in production. The difference in the production environment is it is a farm with 3 WFEs and a SQL cluster, where development and staging are single machine instances. All are MOSS 2007 & SQL Server 2008.
I have verified that the error only occurs upon the first attempt at a connecton; subsequent requests are successful. We cannot find what is causing this issue and I'm hoping someone else has experienced a similar problem and knows the cause.
This is the error that occurs when the query is first executed:
System.Data.SqlClient.SqlException: A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParserStateObject.WriteSni()
at System.Data.SqlClient.TdsParserStateObject.ExecuteFlush()
at System.Data.SqlClient.TdsParser.TdsExecuteRPC(_SqlRPC[] rpcArray, Int32 timeout, Boolean inSchema, SqlNotificationRequest notificationRequest, TdsParserStateObject stateObj, Boolean isCommandProc)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteScalar()
at [Our Namespace.OurMethod](String someParam)
More than one method will generate this, but this is typical code, note the retry work-around:
public static DataTable OurMethod(string someParam)
{
const string query = "sp_OurStoredProcedure";
var resultsTable = new DataTable();
var connection = new SqlConnection { ConnectionString = OurDatabaseConnection };
var command = new SqlCommand(query, connection) { CommandType = CommandType.StoredProcedure };
var dataAdapter = new SqlDataAdapter(command);
var retry = 5;
while (retry > 0)
{
try
{
command.Parameters.Clear();
command.Parameters.AddWithValue("@someParam", someParam);
connection.Open();
dataAdapter.Fill(resultsTable);
retry = -1;
}
catch (Exception ex)
{
retry--;
if (retry <= 0)
{
LogError("Exception: DataAccess:OurMethod: *RETRIES EXCEEDED* " + ex, "error");
}
else
{
SqlConnection.ClearPool(connection);
LogError("Exception: DataAccess:OurMethod: ATTEMPTS REMAINING " + retry + ". " + ex, "warning");
}
}
}
dataAdapter.Dispose();
command.Dispose();
connection.Dispose();
return resultsTable;
}
Any help is greatly appreciated
April 8, 2011 at 7:30 am
Hope this helps someone...
We resolved the issue by creating a private vlan for the web services to talk to the sql cluster
On the WFE Servers placed an entry in the hosts file for the sql cluster
On the SQL cluster added the cluster virtual IP to the SQL Service and made the SQL Server service dependent on it.
This makes all traffic from the WFE servers go over the private vlan instead of back out through the load balancer.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply