Master Database Load Balancing, Read/Write Splitting, and Sharding Techniques

1. Load Balancing Technology

Load‑balancing clusters consist of independent computers linked via network routers; together they appear as a single high‑performance server to clients.

1.1 Implementation Principle

A control layer intercepts connections between applications and databases, allowing the layer to direct traffic to specific databases based on current load and balancing policies.

1.2 Multi‑Database Data Synchronization

All servers must keep data synchronized in real time; otherwise, inconsistent reads occur. Tools such as Moebius for SQL Server embed a middleware component in each database to monitor changes, propagate them to other nodes, and choose synchronization strategies based on the originating SQL statement.

1.3 Pros and Cons

Strong scalability – add more database servers to increase throughput.

High maintainability – failed nodes are automatically detected and traffic is rerouted.

Improved security – data redundancy across multiple servers and placement behind an internal network.

Transparency – the cluster presents a single IP address to applications.

Limitations – cannot allocate load based on web‑server capacity; control‑layer failure can cripple the whole system.

2. Database Read/Write Separation

2.1 Implementation Principle

Read and write operations are directed to different servers: the primary database handles writes, while one or more replicas handle reads, reducing load and I/O pressure on the primary.

2.2 Implementation Method (SQL Server Replication)

Using publication/subscription replication, data changes from the primary (publisher) are sent to a distribution database, which then forwards them to subscriber databases. Replication types include snapshot, transactional, and merge.

2.3 Pros and Cons

Real‑time data may lag because replication is not instantaneous.

Large data volumes can degrade synchronization performance.

Application code must manage connections to multiple databases, increasing complexity.

Read replicas offer high performance, reliability, and scalability.

3. Database Sharding (Distributed)

3.1 Implementation Principle

Data is partitioned across multiple databases based on specific criteria, with routing rules directing queries to the appropriate shard, thus reducing load on any single server.

3.2 Types of Sharding

Vertical (or logical) sharding : separate databases by functional modules (e.g., orders, products, users) with different schemas.

Horizontal (or physical) sharding : split rows of the same table across databases while keeping identical schemas.

3.3 Implementation Methods

Range‑based sharding : allocate data by a range (e.g., orders by year) to different databases.

Hash‑modulo sharding : compute user_id % N to distribute records evenly across N databases.

Configuration‑based routing : store a mapping of keys to database identifiers in a central config database; each request looks up the target shard before querying.

3.4 Pros and Cons

Vertical sharding simplifies pressure distribution for clearly separated modules.

Horizontal sharding balances load but may require complex routing logic.

Range sharding can lead to uneven data distribution.

Hash sharding provides uniform distribution but complicates data migration and cannot align with server performance.

Configuration‑based routing offers flexibility but adds an extra lookup overhead.