MainDB & New Cluster

The MainDB is currently a single point of failure. A new HA cluster is planned to eliminate this risk.

---

Current Architecture (SPOF)

graph TB
    subgraph CURRENT["Current Architecture (SPOF)"]
        MAINDB_OLD["MainDB<br/>10.32.8.130<br/>Physical Dedicated<br/>SQL Server 2019<br/>⚠️ SINGLE POINT OF FAILURE"]
    end

    subgraph CLIENTS["Clients"]
        API["7 API Servers"]
        B2B["B2B Portal"]
        OTH["OTH Servers"]
    end

    API & B2B & OTH -->|"ALL WRITES"| MAINDB_OLD

    style MAINDB_OLD fill:#ff6666

---

Current MainDB Details

Setting	Value
Server IP	10.32.8.130
Port	1988
Type	Physical Dedicated
SQL Version	SQL Server 2019
Database	withinearthUpdated
Role	ALL WRITES - Central Master
Risk	⚠️ SINGLE POINT OF FAILURE

---

Planned HA Cluster

graph TB
    subgraph PLANNED["Planned Architecture (HA Cluster)"]
        subgraph PROXMOX_NEW1["New Proxmox 1 (Physical Dedicated)"]
            MAINDB_NEW["MainDB VM<br/>10.32.8.130 (same IP)<br/>SQL Server Standard<br/>Primary Node"]
        end

        subgraph PROXMOX_NEW2["New Proxmox 2 (Physical Dedicated)"]
            AG_NODE["AG Sync Node VM<br/>SQL Server Standard<br/>Synchronous Replica"]
        end

        MAINDB_NEW <-->|"Always On AG<br/>Synchronous Replication<br/>Real-time Sync"| AG_NODE
    end

    style MAINDB_NEW fill:#90EE90
    style AG_NODE fill:#90EE90

---

Planned Cluster Configuration

Component	Specification
Infrastructure	2 New Proxmox Physical Dedicated Servers
Proxmox 1 VM	MainDB (same IP: 10.32.8.130 after migration)
Proxmox 2 VM	AG Synchronous Replica Node
SQL Edition	SQL Server Standard Edition
Replication	Always On Availability Group
Sync Mode	Synchronous (real-time, zero data loss)
Failover	Automatic failover cluster

---

Migration Benefits

Current Risk	After Migration
Single physical server failure = total outage	Automatic failover to replica
No real-time backup	Synchronous replication (zero data loss)
Manual recovery required	Automatic recovery
Hours of potential downtime	Seconds of failover time

---

Failover Flow

sequenceDiagram
    participant APP as Applications
    participant P1 as Primary<br/>(Proxmox 1)
    participant P2 as Replica<br/>(Proxmox 2)

    Note over P1,P2: Normal Operation
    APP->>P1: Write data
    P1->>P2: Sync replication
    P2-->>P1: Acknowledge

    Note over P1,P2: Failure Scenario
    P1->>P1: Primary fails
    P2->>P2: Detects failure
    P2->>P2: Promotes to Primary
    APP->>P2: Redirected writes

---

Current vs Planned

Aspect	Current	Planned
Servers	1 Physical	2 Proxmox Physical
VMs	None	2 SQL VMs
SQL Edition	SQL Server 2019	Standard Edition
Replication	To read replicas (async)	AG Sync (real-time)
Failover	Manual	Automatic
Data Loss Risk	Potential	Zero (synchronous)
Downtime Risk	Hours	Seconds

---

Last Updated: 2025-12-02