Connection resiliency required when using Azure SQL database

[LaszloKoller]

Describe the bug The CustomerSite (& AdminSite) is unable to recover from a transient failure when connecting to the backing Azure SQL database.

To Reproduce Steps to reproduce the behavior:

1. Implement Azure Application Insights logging for the CustomerSite (and AdminSite)
2. ...and wait until a transient failure eventually occurs.

Expected behavior Microsoft.Data.SqlClient.SqlException should not occur due to transient (connection) failures (see log excerpt below).

RESOLUTION: Implement the fix recommended in Microsoft's Connection Resiliency article.

In the AdminSite/Startup.cs file, change the UseSqlServer() line of code from: services.AddDbContext<SaasKitContext>(options => options.UseSqlServer(this.Configuration.GetConnectionString("DefaultConnection"))); ...to: services.AddDbContext<SaasKitContext>(options => options.UseSqlServer(this.Configuration.GetConnectionString("DefaultConnection"), providerOptions => providerOptions.EnableRetryOnFailure()));

In the CustomerSite/Startup.cs file, change the UseSqlServer() line of code from: services.AddDbContext<SaasKitContext>(options => options.UseSqlServer(this.Configuration.GetConnectionString("DefaultConnection"))); ...to: services.AddDbContext<SaasKitContext>(options => options.UseSqlServer(this.Configuration.GetConnectionString("DefaultConnection"), providerOptions => providerOptions.EnableRetryOnFailure()));

In the MeteredTriggerJob/Program.cs file, change the UseSqlServer() line of code from: .AddDbContext<SaasKitContext>(options => options.UseSqlServer(configuration.GetConnectionString("DefaultConnection"))) ...to: .AddDbContext<SaasKitContext>(options => options.UseSqlServer(configuration.GetConnectionString("DefaultConnection"), providerOptions => providerOptions.EnableRetryOnFailure()))

Screenshots Application Insights log excerpt:

An exception occurred while iterating over the results of a query for context type 'Marketplace.SaaS.Accelerator.DataAccess.Context.SaasKitContext'.
System.InvalidOperationException: 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.
---> Microsoft.Data.SqlClient.SqlException (0x80131904): Database '<database-name>' on server '<sql-server-name>' is not currently available. Please retry the connection later. If the problem persists, contact customer support, and provide them the session tracing ID of '<guid>'.

Environment (please complete the following information):

• Are you using the CloudShell? No
• If not using the CloudShell:
  • OS: Windows 11 Pro
  • PowerShell version: 5.1.22621.963
  • Modules installed: Azure Application Insights implemented with the SaaSAccelerator CustomerSite (also, the AdminSite)

Additional context For additional context, see Microsoft's Connection Resiliency article.

[santhoshb-msft]

@LaszloKoller thank you for this, we will review and incorporate in the coming sprints.

[manilsen]

@santhoshb-msft We seem to be experiencing the same and this is flagged in a review since the error message is leaking a lot of debug data. Is there a way in the Web App to disable the verbose error message that appears when there is an SQL issue (like 0x80131904).