Disaster Recovery — Database Scaling

Disaster Recovery (DR) is the strategy used to restore an application's database, application services, and supporting infrastructure after a major failure.

Examples of disasters include:

• Complete cloud region failure
• Database server failure
• Storage failure
• Accidental data deletion
• Database corruption
• Ransomware or security incident
• Application infrastructure failure

The main interview question is:

“If your entire production environment disappears, how will you bring the system back?”

1. High Availability vs Disaster Recovery

flowchart LR
    A[Production System] --> B[High Availability]
    B --> C[Multi-AZ]
    B --> D[Automatic Failover]

    A --> E[Disaster Recovery]
    E --> F[Backups]
    E --> G[DR Region]
    E --> H[Recovery Plan]

2. Complete DR Architecture

Consider an ASP.NET Core application running in Region 1.

flowchart TD
    U[Users] --> G[Global DNS / Traffic Manager]

    G --> R1[Region 1 - Primary]
    G -. Failover .-> R2[Region 2 - DR]

    R1 --> A1[ASP.NET Core API]
    A1 --> DB1[(Primary SQL Server)]

    DB1 -. Database Replication .-> DB2[(DR SQL Server)]

    R2 --> A2[ASP.NET Core API]
    A2 --> DB2

    DB1 --> B[Backup]
    B --> S[Offsite Backup Storage]

Under normal conditions:

User
 ↓
Region 1
 ↓
ASP.NET Core API
 ↓
Primary SQL Server

After a major regional disaster:

User
 ↓
Global Traffic Manager
 ↓
Region 2
 ↓
ASP.NET Core API
 ↓
DR SQL Server

3. The Four Important DR Questions

Whenever you design disaster recovery, answer these four questions:

4. Hot, Warm and Cold DR

This is a very common system-design interview topic.

Hot DR

A Hot DR site is already running and is kept closely synchronized with production.

flowchart LR
    A[Users] --> B[Primary Region]
    A -. Failover .-> C[Hot DR Region]

    B --> D[Primary DB]
    D -. Near Real Time Replication .-> E[DR DB]

    C --> F[Running ASP.NET Core API]
    C --> E

When production fails, traffic can be moved to the DR environment quickly.

Example:

Region 1
API instances: Running
Database: Primary
Traffic: 100%

Region 2
API instances: Running
Database: Synchronized standby
Traffic: 0% or limited health traffic

Advantages:

• Very fast recovery
• Low RTO
• Infrastructure is already running
• Suitable for business-critical systems

Disadvantages:

• Expensive
• Requires duplicate infrastructure
• Requires continuous replication and monitoring

Warm DR

A Warm DR site has some infrastructure already available, but it is not running at full production capacity.

flowchart LR
    A[Primary Region] --> B[Production API]
    A --> C[Primary Database]

    C -. Replication .-> D[Warm DR Database]

    E[Warm DR Region] --> F[Small API Capacity]
    E --> D

During a disaster, additional application servers and supporting infrastructure are started or scaled up.

Example:

Primary Region
API: 20 instances
Database: Full production capacity

DR Region
API: 2 instances
Database: Replicated standby
Traffic: 0%

Disaster
↓
Scale API from 2 → 20
↓
Switch traffic

Advantages:

• Cheaper than hot DR
• Faster recovery than cold DR
• Good balance between cost and recovery speed

Disadvantages:

• Higher RTO than hot DR
• Additional scaling/startup time

Cold DR

A Cold DR site does not continuously run the complete production environment.

flowchart LR
    A[Primary Region] --> B[Production Database]

    B --> C[Backup Storage]

    D[Cold DR Region] --> E[Infrastructure Templates]
    C --> F[Restore Database]
    E --> G[Start Application]
    F --> G

After a disaster:

Restore infrastructure
        ↓
Restore database
        ↓
Deploy application
        ↓
Configure networking
        ↓
Run health checks
        ↓
Switch traffic

Advantages:

• Lowest infrastructure cost
• Useful when very low RTO is not required

Disadvantages:

• Slowest recovery
• More operational steps
• Potentially larger recovery window

5. Hot vs Warm vs Cold

The actual RTO depends on architecture and configuration; hot/warm/cold are architectural categories, not guaranteed time values.

6. Complete SQL Server Example

Let's build a simple Orders database that we will protect using backups.

Create Production Database

CREATE DATABASE OrdersDb;
GO

ALTER DATABASE OrdersDb
SET RECOVERY FULL;
GO

USE OrdersDb;
GO

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

Insert Production Data

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

SELECT *
FROM Orders
ORDER BY OrderId;
GO

Expected output:

OrderId  CustomerId  Amount   Status      CreatedAt
-------  ----------  -------  ----------  -------------------
1        101         1000.00  Completed   2026-09-27 09:00:00
2        102         2500.00  Completed   2026-09-27 09:10:00
3        103         4500.00  Pending     2026-09-27 09:20:00

Create Full Backup

BACKUP DATABASE OrdersDb
TO DISK = 'C:\SQLBackups\OrdersDb_Full.bak'
WITH
    INIT,
    COMPRESSION,
    STATS = 10;
GO

Create Transaction Log Backup

BACKUP LOG OrdersDb
TO DISK = 'C:\SQLBackups\OrdersDb_Log_01.trn'
WITH
    INIT,
    COMPRESSION,
    STATS = 10;
GO

7. Simulate a Disaster

Suppose the production database becomes unavailable or unusable.

flowchart LR
    A[ASP.NET Core API] --> B[Production SQL Server]
    B --> C[X Database Failure]

    D[DR Environment] --> E[Restore From Backup]

We can restore the database into a DR environment.

Restore Full Backup

RESTORE DATABASE OrdersDb
FROM DISK = 'C:\SQLBackups\OrdersDb_Full.bak'
WITH
    MOVE 'OrdersDb'
        TO 'C:\SQLData\DR_OrdersDb.mdf',
    MOVE 'OrdersDb_log'
        TO 'C:\SQLData\DR_OrdersDb_log.ldf',
    RECOVERY,
    STATS = 10;
GO

The logical file names should be confirmed using:

RESTORE FILELISTONLY
FROM DISK = 'C:\SQLBackups\OrdersDb_Full.bak';
GO

Verify the DR Database

USE OrdersDb;
GO

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-27 09:00:00
2        102         2500.00  Completed   2026-09-27 09:10:00
3        103         4500.00  Pending     2026-09-27 09:20:00

8. ASP.NET Core Web API in DR

The application should be designed so that the same API code can run in either region.

flowchart LR
    A[Global Traffic Manager] --> B[Region 1 API]
    A -. Disaster .-> C[Region 2 API]

    B --> D[Region 1 SQL]
    C --> E[Region 2 SQL]

appsettings.json

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

In production, secrets should be stored in a secure secret-management system rather than committed to configuration files.

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:long}")]
    public async Task<IActionResult> GetOrder(long 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);
    }
}

9. DR Failover Flow

sequenceDiagram
    participant U as User
    participant G as Global Traffic Manager
    participant R1 as Region 1 API
    participant DB1 as Region 1 DB
    participant R2 as Region 2 API
    participant DB2 as Region 2 DB

    U->>G: API Request
    G->>R1: Route to Primary Region
    R1->>DB1: Query
    DB1-->>R1: Data
    R1-->>U: Response

    Note over R1,DB1: Complete Region Failure

    U->>G: New API Request
    G->>R2: Route to DR Region
    R2->>DB2: Query
    DB2-->>R2: Data
    R2-->>U: Response

10. Hot DR Scenario

Suppose a banking application cannot tolerate a long outage.

flowchart TD
    A[Users] --> B[Global Traffic Manager]

    B --> C[Region 1]
    B -. Failover .-> D[Region 2]

    C --> E[20 API Instances]
    C --> F[Primary SQL]

    D --> G[20 API Instances]
    D --> H[Synchronized SQL Replica]

    F -. Continuous Replication .-> H

When Region 1 fails, Region 2 already has application capacity and database infrastructure.

This minimizes the amount of infrastructure that needs to be created during the incident.

11. Warm DR Scenario

Suppose an enterprise application needs reasonably fast recovery but cannot justify the cost of duplicating the entire production environment.

flowchart TD
    A[Region 1] --> B[20 API Instances]
    A --> C[Primary Database]

    C -. Replication .-> D[DR Database]

    E[Region 2] --> F[2 API Instances]
    E --> D

    G[Disaster] --> H[Scale DR API]
    H --> I[20 API Instances]
    I --> J[Switch Traffic]

The DR region already has the basic infrastructure, but application capacity is scaled up during recovery.

12. Cold DR Scenario

Suppose a low-traffic internal reporting system can tolerate several hours of downtime.

flowchart TD
    A[Production] --> B[Scheduled Backups]
    B --> C[Offsite Storage]

    D[Disaster] --> E[Provision Infrastructure]
    E --> F[Restore Database]
    F --> G[Deploy API]
    G --> H[Health Checks]
    H --> I[Switch Traffic]

The environment is created or started only when needed.

13. Complete DR Recovery Process

A good disaster-recovery runbook should contain a defined sequence.

flowchart TD
    A[Detect Disaster]
    A --> B[Declare DR Incident]
    B --> C[Check Primary Region]
    C --> D[Select Recovery Region]
    D --> E[Recover Database]
    E --> F[Start / Scale API]
    F --> G[Configure Dependencies]
    G --> H[Run Health Checks]
    H --> I[Validate Critical APIs]
    I --> J[Switch Global Traffic]
    J --> K[Monitor System]
    K --> L[Restore Normal Operations Later]

14. What About Cache?

This is often missed in interviews.

If your application uses Redis or another cache, the DR strategy must also consider the cache.

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

    D[DR Region] --> E[DR Cache]
    D --> F[DR Database]

    C -. Data Recovery .-> F

The application should not assume that cache data is the source of truth. After DR activation, cache entries can generally be rebuilt from the database according to the application's design.

15. What About External Dependencies?

Database recovery alone is not enough.

A complete DR design should consider:

• Database
• ASP.NET Core APIs
• Load balancers
• DNS / traffic management
• Cache
• Message queues
• Object storage
• Authentication services
• Secrets and certificates
• Monitoring and logging
• Third-party APIs

flowchart TD
    A[Complete DR]

    A --> B[Database]
    A --> C[ASP.NET Core API]
    A --> D[Cache]
    A --> E[Message Queue]
    A --> F[Storage]
    A --> G[Authentication]
    A --> H[Networking]
    A --> I[Monitoring]

16. Backup vs DR

17. DR Test

A DR strategy should be tested.

It is not enough to say:

"We have backups."

You need to prove that the system can actually recover.

flowchart LR
    A[Backup] --> B[Restore Test]
    B --> C[Start Application]
    C --> D[Run API Tests]
    D --> E[Validate Data]
    E --> F[Measure RTO]
    F --> G[Measure RPO]

A successful DR test should demonstrate that the documented recovery procedure actually works.

18. Complete Interview Scenario

Interviewer: You are designing an e-commerce platform. The application processes thousands of orders per second. Your primary region becomes completely unavailable. Explain your disaster-recovery design.

Answer:

I would deploy the application using a multi-region architecture. Region 1 would be the primary region and Region 2 would be the DR region. The ASP.NET Core API would be deployed in both regions, while a global traffic-management layer would route users to the healthy region.

For the database, I would maintain a replicated SQL Server database in the DR region using an appropriate SQL Server HA/DR technology. I would also maintain independent backups in durable offsite storage.

Depending on the business RTO and RPO, I would choose a hot, warm, or cold DR model.

For a business-critical ordering system, I might choose hot or warm DR. The exact choice would depend on the required recovery time, acceptable data loss, operational complexity, and cost.

During a regional failure, I would:

Detect Failure
     ↓
Declare DR
     ↓
Promote / Recover DR Database
     ↓
Scale DR API
     ↓
Validate Dependencies
     ↓
Run Health Checks
     ↓
Switch Global Traffic
     ↓
Monitor

Finally, I would measure the actual RTO and RPO through regular DR exercises.

19. Common Interview Questions

• What is Disaster Recovery?
• HA vs DR?
• What is a DR region?
• What is Hot DR?
• What is Warm DR?
• What is Cold DR?
• Hot vs Warm vs Cold DR?
• Which DR strategy has the fastest recovery?
• Why is Hot DR expensive?
• How would you design DR for SQL Server?
• How would ASP.NET Core switch to the DR database?
• What happens to the cache during DR?
• What happens to message queues?
• How do you test a DR strategy?
• How are RPO and RTO related to DR?
• Does Multi-AZ provide complete disaster recovery?
• Does database replication replace backups?

20. Key Takeaways

• Disaster Recovery is broader than database backup.
• HA handles failures while the system is operating; DR handles major disasters.
• Hot DR: infrastructure is already running and recovery is fastest.
• Warm DR: some infrastructure is running and additional capacity is activated during recovery.
• Cold DR: infrastructure is largely provisioned or restored after the disaster.
• Multi-region architecture can protect against complete regional failure.
• Backups remain important even when replication exists.
• ASP.NET Core should be deployable to both primary and DR environments.
• Global traffic management directs users to the healthy region.
• Cache, queues, authentication, secrets, storage, and third-party dependencies must also be considered.
• Always define and test RPO and RTO.

Conclusion

Production Region
       ↓
Multi-AZ High Availability
       ↓
Cross-Region DR
       ↓
Database Replication + Backups
       ↓
Hot / Warm / Cold Strategy
       ↓
Disaster
       ↓
Recover Database
       ↓
Recover Application
       ↓
Validate Dependencies
       ↓
Switch Global Traffic
       ↓
Service Restored

The most important interview takeaway: Disaster Recovery is not simply “having another database.” It is a complete, tested plan for recovering the database + application + infrastructure + dependencies within the required RPO and RTO.