0

I am working on an MVC application that connects to MS SQL Server 2008 R2. The development server is the Express Version - I don't know if that is a contributing factor...

Intermittently, when I run the application from Visual Studio (so it opens in Chrome), I get errors connecting to SQL Server.

Errors such as:

  • The semaphore timeout period has expired.
  • The underlying provider failed on Open.

This is my connection string (I changed a few words in it before posting it publicly)

<add name="[MyEntities]" 
    connectionString="metadata=res://*/Models.[publicword].csdl|res://*/Models.[publicword].ssdl|res://*/Models.[publicword].msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=thanos;initial catalog=[DatabaseName];Integrated Security=SSPI;multipleactiveresultsets=True;application name=EntityFramework&quot;"
    providerName="System.Data.EntityClient" />

I have searched via Google quite a bit, without finding anything especially helpful - I am trying to figure out how I could make my application retry once when it gets a connection error, rather than failing, considering it almost always works on the second try.

Any suggestions of where to start? I don't have access to mess with the server as an Admin but if that's the place to address this, I can probably coordinate with the person who is an Admin.

I am considering it possible that this is somewhat related to running the application locally - the database is on a server some miles away from where I am located. Once the application is deployed to the server, the application will be running on the same physical box as the database. But I don't feel good enough about that to assume that everything will be fine - I don't want to get to Production with this assumption just to find out I was wrong...

John Saunders
  • 160,644
  • 26
  • 247
  • 397
Daniel
  • 71
  • 2

1 Answers1

1

Is there any reason you dont handle the intermittent timeout by a retry block ? If in reality your database might be non-geolocated then IP lag and other network anomolies may well interfere with your ability to connect on a sporadic basis.

As a similar example the standard practise on connecting to a SQL Azure database is to always connect in retry block, mainly for throttling reasons rather than network failure, but its quite common practise to handle intermittent failures this way.

PhillipH
  • 6,182
  • 1
  • 15
  • 25
  • That's what I would like to do - I wasn't understanding how to do that though - all the examples I've found are about SQL Azure and I was having troubles applying that to non-Azure... – Daniel Apr 25 '14 at 19:22
  • Ok, then I'd refer you to http://stackoverflow.com/questions/1563191/c-sharp-cleanest-way-to-write-retry-logic – PhillipH Apr 28 '14 at 12:18
  • Thank you! I'll look into that. It seems that rebooting our local router has resolved much of my issue but I want to make my page a little better able to work with whatever it runs into too... – Daniel Apr 28 '14 at 18:30