An exception has been raised that is likely due to a transient failure when accessing an Azure SQL Database with Auto-pause on from Entity Framework Core

When you create an Azure SQL Database with the Serverless (pay per second of usage) pricing model there’s an auto-pause option which will pause the DB after a period of inactivity. This option is intended to save you money. One problem with enabling it is connection failures when activity re-starts again as the DB has to go through a cold start.

When you’re connecting to a paused DB from Entity Framework Core you’ll often see an error like below…

An exception has been raised that is likely due to a transient failure. Consider enabling transient error resiliency by adding ‘EnableRetryOnFailure()’ to the ‘UseSqlServer’ call.

As suggested by the error we can handle this by including EnableRetryOnFailure in our DbContext configuration as shown below…

EnableRetryOnFailure setting for Entity Framework

When the above options are set and the DB is in a paused state the first request of the app will take longer than normal but the user won’t see an error as the DB will be accessible after one or two connect retries.

Testing EnableRetryOnFailure when Azure SQL DB is paused

Unfortunately at the moment there doesn’t seem to be a way to simulate the paused state for Azure SQL Databases so you’ll need to wait an hour (min auto-pause time) to test. You can confirm the DB is in a paused state from the portal…

Azure SQL DB state

… and after hitting your app if you’re logging debug and info messages you should see some failed connection attempts (first yellow box below) and finally a successful connection and query execution (second yellow box below) in your logs.

The sample log output below (click for a larger view in a new window) is taken from the App Service Log stream page which allows you to view your application logs in real-time direct from the portal.

Viewing app service logs

Leave a Reply

Your email address will not be published. Required fields are marked *