Connection Resiliency: Surviving Brief Wi-Fi and Network Drops

A laptop loses Wi-Fi for a moment, and an idle database connection is suddenly gone. Connection resiliency can reconnect a broken idle connection before the next command reaches the application.

Know What Connection Resiliency Retries

The driver's idle connection resiliency feature detects a broken idle connection and tries to restore it. ConnectRetryCount controls retry attempts. ConnectRetryInterval sets the seconds between them. Support and defaults vary by client driver and version, so check the actual driver used by the application. I do not copy a connection string from one provider into another and assume the same behavior. A successful retry still consumes time. Set the application's command and connection timeouts with that delay in mind.

The feature does not replay a command that was running when the link failed. It cannot restore an open transaction to the state the caller imagined. Failover can also require application-level handling. Treat retry as a limited convenience, not a promise that every interruption disappears.

Put the Setting in the Real Connection String

Find where the application builds its connection string. It can be in configuration, a secret store, a framework data source, or a connection pool factory. Change the exact string the process uses, then restart or recycle the process so new physical connections pick it up. A test in a separate SSMS window says nothing about an application using another driver. Protect credentials in the usual secret store; do not put a password into a diagnostic screenshot. In PowerShell, set every key through the indexer with its spaced name, such as ‘Data Source’. Property syntax like $builder.DataSource fails there with "Keyword not supported."

# PowerShell
$builder = [System.Data.SqlClient.SqlConnectionStringBuilder]::new()
$builder['Data Source'] = 'server.example.internal'
$builder['Initial Catalog'] = 'AppDb'
$builder['Integrated Security'] = $true
$builder['Application Name'] = 'OrdersWeb'
$builder['ConnectRetryCount'] = 3
$builder['ConnectRetryInterval'] = 5
$builder.ConnectionString

Separate Idle and Active Failures

Test an idle pooled connection that has no transaction and no command in flight. Then test a command running across the interruption. The first is the feature's intended case. The second needs a higher-level retry policy that understands whether the operation committed. An insert can succeed on the server just before the response is lost. Reissuing it blindly can duplicate work. Use operation identifiers or idempotent commands where retries are required. I have seen an application log say "timeout" and assume "nothing happened." The server is not obliged to agree.

For explicit transactions, dispose of the broken connection and start a new transaction only after the application decides how to reconcile prior work. Retrying inside the old transaction object is not recovery.

Pull the Network in a Controlled Test

Use a nonproduction environment. Open the application connection, let it go idle, interrupt the network briefly, restore it, then issue a simple command through the same application path. Record the driver version, retry settings, connection timeout, interruption duration, and observed result. Repeat with the feature disabled to see the difference. A lab test can use a firewall rule or disconnected adapter, but coordinate it so no shared service is disrupted. The network cable test is memorable; the operations team prefers a schedule.

Run the test through the connection pool, not only a one-off connection. Pools can retain stale physical connections and surface the first failure differently. Confirm that logging records a recovered connection without reporting a successful transaction that never ran.

Watch Connection Resiliency From the Server Side

SQL Server DMVs show current sessions, not the driver's private retry loop. Query sys.dm_exec_sessions for the application name and host to confirm where new sessions appear after a reconnect. A new session_id is evidence of a new server connection, not proof that the whole business operation succeeded. Pair it with application logs and a known test query. The Application Name set in the connection string above shows up as program_name here, which makes these sessions easy to spot.

SELECT session_id, host_name, program_name, login_time, status
FROM sys.dm_exec_sessions
WHERE is_user_process = 1
ORDER BY login_time DESC;

Give Connection Resiliency a Bounded Retry Budget

ConnectRetryCount and ConnectRetryInterval interact with connection timeout and application-level retry. If the driver waits several intervals and the application immediately retries the entire request, a brief outage can create a long chain of attempts. I set one bounded budget for idle reconnect and another explicit policy for safe business operations. Measure the worst-case delay in a test. A connection pool with many workers can produce a reconnect burst after Wi-Fi or network recovery, so rate limits and backoff matter at the application layer.

The error message should distinguish "could not restore the connection" from "operation outcome unknown." A failed read can usually be retried. A write whose response was lost needs an idempotency key or a status lookup. I ask developers to show the call path where the connection is disposed after final failure. Keeping a broken connection object around is not resiliency; it is an invitation to the next exception.

Observe Failover and Pool Recycling Separately

A database failover can close active and idle connections, move the listener, and change the server session behind the pool. Driver idle reconnect addresses one narrow part of that sequence. Test failover with the real listener, DNS configuration, driver version, and authentication mode. Record how long the application takes to issue a successful new request and how it reports requests that were active during the switch. I inspect server sessions after recovery to confirm the pool created fresh connections, but I judge the business result in application logs.

What happens to cached data or session-level settings after reconnect? A new SQL connection does not carry temporary tables, session context, or a transaction from the old one. If the application relies on any of these, its recovery path must recreate them deliberately. That is why I describe the feature as connection resiliency, not transparent transaction replay. The setting is valuable when its boundary is understood and tested.

Decide Where Retry Belongs

Keep transient connection recovery in the driver and business operation retry in application code. Define which operations are safe to repeat. A read-only lookup is usually simpler than a payment or booking write. Give the application a clear error after its retry budget is exhausted. Endless retries can turn a short outage into a long queue of blocked users. What should the user see after the last attempt? Decide that before production does it for you.

I verify the setting with the actual installed driver, the actual pool, and a repeatable interruption. That is the only useful meaning of "we enabled retry." A string in a configuration file is not a network test.

Related reading on this blog: Connection Retry Logic for Cloud Databases and SSMS and Execution Timeout: SQL in Sixty Seconds 209.

A retry setting is not a transaction recovery plan, it is help for a narrow connection failure.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.