SQL Server Interview Question #297

How can an ASP.NET Core application safely handle transient SQL Server failures?

ASP.NET Core, EF Core & SQL Server Real-World Scenarios Senior Advanced

Detailed Explanation

Transient failures include temporary connectivity problems, failovers, throttling, deadlock victims, and other short-lived conditions depending on deployment.

EF Core's SQL Server provider can enable retrying execution strategies through EnableRetryOnFailure. Retries should be bounded and used only for operations that are safe under the strategy.

Custom transaction code requires special care because retrying an individual statement inside a partially completed business operation can create incorrect behavior. The entire retriable unit should be designed consistently.

Code Example

services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(
        connectionString,
        sql => sql.EnableRetryOnFailure()));