Execution Strategies, Connection Resiliency, Azure SQL Transient Faults, and Retry Semantics

Learning outcomes

1. The problem: a cloud database can be healthy and still reject one attempt

Azure SQL and remote SQL Server deployments can experience brief connection resets, throttling, failovers, or other transient conditions. Retrying can improve availability only if the error is actually transient and replaying the operation is safe. A DbUpdateConcurrencyException caused by a rowversion mismatch is not transient infrastructure; it is a business-state conflict from Chapter 13.

2. Enable the SQL Server execution strategy intentionally

EnableRetryOnFailure configures SQL Server's provider execution strategy with known transient error numbers and optional additional ones. Do not copy retry counts/delays as universal tuning. Start from the provider defaults, observe the environment, and coordinate with request/job deadlines.

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

services.AddDbContext<SqlServerServiceHubContext>(options =>    options.UseAzureSql(azureSqlConnectionString));

Current EF guidance configures appropriate resiliency automatically with UseAzureSql/UseAzureSynapse. With ordinary UseSqlServer, enable retries deliberately when the deployment needs them.

3. A retrying strategy changes the unit of replay

When retries are enabled, each EF query and each SaveChanges call is normally an independently retriable unit. Starting a user transaction outside the execution strategy creates a unit EF cannot safely replay by itself. EF therefore requires the whole transaction block to execute inside CreateExecutionStrategy().ExecuteAsync.

await using var tx = await db.Database.BeginTransactionAsync(ct);await db.SaveChangesAsync(ct); // can throw when retry strategy is enabledawait tx.CommitAsync(ct);

var strategy = db.Database.CreateExecutionStrategy();await strategy.ExecuteAsync(async () =>{    await using var db = factory.CreateDbContext();    await using var tx = await db.Database.BeginTransactionAsync(ct);    // all commands that must commit together are inside this delegate    await ApplyServiceHubChangesAsync(db, ct);    await db.SaveChangesAsync(ct);    await tx.CommitAsync(ct);});

The delegate may run more than once. Anything inside it—including custom SQL, side effects, and ID allocation—must be replay-safe or explicitly guarded.

4. Controlled retry lab: add one fake SQL error number, then fail once

For a deterministic local demonstration, configure a user-defined SQL error number as transient only in the disposable lab. A small table stores a fail-once marker. The first execution deletes the marker and throws error 50001; the provider execution strategy sees 50001 in the added transient list and retries; the second execution succeeds.

options.UseSqlServer(connectionString, sql =>    sql.EnableRetryOnFailure(        maxRetryCount: 3,        maxRetryDelay: TimeSpan.FromSeconds(2),        errorNumbersToAdd: new[] { 50001 }));

IF OBJECT_ID(N'dbo.RetryLab', N'U') IS NULL    CREATE TABLE dbo.RetryLab (Id int NOT NULL PRIMARY KEY);IF NOT EXISTS (SELECT 1 FROM dbo.RetryLab WHERE Id = 1)    INSERT dbo.RetryLab(Id) VALUES (1);-- Run the following inside the execution-strategy delegate:IF EXISTS (SELECT 1 FROM dbo.RetryLab WHERE Id = 1)BEGIN    DELETE dbo.RetryLab WHERE Id = 1;    THROW 50001, 'ServiceHub retry lab transient fault', 1;END;SELECT 1;

var strategy = db.Database.CreateExecutionStrategy();await strategy.ExecuteAsync(async () =>{    var connection = db.Database.GetDbConnection();    if (connection.State != ConnectionState.Open)        await db.Database.OpenConnectionAsync(ct);    await using var command = connection.CreateCommand();    command.CommandText = labSql;    await command.ExecuteNonQueryAsync(ct);});

5. Ambiguous commit is different from a failed command before commit

If the connection drops while a transaction is being committed, the client may not know whether the database committed. Blindly retrying an INSERT with a store-generated identity can create a duplicate logical operation. Idempotency therefore needs a durable application key or a verification step.

public sealed class DispatchCommand{    public Guid OperationId { get; init; } = Guid.NewGuid();    public required string WorkOrderNumber { get; init; }}// Persist OperationId under a UNIQUE constraint in the same local transaction.// A replay can detect the already-committed operation instead of duplicating it.

For ServiceHub, the transactional outbox introduced earlier is a stronger boundary than “send message then retry DB write.” Database state and outbox record commit together; message delivery is replayable/idempotent later.

6. Retries and transactions increase memory/time cost

Execution strategies may buffer result sets so a query can be replayed, increasing memory for large queries. Retries also extend tail latency. Combine Chapter 17 result bounding/projection with retry configuration, and include retry count/delay in request or job SLO analysis.

7. Failure case: retrying a concurrency conflict

A generic “retry on any exception” policy catches DbUpdateConcurrencyException and immediately calls SaveChanges again with the same original rowversion. Every attempt correctly affects zero rows. The loop adds load but never resolves the changed business state.

Repair: handle concurrency conflicts with the domain-specific merge/store-wins/client-wins workflow from Chapter 13. Keep provider transient retries at the connection/execution-strategy layer. The two policies may both exist in one application, but they answer different questions.

8. Mandatory lab: distinguish retryable from permanent

1. Use a disposable SQL Server 2025 Developer database and configure the lab-only additional transient error 50001.
2. Subscribe to normal EF logging and capture execution-strategy retry events without sensitive parameter values.
3. Create/reset dbo.RetryLab, run the fail-once delegate, and verify one failure followed by success.
4. Run a deliberately invalid SQL query such as selecting a nonexistent column and verify it is not made successful by the retry policy.
5. Reproduce the Chapter 13 rowversion conflict and prove it surfaces as DbUpdateConcurrencyException, not as a provider transient retry.
6. Wrap two dependent writes in an explicit transaction inside CreateExecutionStrategy().ExecuteAsync and verify both commit together.
7. Record retry count, elapsed time, SQL error number, transaction scope, and final database state.

9. Production judgment and bridge

Enable connection resiliency for environments that actually exhibit transient SQL Server/Azure SQL failures, but keep retry scope bounded and observable. Never assume retries make non-idempotent side effects safe. Run user transactions inside the strategy delegate, use durable operation IDs where commit ambiguity matters, and keep business concurrency policy separate. The next lesson turns from runtime resilience to SQL Server physical design: indexes and migration DDL.

Authoritative references

Retry behavior is provider- and topology-specific. Re-check SQL Server/Azure SQL guidance and your incident data before changing production policy.

• SQL Server provider - EF Core — UseSqlServer/UseAzureSql and resiliency guidance
• Connection resiliency - EF Core — execution strategies, transaction delegates and buffering
• EnableRetryOnFailure API - EF Core 10 — retry count/delay and additional SQL error numbers
• Transactions - EF Core — explicit transactions and execution-strategy interaction
• Concurrency conflicts - EF Core — business conflict detection and resolution
• Azure SQL transient errors — Azure SQL connectivity/transient-fault operational context