Building Resilient Data Layers with EF Core Retry Logic

When an ASP.NET application backed by SQL Server experiences a brief network blip, the worst-case response is often a hard failure surfaced straight to the user. Cloud platforms, elastic pools and high-traffic APIs make those blips far more common than the developers who first wrote the data access code expected. Australian teams in particular operate across long geographic distances — between Sydney and Melbourne offices, from Brisbane to Perth, or from a corporate datacentre in Canberra to an Azure region hosted overseas — and that shape of deployment pushes transient SQL Server faults into the everyday reliability conversation.

Entity Framework Core treats transient fault handling as a first-class concept through the execution strategy pipeline, allowing a retry policy for SQL Server to be applied declaratively. Out of the box the provider knows how to recognise a small set of well-known transient signals and replay failed operations. Getting the most out of that machinery, however, requires understanding which errors qualify, how the retry counters interact with user transactions, and how to keep the layer observable when the database misbehaves.

The walkthrough below covers the practical mechanics of implementing a robust SQL Server retry logic policy in EF Core, from configuring EnableRetryOnFailure to extending it with custom error codes and instrumentation that fits an Australian production workload. Attention is paid to idempotency, diagnostic logging, and the small policy decisions that quietly decide whether retries help or harm a system.

Why transient faults matter for Australian applications

A transient fault is a temporary condition that disappears if the same operation is attempted again seconds later. For SQL Server, common offenders include throttling under Azure SQL elastic pools, brief network glitches between an Australian application server and a Sydney database endpoint, failovers in geo-replicated setups, and resource contention during heavy batch jobs. In Australia, where many organisations run hybrid estates that span on-premises SQL Server instances in Melbourne and managed instances hosted in Azure's Sydney or Central US regions, the path between a web tier and the database is rarely as short as a diagram suggests.

The Australian Signals Directorate's Essential Eight maturity model and the Privacy Act 1988 with its Notifiable Data Breaches scheme both push organisations toward resilience and continuity controls. Repeated, unhandled database failures are more than an availability nuisance — if they cascade into half-written business records, the consequences can ripple into compliance reporting. Atlassian, Canva and the major Australian banks all publish post-incident notes that surface the same lesson: well-tuned retry and circuit-breaker logic is what separates a degraded experience from a public outage.

Locally, day-to-day realities shape the policy too. Australian engineering teams frequently collaborate with counterparts in Europe, India or North America, so an application hitting "off-hours" in Sydney may still see heavy load from a European partner integrating overnight. That uneven load profile increases the likelihood of Azure SQL DTU saturation and login throttling. A retry policy tuned to those conditions — generous enough to absorb a minute of pressure yet disciplined enough not to multiply a slow query — becomes an operational necessity rather than a nice-to-have.

Understanding SQL Server transient errors

SQL Server surfaces transient conditions through specific error numbers, many of which are documented by Microsoft for Azure SQL Database and SQL Managed Instance. The most common are 40197 (service busy), 40501 (service is busy), 10928 and 10929 (resource limits reached), 10053 and 10054 (transport-level errors), 10060 (deadlock victim during connect), 18456 (login failed, often transient during failover), 4060 (database unavailable during reconfiguration), and 40143 or 233 (connection broken). Each one represents a slightly different failure mode, but they all share a common property: another attempt, after a brief pause, is likely to succeed.

EF Core's SqlServer provider bakes the canonical set into the default execution strategy. The provider recognises 4060 and 40197 as transient, retries on 10928 and 10929 when the resource governor has throttled a session, and retries on the small handful of transport-level codes that indicate an interrupted handshake. Anything outside that list is treated as a deterministic failure and bubbled up immediately. That distinction matters because indiscriminately retrying a deterministic error — a syntax mistake in a stored procedure, a permission revocation — turns a clear bug into a confusing delay.

Before writing any retry policy, it pays to capture real production error logs for at least a week and count which codes actually appear. Australian financial-services shops often see a slightly different mix than retail platforms. Brokers using Sydney-based Azure SQL may experience more failover-style codes during Microsoft's regional maintenance windows, while a government deployment in a Protected cloud zone might see more login throttling under tight DTU budgets. The data should drive the configuration, not the other way around.

The EF Core execution strategy framework

The execution strategy is a pluggable component that lives between DbContext and the underlying ADO.NET connection. Whenever EF Core opens a connection, executes a command or initiates an ambient transaction, it asks the strategy whether the operation should be wrapped in retry logic. The default strategy — NonRetryingExecutionStrategy — simply runs each call once, which is the right choice for unit tests but rarely correct in production.

For SQL Server, EF Core ships two relevant implementations. SqlServerExecutionStrategy is the non-retrying base; it exists so that developers can build their own strategies by subclassing it. SqlServerRetryingExecutionStrategy is the one most production codebases reach for. It takes a maximum retry count, a maximum delay and an optional set of additional error numbers, then applies exponential back-off with jitter to each replayed call.

The strategy is wired up when the DbContext is configured, typically inside Program.cs for an ASP.NET Core application or in the OnConfiguring override for libraries. Once enabled, retries apply automatically to every database call made through that DbContext — including SaveChanges, SaveChangesAsync, FromSqlRaw, ExecuteSqlRaw and any explicit Database.ExecuteSql call. The framework is opinionated about this: it does not ask developers to wrap individual calls in retry blocks, because doing so reliably would require a level of context the call site rarely has.

Configuring a custom retry policy step by step

The simplest configuration attaches the default retrying strategy with sensible parameters and lets EF Core handle the rest. In an Azure-hosted ASP.NET Core service connecting to a Sydney SQL pool, a starting configuration might look like the following.

builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(
        connectionString,
        sqlOptions => sqlOptions.EnableRetryOnFailure(
            maxRetryCount: 5,
            maxRetryDelay: TimeSpan.FromSeconds(30),
            errorNumbersToAdd: null));

Five retries with a thirty-second ceiling covers almost every Azure SQL throttling event in normal operations. The exponential back-off starts at a few hundred milliseconds, doubles on each attempt and tops out at the configured ceiling, with a small random jitter added to avoid retry stampedes when many instances are competing for the same database. For a team operating across AEST business hours plus overnight European load, that combination balances patience with the need to surface sustained outages quickly.

When the policy needs to catch additional error codes — perhaps an internal SQL routine that throws a custom error during failover — errorNumbersToAdd accepts an IEnumerable<int> and merges those codes into the transient set without overriding the built-ins. The use case often appears in Australian enterprise environments where ISV applications rely on legacy stored procedures that signal transient conditions with their own numbers. Adding those codes to the strategy turns a fragile integration into one that survives quiet maintenance windows without manual intervention.

Handling specific SQL error codes

Even with EnableRetryOnFailure in place, real applications sometimes need finer control. EF Core exposes ExecutionStrategyFactory and the IExecutionStrategy interface for those cases, allowing a strategy that combines the built-in retry behaviour with project-specific logic. For instance, a healthcare platform subject to the My Health Records Act may want certain error codes to trigger an instant fallback read from a secondary replica rather than a retry against the primary, because the latency cost of a wrong retry against the primary could exceed a legislated service-level commitment.

A second, lighter pattern is to register a strategy delegate through UseSqlServer(... .ExecutionStrategy(...)) for a single dependency injection scope. That is useful when a particular background worker, running on a schedule that only fires during AEST business hours, must match the policy of the rest of the application without inheriting any cached instance from a long-lived singleton.

For deeper debugging of why a retry path misbehaves, attach a debugger that can break inside the asynchronous rethrow point. The reference material on debugging async code walks through the breakpoint configuration that makes stepping through a multi-attempt SQL command far less painful, particularly when the failure surface is on the third attempt inside a tight request budget.

Idempotency and transactional safety

Retries only work when the replayed operation produces the same observable outcome as the original. EF Core enforces this constraint by design: if a developer opens an explicit transaction with BeginTransaction, the retry strategy refuses to wrap further calls and throws an InvalidOperationException. The reason is simple — a retried command inside an open transaction can leave the database in an undefined state, and the framework refuses to put teams in that position silently.

For most CRUD paths through SaveChanges, idempotency is natural because the operations are well-known and short. Bulk inserts of audit log records, however, can surprise even experienced developers: an optimistic concurrency token that increments per insert can turn a single intent into two records if the original insert succeeded but the response was lost. For Australian payment systems subject to the ePayments Code, double-charging caused by retried inserts is exactly the kind of incident that triggers a regulatory conversation with the relevant industry scheme.

The pragmatic answer is to design the retry boundary carefully. Use Add calls on aggregates that contain a server-generated uniqueidentifier as the primary key, so the row is genuinely new on every retry. Avoid IDENTITY columns for payloads that may be retried, and prefer MERGE or INSERT ... WHERE NOT EXISTS patterns when duplicating a row would violate a business rule. Where a manual transaction is unavoidable, scope it so the retry strategy can wrap the entire unit of work from outside.

Observability: logging retries and monitoring health

A retry policy that fires silently is worse than no policy at all, because the operations team has no signal that the system is compensating for an underlying problem. EF Core raises ExecutionStrategyRetrying events on DbContextOptionsBuilder through UseSqlServer(...).EnableRetryOnFailure(...).LogTo(...) and through the DbContext infrastructure events, allowing every retry attempt to be recorded with the elapsed time, the error number and the upcoming delay.

For an Australian team using Application Insights or an on-premises Grafana stack fed by Serilog, the practical setup is to capture those events as structured logs and forward them to the team's existing alerting pipeline. Counter metrics on retries per minute become a leading indicator for capacity planning, while a sudden spike in retries against error 10928 is a strong signal that the Azure SQL pool is approaching its DTU ceiling and a support ticket will be needed.

For local teams, small habits help. Sydney-based engineers learn quickly that 09:00 AEST is when business traffic ramps up and is the most likely window for transient faults to surface. Setting a slightly more verbose log level during that hour, or wiring a temporary alert that pages on more than three retries per minute, gives an early-warning system without flooding the team's preferred channels during quiet periods.

Comparing retry strategies for SQL Server

Practical guidance for production workloads

• Start with EnableRetryOnFailure(5, TimeSpan.FromSeconds(30), null) and validate against realistic load before tightening the parameters.
• Treat BeginTransaction calls as a signal to redesign the operation rather than to override the policy, because the framework's safety check is there for a reason.
• Add custom error numbers only when production logs prove the standard set is incomplete; never add codes speculatively based on documentation alone.
• Capture every retry event as a structured log line and ship it to the existing observability stack, whether that is Application Insights, Splunk or a Grafana Loki deployment in a local data centre.
• Test the strategy with chaos scripts that drop connections and throttle the SQL pool, and verify that the application recovers without manual intervention during AEST business hours.