Timeout Expired on SQL Azure; cannot be reproduced on-premise SQL Server [closed]

Question

In our line of business we are hosting a REST based API that is hosted by Windows Azure and with SQL Azure as database storage.

Both the Web Role (Windows 2008R2, IIS 7.5, WCF, Large instance) and SQL Azure is hosted in North Europe region.

The problem is, that when we do intensive SQL work we often get a "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.".

What troubles me here is, that no matter what we do, we cannot provoke this on our on-premise SQL servers (SQL Server 2008R2).

Any help in clarifying this mystery is appreciated as it seems that the Web Role instance is not directly talking to the SQL Azure instance although both are located in North Europe.

A more detailed exception:

<SqlException>
    <Message>Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.</Message>
    <StackTrace>
        <Line>at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)</Line>
        <Line>at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()</Line>
        <Line>at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)</Line>
        <Line>at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()</Line>
        <Line>at System.Data.SqlClient.SqlDataReader.get_MetaData()</Line>
        <Line>at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)</Line>
        <Line>at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)</Line>
        <Line>at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)</Line>
        <Line>at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)</Line>
        <Line>at System.Data.SqlClient.SqlCommand.ExecuteScalar()</Line>
        <Line>at SyncInvokeAddCollaboratorFieldInstance(Object , Object[] , Object[] )</Line>
        <Line>at System.ServiceModel.Dispatcher.SyncMethodInvoker.Invoke(Object instance, Object[] inputs, Object[]&amp; outputs)</Line>
        <Line>at System.ServiceModel.Dispatcher.DispatchOperationRuntime.InvokeBegin(MessageRpc&amp; rpc)</Line>
        <Line>at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage5(MessageRpc&amp; rpc)</Line>
        <Line>at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage31(MessageRpc&amp; rpc)</Line>
        <Line>at System.ServiceModel.Dispatcher.MessageRpc.Process(Boolean isOperationContextSet)</Line>
    </StackTrace>
    <UserDefinedInformation>
        <HelpLink.ProdName><![CDATA[Microsoft SQL Server]]></HelpLink.ProdName>
        <HelpLink.ProdVer><![CDATA[11.00.2065]]></HelpLink.ProdVer>
        <HelpLink.EvtSrc><![CDATA[MSSQLServer]]></HelpLink.EvtSrc>
        <HelpLink.EvtID><![CDATA[-2]]></HelpLink.EvtID>
        <HelpLink.BaseHelpUrl><![CDATA[http://go.microsoft.com/fwlink]]></HelpLink.BaseHelpUrl>
        <HelpLink.LinkId><![CDATA[20476]]></HelpLink.LinkId>
    </UserDefinedInformation>
</SqlException>

Answer 1

If you need to do SQL intensive work (for instance, a lot of INSERT statements in a normalized OLTP database) you need to have fail-over logic in your code.

On-premise SQL server will not suffer from this, so take this into consideration before switching to SQL Azure.

These two articles inspired me (special thanks to Joachim Isaksson for guidance):

http://blogs.msdn.com/b/sqlazure/archive/2010/05/11/10011247.aspx

http://social.msdn.microsoft.com/Forums/en-US/ssdsgetstarted/thread/7a50985d-92c2-472f-9464-a6591efec4b3/

To sum of the result, I have provided the two results which is now identical in result (where before some records where not added do to missing fail-over logic in regards to the original question: Timeout Expired):

On-premise SQL Server: 179.285 records in 427 seconds

SQL Azure w. fail-over logic: 179.285 records in 2.247 seconds - a whooping 5.2 times slower!

I hope this can help others struggling with SQL Azure. On a positive note; you learn (the hard way) that you have been lucky and privileged on your native in-house applications :-)

Note: i would still like an explanation how this can happen .. seems to easy to blame on latency, no?