Testing Database Failover Before It Happens in Production

A database can work perfectly for months and still fail when an application needs it most. A server restart, network interruption, storage problem, or database maintenance operation can make the primary database unavailable.

Applications that depend on a single database connection often struggle during these situations. Users may receive errors, transactions may fail, and background jobs may stop working.

Database failover helps an application continue working when its primary database becomes unavailable. However, configuring a secondary database is not enough. The application must also handle connection changes, retry failed operations safely, and recover from interrupted transactions.

The best way to find these problems is to test database failover before they happen in production.

This article explains how to plan and test database failover in an ASP.NET Core application using SQL Server as an example. The same testing principles apply to PostgreSQL, MySQL, and managed cloud databases.

What Is Database Failover?

Database failover is the process of moving database operations from an unavailable primary database to another database instance.

In a typical high-availability setup, the application connects to a primary database. A secondary instance is available to take over if the primary fails.

A simplified architecture looks like this:

ASP.NET Core Application
          |
          v
Database Endpoint
          |
          v
   Primary Database
          |
     Replication
          |
          v
   Secondary Database

When the primary database becomes unavailable, the database platform or cluster management system promotes an eligible secondary instance.

The application then reconnects through the database endpoint and continues its work.

The exact behavior depends on the database technology and deployment configuration. Some systems provide automatic failover, while others require an administrator or orchestration system to initiate it.

Failover does not guarantee that every operation will succeed. Requests already running during the transition may fail, and transactions that were not committed may need to be executed again.

Why Database Failover Testing Matters

A database cluster can appear healthy while still having problems that only become visible during a failure.

For example, an ASP.NET Core application may use a connection pool containing connections to the old primary database. After failover, some of those connections may no longer be usable.

Another problem occurs when the application retries a database operation without checking whether the previous attempt succeeded.

Consider a payment operation:

The application sends a payment record to the database.
The database commits the transaction.
The connection breaks before the application receives confirmation.
The application retries the operation.
The payment record may be inserted twice.

This is why failover testing must cover more than database availability. It must also verify application behavior and data consistency.

Prepare a Test Environment

Do not start failover testing by shutting down a production database.

Use a separate environment that resembles the production architecture as closely as possible.

The environment should include:

A primary database instance.
A secondary database instance.
Replication or availability configuration.
The same database driver and connection settings used by the application.
An ASP.NET Core application connected through the intended database endpoint.
Monitoring for database health and application errors.

For SQL Server, an Always On Availability Group can provide a primary and secondary replica. A managed SQL database service may provide a different failover mechanism.

The test should use the same connection strategy that production uses. Testing directly against individual database instances will not accurately represent an application that connects through a failover-aware endpoint.

Before starting, verify that replication is healthy and that the secondary is eligible for failover.

Configure the ASP.NET Core Database Connection

An ASP.NET Core application commonly uses Entity Framework Core to access SQL Server.

A typical registration looks like this:

builder.Services.AddDbContext<AppDbContext>(options =>
{
    options.UseSqlServer(
        builder.Configuration.GetConnectionString(
            "DefaultConnection"));
});

The connection string should point to the database endpoint designed for application connections.

For an SQL Server Availability Group, the connection may use a listener:

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=tcp:sql-listener,1433;Database=OrderDb;Integrated Security=True;Encrypt=True;TrustServerCertificate=False;Connect Timeout=15"
  }
}

This is an illustrative configuration. Authentication and encryption settings must match the deployment environment.

The important point is that the application should not need to change its connection string manually every time the primary database changes.

Enable connection resiliency

Entity Framework Core supports connection resiliency through provider-specific execution strategies.

For SQL Server:

builder.Services.AddDbContext<AppDbContext>(options =>
{
    options.UseSqlServer(
        builder.Configuration.GetConnectionString(
            "DefaultConnection"),
        sqlOptions =>
        {
            sqlOptions.EnableRetryOnFailure(
                maxRetryCount: 5,
                maxRetryDelay: TimeSpan.FromSeconds(10),
                errorNumbersToAdd: null);
        });
});

This configuration allows EF Core to retry certain transient database failures.

However, retries are not a replacement for failover configuration. They cannot make an unavailable database available, and they do not guarantee that an operation is safe to execute again.

For example, a failed read can often be retried safely. A write operation needs more care because the first attempt may have committed before the connection failed.

Create a Test Operation

Before testing failover, create an operation that the application can execute repeatedly.

For example, an order service can insert a new order using a unique operation identifier.

public class Order
{
    public int Id { get; set; }

    public Guid OperationId { get; set; }

    public string CustomerName { get; set; } = string.Empty;

    public decimal Amount { get; set; }

    public DateTime CreatedAtUtc { get; set; }
}

Create a unique index on OperationId:

protected override void OnModelCreating(
    ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Order>()
        .HasIndex(x => x.OperationId)
        .IsUnique();
}

The operation identifier provides a way to detect duplicate submissions.

The service can then save an order using a stable identifier:

public async Task<int> CreateOrderAsync(
    Guid operationId,
    string customerName,
    decimal amount,
    CancellationToken cancellationToken)
{
    var existing = await _context.Orders
        .FirstOrDefaultAsync(
            x => x.OperationId == operationId,
            cancellationToken);

    if (existing != null)
    {
        return existing.Id;
    }

    var order = new Order
    {
        OperationId = operationId,
        CustomerName = customerName,
        Amount = amount,
        CreatedAtUtc = DateTime.UtcNow
    };

    _context.Orders.Add(order);

    await _context.SaveChangesAsync(cancellationToken);

    return order.Id;
}

This is a basic example of idempotent request handling. In a production implementation, the lookup and insert must also handle concurrent requests safely. The unique database constraint remains the final protection against duplicate operation IDs.

If a connection fails after a transaction commits, the client can retry using the same operation ID rather than creating a new logical operation.

Test Different Failover Scenarios

A useful failover test plan should include several types of failure.

Test 1: Planned failover

A planned failover moves database responsibilities to another replica through the database platform's supported process.

During this test:

Start continuous application requests.
Record the primary database instance.
Initiate a planned failover.
Observe connection errors and request failures.
Verify that the application reconnects.
Check whether new database operations succeed.
Confirm that previously committed records remain available.

For SQL Server Availability Groups, use the supported failover procedure and ensure the target replica meets the required synchronization conditions.

Do not manually change database roles or manipulate replication state outside the platform's supported process.

Test 2: Unexpected primary failure

A planned failover does not reproduce every real-world failure.

In a controlled test environment, simulate an unexpected primary outage using the database platform's supported failure-injection or infrastructure controls.

Observe whether the cluster detects the failure and promotes an eligible secondary.

Measure the time between the primary becoming unavailable and successful application recovery.

This period is important because it affects application availability.

Test 3: Network interruption

Sometimes the database remains healthy, but the application cannot communicate with it.

Simulate a network interruption between the application and database using controlled network rules or a failure-testing tool.

Verify that:

Existing database connections fail cleanly.
New connections recover after connectivity returns.
The application does not enter an endless retry loop.
Requests do not remain blocked indefinitely.
Connection pools recover without requiring an application restart.

This test is particularly useful for applications running across multiple networks or cloud environments.

Test 4: Failover during a transaction

A database failure during a transaction can produce different results depending on when the failure occurs.

Test at least these situations:

Failure point	Expected behavior
Before transaction starts	Operation can usually be retried
During transaction execution	Transaction may be rolled back
After commit but before response	Commit outcome may be uncertain
During a read operation	Read may be retried if appropriate
During a multi-step business operation	Business consistency must be verified

The most important case is a connection failure after commit but before the application receives confirmation.

The application must not assume that every connection exception means the transaction was rolled back.

Handle Transactions Carefully

EF Core execution strategies can retry operations, but user-managed transactions require special handling.

When using a retrying execution strategy, execute the complete transaction through the strategy rather than creating a transaction outside it.

For example:

var strategy = _context.Database
    .CreateExecutionStrategy();

await strategy.ExecuteAsync(async () =>
{
    await using var transaction =
        await _context.Database.BeginTransactionAsync(
            cancellationToken);

    var order = new Order
    {
        OperationId = operationId,
        CustomerName = customerName,
        Amount = amount,
        CreatedAtUtc = DateTime.UtcNow
    };

    _context.Orders.Add(order);

    await _context.SaveChangesAsync(cancellationToken);

    await transaction.CommitAsync(cancellationToken);
});

This demonstrates how an execution strategy can wrap a transaction. It does not, by itself, guarantee exactly-once business execution if the commit outcome is unknown.

For important operations, use stable operation IDs, unique constraints, and a way to verify whether the previous attempt committed before repeating the business action.

Avoid putting external side effects such as payment gateway calls or email delivery inside a database retry delegate. A database retry could execute those external operations more than once.

Use an outbox pattern when database changes must reliably trigger external actions.

Measure Failover Recovery Time

A successful failover test should produce measurable results.

Two useful measurements are:

Recovery Time Objective (RTO): The maximum acceptable time required to restore the service.
Recovery Point Objective (RPO): The acceptable amount of data loss measured in time.

For example, an application may have an RTO target of 60 seconds and an RPO target of zero for a particular business operation.

These targets depend on the business requirements and the database replication configuration. They should not be assumed simply because a secondary replica exists.

Record the following during each test:

Metric	What it tells you
Failover detection time	How quickly the database platform detects failure
Promotion time	How long the secondary takes to become primary
Application recovery time	How long until successful application queries resume
Failed request count	How many user requests were affected
Retry count	How often database operations needed another attempt
Data consistency	Whether committed business records remain correct
Duplicate operation count	Whether retries created duplicate work

Application logs should include correlation IDs and operation IDs so that failed requests can be traced across the API, database, and background workers.

Common Mistakes During Failover Testing

Testing only whether the database is online

A database health check may return success while the application still has broken connections or cannot complete transactions.

Test real application operations, not just database availability.

Assuming retries solve every failure

Retries help with transient errors, but they cannot resolve permanent failures or guarantee that a write was not already committed.

Use idempotency and transaction-aware recovery.

Ignoring connection pools

Existing connections may become invalid during failover. Verify that the application can establish new connections through the configured database endpoint.

Testing without production-like traffic

A failover that works with one request may behave differently under heavy traffic. Run controlled concurrent workloads and monitor database and application resource usage.

Restarting the application after every failover

If the application requires a manual restart to recover, the failover process is not working as intended. Identify the connection, retry, or configuration problem instead of treating a restart as the recovery strategy.

Build a Repeatable Failover Test Plan

Failover testing should be a regular operational activity, not something performed only after a database incident.

A practical test plan should include:

Verify replication health before starting.
Record the current primary and secondary instances.
Start representative application traffic.
Perform a planned failover.
Measure application recovery time.
Verify transaction outcomes and data consistency.
Test an unexpected primary failure in a controlled environment.
Test network interruption and connection recovery.
Review logs, metrics, and failed operations.
Document the results and repeat after major infrastructure or application changes.

Run tests in a non-production environment first. Production failover exercises should follow an approved change process with a clear rollback and recovery plan.

Summary

Database failover is not complete just because a secondary database can become the primary. The application must also reconnect, recover from transient failures, protect transactions, and avoid duplicate business operations.

For ASP.NET Core applications, combine a failover-aware database endpoint, appropriate connection resiliency, idempotent operations, and transaction-safe retry handling.

Test planned failover, unexpected outages, network interruptions, and failures during transactions. Measure recovery time and verify data consistency after every test.

A database failover configuration is only useful when the application can continue operating correctly after the change. Regular testing helps identify those gaps before users experience them in production.