Multi-AZ Database — Database Scaling

Multi-AZ (Availability Zone) means running database infrastructure across multiple isolated Availability Zones within the same cloud region so that a failure in one zone does not take down the database service.

Why Do We Need Multi-AZ?

A single database server in one Availability Zone has a single-zone failure risk.

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

    B --> C[AZ-1]
    C --> D[(SQL Server)]

    C --> E[AZ Failure]

With Multi-AZ:

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

    B --> C[Primary
AZ-1]
    C -. Synchronous Replication .-> D[Standby
AZ-2]

    D --> E[Failover]

If AZ-1 becomes unavailable, the database service can fail over to the healthy infrastructure in AZ-2, depending on the database technology and configuration.

Multi-AZ vs Read Replica

Typical Architecture

flowchart TD
    A[Users] --> B[Load Balancer]
    B --> C[ASP.NET Core Web API]

    C --> D[Database Endpoint]

    D --> E[Primary
AZ-1]
    D -. Failover .-> F[Standby
AZ-2]

    E -. Synchronous Replication .-> F

The API does not normally need to know which Availability Zone currently owns the primary role.

Complete SQL Server Example

For SQL Server, a common HA implementation is Always On Availability Groups with replicas hosted on different infrastructure or Availability Zones.

The following SQL demonstrates the database and table that would participate in the HA configuration. The actual Availability Group creation requires Windows/SQL Server infrastructure configuration and cannot be represented as a single portable SQL script.

1. Create the Application Database

CREATE DATABASE OrdersDb;
GO

USE OrdersDb;
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

2. Insert Sample Data

INSERT INTO Orders
    (OrderId, CustomerId, Amount, Status, CreatedAt)
VALUES
    (1, 101, 1000.00, 'Completed', '2026-09-25'),
    (2, 102, 2500.00, 'Pending', '2026-09-26'),
    (3, 103, 4500.00, 'Completed', '2026-09-27');
GO

3. Verify Data

SELECT
    OrderId,
    CustomerId,
    Amount,
    Status,
    CreatedAt
FROM Orders
ORDER BY OrderId;
GO

Expected output:

OrderId  CustomerId  Amount   Status      CreatedAt
-------  ----------  -------  ----------  -------------------
1        101         1000.00  Completed   2026-09-25
2        102         2500.00  Pending     2026-09-26
3        103         4500.00  Completed   2026-09-27

SQL Server Availability Group Architecture

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

    B --> C[SQL01
Primary
AZ-1]

    C -. Synchronous Replication .-> D[SQL02
Secondary
AZ-2]

    D -. Failover .-> C

In a real SQL Server Always On deployment, the Availability Group Listener provides a stable connection endpoint for applications.

Check Availability Group Replicas

After an Availability Group has been configured, SQL Server DMVs can be used to inspect replica state.

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

Example output:

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

The actual output depends on the configured Availability Group.

Check Database Synchronization

SELECT
    DB_NAME(drs.database_id) AS DatabaseName,
    drs.is_primary_replica,
    drs.synchronization_state_desc,
    drs.synchronization_health_desc
FROM sys.dm_hadr_database_replica_states AS drs;
GO

Example output:

DatabaseName  is_primary_replica  synchronization_state_desc  synchronization_health_desc
------------  ------------------  ---------------------------  ----------------------------
OrdersDb      1                   SYNCHRONIZED                HEALTHY
OrdersDb      0                   SYNCHRONIZED                HEALTHY

ASP.NET Core Web API

The important design principle is to connect the API to the Availability Group Listener, not directly to SQL01.

Connection String

{
  "ConnectionStrings": {
    "OrdersDb":
      "Server=OrdersAgListener;Database=OrdersDb;Integrated Security=True;TrustServerCertificate=True;"
  }
}

Entity Model

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

    public int CustomerId { get; set; }

    public decimal Amount { get; set; }

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

    public DateTime CreatedAt { get; set; }
}

DbContext

public class OrdersDbContext : DbContext
{
    public OrdersDbContext(
        DbContextOptions<OrdersDbContext> options)
        : base(options)
    {
    }

    public DbSet<Order> Orders => Set<Order>();
}

Program.cs

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

builder.Services.AddControllers();

var app = builder.Build();

app.MapControllers();

app.Run();

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);
    }

    [HttpGet]
    public async Task<IActionResult> GetOrders()
    {
        var orders = await _db.Orders
            .AsNoTracking()
            .OrderBy(x => x.OrderId)
            .ToListAsync();

        return Ok(orders);
    }
}

API Request

GET /api/orders/2

Expected response:

{
  "orderId": 2,
  "customerId": 102,
  "amount": 2500.00,
  "status": "Pending",
  "createdAt": "2026-09-26T00:00:00"
}

What Happens When AZ-1 Fails?

sequenceDiagram
    participant API as ASP.NET Core API
    participant L as AG Listener
    participant P as Primary AZ-1
    participant S as Secondary AZ-2

    API->>L: Database Request
    L->>P: Route to Primary
    P-->>API: Response

    P-xL: AZ-1 failure

    L->>S: Failover
    API->>L: New Request
    L->>S: Route to New Primary
    S-->>API: Response

The API continues using the same listener.

Does Multi-AZ Mean Zero Downtime?

No.

Multi-AZ reduces downtime risk, but failover itself can take some time. Existing connections may be broken and in-flight requests may fail.

The application should therefore handle transient database failures correctly.

Multi-AZ and Connection Pooling

ASP.NET Core applications typically use connection pooling through the SQL Server provider.

After failover, existing pooled connections may no longer be valid. New connections should resolve the listener and connect to the current primary.

flowchart LR
    A[ASP.NET Core] --> B[Connection Pool]

    B --> C[AG Listener]

    C --> D[Current Primary]

    D -. Failure .-> E[New Primary]

Multi-AZ vs Multi-Region

Important Interview Point

Multi-AZ does not protect you from every failure.

It primarily addresses failures affecting one Availability Zone or infrastructure location within a region. Application bugs, accidental deletes, data corruption, and an entire regional outage require additional strategies such as backups and disaster recovery.

Interview Scenario

Interviewer: Your SQL Server database is running in one AWS/Azure Availability Zone. How would you improve availability?

Answer: I would deploy the database using a high-availability architecture across multiple Availability Zones. For SQL Server, Always On Availability Groups can provide synchronized replicas and failover. The ASP.NET Core API would connect through a stable database listener rather than a specific SQL Server node. I would also configure transient retry handling and maintain independent backups for recovery from data corruption or accidental deletion.

Common Interview Questions

• What is Multi-AZ?
• Why do we need Multi-AZ databases?
• Multi-AZ vs Read Replica?
• How does SQL Server support Multi-AZ high availability?
• What is an Availability Group Listener?
• What happens when the primary AZ fails?
• Does Multi-AZ guarantee zero downtime?
• Does Multi-AZ replace backups?
• Multi-AZ vs Multi-Region?
• How should ASP.NET Core connect to a Multi-AZ database?

Key Takeaways

• Multi-AZ protects database availability within a region.
• Primary and secondary infrastructure are placed in separate Availability Zones.
• SQL Server Always On Availability Groups are a common SQL Server HA approach.
• The ASP.NET Core application should use a stable database listener.
• Failover can still cause brief interruption and failed in-flight requests.
• Retry handling can help recover from transient failures.
• Multi-AZ is not a replacement for backups or disaster recovery.

Conclusion

ASP.NET Core Web API
        ↓
Availability Group Listener
        ↓
Primary — AZ-1
        ↓
Synchronous Replication
        ↓
Secondary — AZ-2
        ↓
AZ Failure
        ↓
Failover
        ↓
Secondary Becomes Primary

For interviews, remember: Multi-AZ is primarily an availability strategy, while Multi-Region is used when you also need protection from a regional failure.