SQL Replicas & Redis Cluster¶

SQL Replicas provide read scaling, while Redis Cluster is the new caching layer for supplier-agent mapping data.

Architecture Diagram¶

graph TB
    subgraph MAINDB["Primary Database (Physical - SPOF)"]
        MASTER["MainDB<br/>10.32.8.130:1988<br/>SQL Server 2019<br/>ALL WRITES"]
    end

    subgraph REPLICATION["SQL Server Replication"]
        direction LR
        MASTER -->|"Always On AG<br/>Async Replication"| R1["Replica 1<br/>10.32.8.143"]
        MASTER -->|"Always On AG"| R2["Replica 2<br/>10.32.8.149"]
        MASTER -->|"Always On AG"| R3["Replica 3<br/>10.32.8.85"]
        MASTER -->|"Always On AG"| R4["Replica 4<br/>10.32.8.39"]
        MASTER -->|"Always On AG"| R5["Replica 5<br/>10.32.8.37"]
        MASTER -->|"Always On AG"| R6["Replica 6<br/>10.32.8.9"]
    end

    subgraph SQL_LB["SQL Read Load Balancer"]
        SQLVIP["HAProxy SQL VIP<br/>10.32.8.5<br/>Nodes: 7, 8"]
    end

    subgraph REDIS["Redis Cluster (NEW - SP Data Cache)"]
        REDIS_M["Redis Master<br/>10.32.8.202"]
        REDIS_R1["Redis Replica 1<br/>10.32.8.203"]
        REDIS_R2["Redis Replica 2<br/>10.32.8.204"]

        REDIS_M -->|"Replication"| REDIS_R1
        REDIS_M -->|"Replication"| REDIS_R2
    end

    R1 & R2 & R3 & R4 & R5 & R6 --> SQLVIP

    subgraph API["API Servers"]
        APIS["7 API Servers"]
    end

    APIS -->|"WRITES"| MASTER
    APIS -->|"READS"| SQLVIP
    APIS -->|"SP Mapping Cache"| REDIS

    style MASTER fill:#ff6666
    style SQLVIP fill:#90EE90
    style REDIS fill:#ff9999

SQL Replica Inventory¶

Server	IP Address	Type	Host	Role
Replica 1	10.32.8.143	VM	XCP-1	Read-only replica
Replica 2	10.32.8.149	VM	XCP-2	Read-only replica
Replica 3	10.32.8.85	VM	XCP-2	Read-only replica
Replica 4	10.32.8.39	VM	XCP-3	Read-only replica
Replica 5	10.32.8.37	VM	XCP-1	Read-only replica
Replica 6	10.32.8.9	VM	XCP-3	Read-only replica

SQL Replication Configuration¶

Setting	Value
Replication Type	SQL Server Always On AG
Sync Mode	Asynchronous
Replication Lag	< 1 second typically
Load Balancer VIP	10.32.8.5
LB Nodes	10.32.8.7, 10.32.8.8
Distribution	Round-robin

Redis Cluster Configuration¶

Setting	Value
Cluster Type	3-node Master-Replica
Master Node	10.32.8.202
Replica Node 1	10.32.8.203
Replica Node 2	10.32.8.204
Hosting	Spread across XCP hypervisors
Purpose	Supplier-Agent mapping data cache
Status	Production - Running

Redis Cluster Diagram¶

graph TB
    subgraph REDIS_CLUSTER["Redis 3-Node Cluster"]
        MASTER["Redis Master<br/>10.32.8.202<br/>All Writes"]
        REP1["Replica 1<br/>10.32.8.203<br/>Read + Failover"]
        REP2["Replica 2<br/>10.32.8.204<br/>Read + Failover"]

        MASTER -->|"Async Replication"| REP1
        MASTER -->|"Async Replication"| REP2
    end

    API["API Servers"] -->|"Write"| MASTER
    API -->|"Read"| MASTER
    API -->|"Read"| REP1
    API -->|"Read"| REP2

    style MASTER fill:#ff9999
    style REP1 fill:#FFE4B5
    style REP2 fill:#FFE4B5

Redis Use Case¶

graph LR
    API["API Server"] -->|"Check Cache"| REDIS["Redis Cluster"]
    REDIS -->|"Cache Hit"| API
    REDIS -->|"Cache Miss"| SQL["SQL Replica"]
    SQL -->|"Data"| API
    API -->|"Cache Result"| REDIS

    style REDIS fill:#ff9999
    style SQL fill:#87CEEB

Cache Data	Description
SP Mapping	Supplier-Agent relationship data
Purpose	Reduce SQL read load
TTL	Configurable per data type
Eviction	LRU (Least Recently Used)

Read/Write Flow¶

Operation	Target	Details
All Writes	MainDB (10.32.8.130)	Single master, physical
All Reads	SQL VIP (10.32.8.5)	Load balanced across 6 replicas
SP Cache Read	Redis Cluster	Check cache first
SP Cache Miss	SQL Replica	Fallback to SQL

Last Updated: 2025-12-02