Database Failover — Database Scaling

Database Failover is the process of switching database operations from a failed or unhealthy primary database to a healthy secondary database.

The goal is to keep the application available when the primary database fails.

Why Do We Need Failover?

Consider an application connected to one SQL Server:

flowchart LR
    A[ASP.NET Core Web API] --> B[Primary SQL Server]
    B --> C[(Database)]

If the database server fails, the application cannot process database requests.

With a secondary:

flowchart LR
    A[ASP.NET Core Web API] --> B[Primary SQL Server]

    B --> C[Secondary SQL Server]

    B -. Replication .-> C

When the primary becomes unavailable, traffic can move to the secondary.

flowchart LR
    A[ASP.NET Core Web API] --> B{Database Endpoint}

    B --> C[Primary]
    B --> D[Secondary]

    C --> E[X Failed]
    D --> F[Accepting Traffic]

Automatic vs Manual Failover

Important Terms

• Primary: Database currently accepting writes.
• Secondary: Replica capable of taking over.
• Failover: Switching database role.
• Failback: Returning workload to the original primary after recovery.
• Failover endpoint: Stable endpoint that allows the application to connect without knowing the current primary server.

ASP.NET Core Web API Architecture

A good design avoids hardcoding a specific primary server into the application.

flowchart LR
    A[Client] --> B[ASP.NET Core Web API]
    B --> C[Database Listener / Endpoint]
    C --> D[Primary]

    D -. Failure .-> E[Secondary]
    C --> E

The API continues using the same database endpoint while the underlying database role changes.

ASP.NET Core Connection Example

{
  "ConnectionStrings": {
    "OrdersDb":
      "Server=sql-listener;Database=OrdersDb;Integrated Security=True;TrustServerCertificate=True;"
  }
}

The API should connect to the stable listener rather than directly to sql-primary.

DbContext Registration

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

builder.Services.AddControllers();

Controller

[ApiController]
[Route("api/orders")]
public class OrdersController : ControllerBase
{
    private readonly OrdersDbContext _db;

    public OrdersController(OrdersDbContext db)
    {
        _db = db;
    }

    [HttpGet("{id:int}")]
    public async Task<IActionResult> GetOrder(int id)
    {
        var order = await _db.Orders
            .AsNoTracking()
            .FirstOrDefaultAsync(x => x.OrderId == id);

        if (order == null)
            return NotFound();

        return Ok(order);
    }
}

Complete SQL Server Example

For a simple demonstration, create a primary database and a secondary database containing the same schema.

Primary Database

CREATE DATABASE OrdersPrimary;
GO

USE OrdersPrimary;
GO

CREATE TABLE Orders
(
    OrderId INT PRIMARY KEY,
    CustomerId INT NOT NULL,
    Amount DECIMAL(18,2) NOT NULL,
    Status VARCHAR(30) NOT NULL,
    CreatedAt DATETIME2 NOT NULL
);
GO

INSERT INTO Orders
    (OrderId, CustomerId, Amount, Status, CreatedAt)
VALUES
    (1, 101, 1000.00, 'Completed', '2026-09-20'),
    (2, 102, 2500.00, 'Pending', '2026-09-21');
GO

SELECT *
FROM Orders;
GO

Expected output:

OrderId  CustomerId  Amount   Status      CreatedAt
-------  ----------  -------  ----------  -------------------
1        101         1000.00  Completed   2026-09-20
2        102         2500.00  Pending     2026-09-21

Secondary Database

CREATE DATABASE OrdersSecondary;
GO

USE OrdersSecondary;
GO

CREATE TABLE Orders
(
    OrderId INT PRIMARY KEY,
    CustomerId INT NOT NULL,
    Amount DECIMAL(18,2) NOT NULL,
    Status VARCHAR(30) NOT NULL,
    CreatedAt DATETIME2 NOT NULL
);
GO

INSERT INTO Orders
    (OrderId, CustomerId, Amount, Status, CreatedAt)
VALUES
    (1, 101, 1000.00, 'Completed', '2026-09-20'),
    (2, 102, 2500.00, 'Pending', '2026-09-21');
GO

This demonstrates the data that would be available on the secondary. In production, synchronization should be provided by the chosen HA/replication technology rather than manually copying rows.

Simulating a Failover

Before failover:

flowchart LR
    A[API] --> B[Primary]
    B --> C[(OrdersPrimary)]

    D[Secondary] --> E[(OrdersSecondary)]

    B -. Replication .-> D

After failover:

flowchart LR
    A[API] --> B[Database Listener]
    B --> C[Secondary]
    C --> D[(OrdersSecondary)]

    E[Old Primary] --> F[Unavailable]

The application should not need to change its API routes or business logic.

SQL Server Always On Availability Groups

In SQL Server environments, Always On Availability Groups can provide high availability with multiple replicas and automatic or manual failover depending on the configuration.

flowchart LR
    A[ASP.NET Core API] --> B[Availability Group Listener]

    B --> C[Primary Replica]
    C -. Synchronous Replication .-> D[Secondary Replica]

    D --> E[Failover Target]

The listener provides a stable connection endpoint while the primary replica can change.

Checking Availability Group State

In a configured SQL Server Always On environment, you can inspect replica state using DMVs.

SELECT
    ar.replica_server_name,
    ars.role_desc,
    ars.connected_state_desc,
    ars.operational_state_desc
FROM sys.availability_replicas ar
JOIN sys.dm_hadr_availability_replica_states ars
    ON ar.replica_id = ars.replica_id;
GO

Example output:

replica_server_name  role_desc   connected_state_desc
-------------------  ----------  ---------------------
SQL01                PRIMARY     CONNECTED
SQL02                SECONDARY   CONNECTED

The exact output depends on the actual Availability Group configuration.

What Happens During a Failure?

sequenceDiagram
    participant API as ASP.NET Core API
    participant L as AG Listener
    participant P as Primary
    participant S as Secondary

    API->>L: Database request
    L->>P: Route request
    P-->>API: Response

    P-xL: Primary failure

    L->>S: Failover
    API->>L: New database request
    L->>S: Route request
    S-->>API: Response

What About In-Flight Transactions?

Failover does not mean every in-flight transaction automatically succeeds.

A request that was executing when the primary failed may receive a connection or transaction error. The application should handle transient database failures appropriately.

flowchart TD
    A[API Request] --> B[Database Operation]
    B --> C{Primary Available?}

    C -->|Yes| D[Return Result]
    C -->|No| E[Transient Failure]

    E --> F[Retry Policy]
    F --> G[Database Listener]
    G --> H[New Primary]

ASP.NET Core Retry Example

Entity Framework Core can be configured with SQL Server's execution strategy:

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

This helps with transient failures, but retries should be designed carefully for write operations so that the application does not accidentally duplicate non-idempotent work.

Failover vs Failback

flowchart LR
    A[Normal]
    A --> B[Primary A]

    B --> C[Failure]
    C --> D[Failover]
    D --> E[Primary B]

    E --> F[Original Primary Recovered]
    F --> G[Failback]
    G --> B

Failover moves workload away from the failed primary.

Failback moves workload back after the original environment has recovered, if the operating procedure calls for it.

Important: Failover Is Not Backup

Important Interview Point

Replication alone does not automatically mean failover.

You need a mechanism to detect failure, determine the healthy target, switch the database role or endpoint, and reconnect clients.

Interview Scenario

Interviewer: Your production SQL Server primary suddenly goes down. What happens?

Answer: If the database is configured for high availability, the secondary can become the new primary through automatic or manual failover. The application should connect through a stable listener or database endpoint rather than a hardcoded primary server. ASP.NET Core can use transient-failure retry handling to recover connections. I would also consider transaction consistency, replication lag, RPO, RTO, and how the application handles in-flight requests.

Common Interview Questions

• What is database failover?
• Automatic vs manual failover?
• What is an Availability Group Listener?
• Why should an application use a listener instead of a primary server name?
• What happens to in-flight transactions during failover?
• How does ASP.NET Core handle transient database failures?
• Failover vs failback?
• Failover vs backup?
• Does replication guarantee zero data loss?
• What are RPO and RTO?

Key Takeaways

• Failover moves database workload to a healthy secondary.
• Automatic failover reduces manual intervention.
• Use a stable database listener/endpoint where supported.
• ASP.NET Core should handle transient connection failures appropriately.
• In-flight transactions may fail and require application-level handling.
• Failover and backup solve different problems.
• Always consider RPO and RTO when designing failover.

Conclusion

ASP.NET Core Web API
        ↓
Database Listener
        ↓
Primary Database
        ↓
Primary Failure
        ↓
Automatic / Manual Failover
        ↓
Secondary Becomes Primary
        ↓
API Continues

In a system-design interview, the key idea is simple: the application should depend on a stable database endpoint, while the HA layer handles which database is currently primary.