Replication — Database Scaling

Database replication means maintaining copies of the same database data on multiple database servers.

Replication is mainly used for high availability, read scaling, failover, disaster recovery, and reducing regional dependency.

Why Do We Need Replication?

With only one database server, a database failure can affect the entire application.

flowchart LR
    U[Users] --> API[Application]
    API --> DB[(Single Database)]

    DB --> F[Database Failure]
    F --> D[Application Impact]

With replication, another database maintains a copy of the data.

flowchart LR
    API[Application] --> P[(Primary)]

    P -->|Replication| R1[(Replica 1)]
    P -->|Replication| R2[(Replica 2)]

Complete Example — E-Commerce Order

Suppose an e-commerce application creates this order:

OrderId   = 1001
Customer  = 501
Amount    = 5000
Status    = Paid

The application writes the order to the primary database.

sequenceDiagram
    participant API as Order API
    participant P as Primary DB
    participant R1 as Replica 1
    participant R2 as Replica 2

    API->>P: INSERT Order 1001
    P->>P: Commit Transaction

    P->>R1: Replicate Order 1001
    P->>R2: Replicate Order 1001

    R1->>R1: Store Order
    R2->>R2: Store Order

Now the data exists on multiple database nodes.

SQL Server Example — Create Source Database

For learning, we can create a simple database and table:

CREATE DATABASE OrderDb;
GO

USE OrderDb;
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 DEFAULT SYSUTCDATETIME()
);
GO

INSERT INTO Orders
(
    OrderId,
    CustomerId,
    Amount,
    Status
)
VALUES
(
    1001,
    501,
    5000.00,
    'Paid'
);
GO

SELECT *
FROM Orders;

Output

OrderId | CustomerId | Amount  | Status | CreatedAt
--------|------------|---------|--------|---------------------------
1001    | 501        | 5000.00 | Paid   | 2026-09-27 ...

In a real SQL Server production environment, replication is configured using SQL Server's supported replication or high-availability technologies depending on the requirement. The exact setup depends on whether the goal is transactional replication, reporting, high availability, or disaster recovery.

SQL Server Replication Types

SQL Server provides different replication models for different requirements.

1. Snapshot Replication

A snapshot of the data is periodically generated and delivered to subscribers.

flowchart LR
    P[(Publisher)] --> S[Snapshot]
    S --> D[(Subscriber)]

This can be useful when data does not need to be continuously synchronized.

2. Transactional Replication

Changes are continuously propagated from the publisher to subscribers.

flowchart LR
    P[(Publisher)] --> D[Distribution]
    D --> S1[(Subscriber 1)]
    D --> S2[(Subscriber 2)]
    D --> S3[(Subscriber 3)]

It is commonly used when changes need to be delivered with low latency to subscribers, such as reporting or distributed read workloads.

3. Merge Replication

Multiple databases can make changes and later synchronize those changes.

flowchart LR
    A[(Database A)] <-->|Sync| B[(Database B)]
    B <-->|Sync| C[(Database C)]
    C <-->|Sync| A

Because multiple locations can modify data, conflict handling becomes important.

Replication vs High Availability

This is an important interview distinction.

Replication Lag

When replication is asynchronous, the replica can temporarily be behind the primary.

sequenceDiagram
    participant API
    participant P as Primary
    participant R as Replica

    API->>P: UPDATE Orders SET Status = 'Shipped'
    P-->>API: Success

    Note over P,R: Replication pending

    API->>R: SELECT Status
    R-->>API: Paid

    P->>R: Replicate Update
    R->>R: Status = Shipped

This is called replication lag.

Read-After-Write Problem

Suppose a customer updates an order and immediately requests it again.

sequenceDiagram
    participant User
    participant API
    participant P as Primary
    participant R as Replica

    User->>API: Update Order
    API->>P: UPDATE Order
    P-->>API: Success

    User->>API: Get Order
    API->>R: SELECT Order
    R-->>API: Old Data

    Note over R: Replica has not caught up

Possible solutions:

• Read from the primary immediately after a write.
• Use session-aware routing.
• Use consistency-aware replica selection where supported.
• Accept eventual consistency where the business requirement allows it.

Synchronous vs Asynchronous Replication

flowchart TD
    A[Replication]

    A --> B[Synchronous]
    A --> C[Asynchronous]

    B --> D[Wait for Required Replica]
    B --> E[Lower Potential Data Loss]
    B --> F[Higher Write Latency Possible]

    C --> G[Do Not Wait]
    C --> H[Lower Write Latency]
    C --> I[Replication Lag Possible]

The detailed synchronous/asynchronous comparison is covered separately in the next concept.

Replication and Failover

Replication can provide the data copy required for failover.

flowchart TD
    P[(Primary)] --> R1[(Replica 1)]
    P --> R2[(Replica 2)]

    P --> M[Health Monitor]

    M --> C{Primary Failed?}

    C -->|No| P
    C -->|Yes| R1

    R1 --> N[Promote Replica]
    N --> A[Redirect Application]

The actual failover behavior depends on the database technology and configuration.

Replication Across Regions

Replication can also be used for disaster recovery.

flowchart LR
    A[(Primary - India)] -->|Replication| B[(Secondary - Singapore)]

    B --> C[Disaster Recovery]

    A --> F[Production Traffic]

If the primary region becomes unavailable, the secondary region can potentially be used for recovery depending on the architecture.

Replication vs Backup

Important: If someone accidentally deletes 1 million rows and that deletion is replicated, the replicas can also receive the deletion. A backup provides a separate recovery mechanism.

Complete System Design Example

flowchart TD
    U[Users] --> LB[Load Balancer]
    LB --> API[Application Servers]

    API --> C[(Redis Cache)]

    API -->|Writes| P[(Primary SQL Server)]

    API -->|Reads| R1[(Read Replica 1)]
    API -->|Reads| R2[(Read Replica 2)]

    P -->|Replication| R1
    P -->|Replication| R2

    P --> B[Backup Storage]

    P --> M[Monitoring]
    R1 --> M
    R2 --> M

When Would You Use Replication?

Interview Scenario

Interviewer: Your SQL Server database is handling millions of read requests. How can replication help?

Answer:

1. Keep the primary responsible for writes.
2. Create one or more replicas.
3. Replicate changes from the primary.
4. Route suitable read-only traffic to replicas.
5. Monitor replication health and lag.
6. Handle read-after-write consistency explicitly.
7. Keep backups independent from replication.
8. If the write workload becomes the bottleneck, evaluate partitioning or sharding instead of simply adding replicas.

Common Interview Questions

• What is database replication?
• Why do we need replication?
• What are the types of SQL Server replication?
• Snapshot vs Transactional vs Merge replication?
• Replication vs read replica?
• Replication vs backup?
• What is replication lag?
• How do you handle read-after-write consistency?
• How does replication support disaster recovery?
• Does replication increase write scalability?

Key Takeaways

• Replication copies database changes to other database servers.
• It can support read scaling, availability, reporting, and disaster recovery.
• SQL Server provides multiple replication technologies for different use cases.
• Asynchronous replication can introduce replication lag.
• Replication does not automatically mean high availability or automatic failover.
• Replication is not a replacement for backups.
• Always consider consistency, lag, failure handling, and recovery requirements.

Conclusion

Replication is a fundamental database scaling and availability technique. In a system design interview, explain not only how data is copied, but also why it is being replicated, how consistency is handled, what happens during failure, and how the application uses the replicas.