sql server error after quiesence
I find that if my app has not been used for some time, it will throw SQL server errors initially. But then after about 30 seconds to a minute it starts to behave correctly.
This is the error I'm getting:
(A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server))
New Relic notices the error as well: https://rpm.newrelic.com/accounts/1500084/applications/34892419/traced_errors/0e35c487-bdb9-11e6-a1ff-f8bc12425d4c_0_477
New Relic shows the System.Data.SqlClient.SqlConnection.Open() operation taking 27 seconds which may well be triggering a timeout.
The app is http://spellingbot.apphb.com/ though the URL that initiates the DB connection is https://spellingbot.net/TestAdmin.html
Is this a plausible explanation or is something else going on? Any suggestions as to mitigation? Can we configure a timeout parameter perhaps?
Bob
Discussions are closed to public comments.
If you need help with AppHarbor please
start a new discussion.
Keyboard shortcuts
Generic
| ? | Show this help |
|---|---|
| ESC | Blurs the current field |
Comment Form
| r | Focus the comment reply box |
|---|---|
| ^ + ↩ | Submit the comment |
You can use Command ⌘ instead of Control ^ on Mac
Support Staff 1 Posted by rune on 09 Dec, 2016 08:43 AM
Hi Bob,
It sounds like the application may need to perform some database-intensive tasks at startup? That can sometimes return issues like the one you mention, particularly if the shared database server is under extra load.
To mitigate I'd recommend upgrading to a dedicated SQL server database as these provide more consistent performance - the shared database service is only meant for testing/dev as performance and availability will be very variable at times. AppHarbor provides dedicated SQL server options through the add-on catalogue, but you may want to take a look at Amazon RDS for SQL Server for a more cost-effective option. They have SQL server instances starting around $15/month (and a free tier the first year) and up, and can be integrated easily with your AppHarbor apps -- you'll need the details in this KB article if you take this approach.
Also, since the issue appears to occur at startup you may want to minimize that. You likely don't experience this issue when you redeploy your application as AppHarbor will warm up the new worker before routing data to it -- at least for up to 2 minutes on the free plan. However, applications on the Canoe plan will time out after 20 minutes of inactivity. You can reduce this by upgrading to the Catamaran plans workers on that plan will only idle out after 36 hours of inactivity. Apps on the paid plans also have 5 minutes to warm up before routing traffic to it.
I hope this helps, but please don't hesitate to reach out if there's anything else I can help with!
Best,
Rune