retool->Azure SQL intermittent connection issue

Queries and Resources
1

  • Goal: Retool should be able to query the azure SQL resource immediately

  • Steps: It happens occasionally but somewhat consistently when the app hasn't been in use for a few hours: every query fails with a flurry of error messages when I first open it up in retool. If I immediately refresh the browser, it works fine. It's like the connection goes to sleep or something, or the cached data expires and retool doesn't go grab it again fast enough.

  • Details: I'm embarrassed that I haven't grabbed the actual error messages, but will grab next time it happens. It's to the effect of a connection error, that the queries fail. Oddly, nothing shows up about it in the Retool logs that I can see, though it happened this morning at 0838 CDT or so. And nothing shows up in the SQL Server logs either (using "SELECT * FROM sys.event_log ") returns no errors. In both I can see the activity at that time, but neither catches the error in any way that I can discern.

It is perhaps notable that I have never seen it happen during a working session. It seems to always happen after the app has been idle a while. And it did not happen, to my recollection, when I was using the Retool db. So I'm assuming it's something in the connection between retool and SQL.

I did try adding a "Connection Policy" as "Redirect" but that didn't seem to do anything.

3

In case this helps someone else in the future, the following seem to have fixed this:

  • The band-aid fix: I setup an uptime monitor to hit the public app every 10 mins, to prevent it from "going to sleep" until I sorted it out. Obviously that wouldn't help much if it wasn't a publicly-accessible app.

  • The culprit: I have Azure setup in a pretty basic config right now, using Serverless. This has an "auto-pause" feature that "allows the database to automatically pause during periods of inactivity, which helps in reducing costs". I turned this off; this setting is in the database object, under "compute and storage". Longer-term, I'll probably switch to a "provisioned" instead of serverless setup, which doesn't have this 'feature' .