One server has run out of room to grow, and there is no bigger box to buy. Sharding splits data and work across independent databases by a chosen key. You gain capacity and isolation. You pay for it in queries, transactions, and operations that must understand the split.
Prove a Single Server Is the Limit
Before splitting data, examine query plans, indexes, caching, hardware headroom, read replicas, partitioning, and retention. A poorly indexed query can be slow on every shard. A large archive can frequently be managed without distributing active transactions. Sharding is warranted when measured workload or data growth exceeds what a practical single-server design can deliver.
I ask for the limiting resource and a forecast: CPU, memory, storage, write throughput, or operational window. Then I test simpler changes and document their ceiling. That evidence keeps a data split from becoming a dramatic answer to an ordinary tuning problem.
Choose a Stable Shard Key
A shard key determines where a row lives. TenantID is common when most operations stay within one tenant. CustomerID, region, or another domain key can work if it keeps related transactions together. The key should be stable, present in common requests, and distribute load reasonably. Changing a shard key later can require moving large amounts of data.
A simple modulo of numeric IDs spreads rows but makes rebalancing difficult as the shard count changes. A routing catalog or consistent mapping layer gives more control. I prefer a key tied to business boundaries that the application already understands, then test whether a few large tenants create hotspots.
Build a Routing Contract for Sharding
The application needs a reliable way to map a shard key to a database endpoint. Central routing metadata should be cached carefully and updated atomically during moves. Every query must carry the key or have a defined fan-out behavior. Missing shard keys should produce a clear error, not a silent query against a default server.
This example shows a simple routing lookup table. It does not implement migration or high availability, but it makes the mapping explicit. Use secure endpoint configuration rather than embedding credentials in the table.
CREATE TABLE dbo.ShardMap
(
TenantID bigint NOT NULL PRIMARY KEY,
ShardName sysname NOT NULL,
RoutingVersion bigint NOT NULL
);
SELECT ShardName, RoutingVersion
FROM dbo.ShardMap
WHERE TenantID = 42;Keep Transactions Local
A transaction confined to one shard resembles an ordinary database transaction. A transaction that updates two shards needs distributed coordination or an application workflow with compensating actions. That is more complex to test and recover. Design aggregates and ownership boundaries so routine writes stay local where possible.
I identify business operations that cross tenants or regions before choosing the key. Invoices, global inventory, and shared identity can create hidden cross-shard work. A design that makes the common transaction local is valuable even if some rare reports need extra effort.
Expect Cross-Shard Query Cost
A report over all customers can no longer scan one database. It must query each shard, combine results, handle partial failures, and align data versions. Pagination and sorting across shards are particularly challenging. A separate analytical store or precomputed aggregate can be simpler than real-time fan-out for broad reports.
The application must define what happens if one shard is unavailable. Is a partial total acceptable with a warning, or must the report fail? I make that product decision explicit. A cross-shard COUNT is not just one SQL statement with a longer server name.
Plan Rebalancing From Day One
Some shards will grow faster than others. A move needs a controlled sequence: copy data, capture ongoing changes, verify counts and checksums, switch routing, then retire the old copy after a safety period. The exact mechanism depends on application writes and consistency requirements. A routing version can help clients detect a move.
This read-only query illustrates a basic per-tenant size signal within one shard. It is only a count. Real balancing also needs bytes, CPU, and write rate.
SELECT TenantID, COUNT_BIG(*) AS order_count
FROM dbo.Orders
GROUP BY TenantID
ORDER BY order_count DESC;Accept That Sharding Multiplies Operations
Every shard needs backups, restore tests, integrity checks, schema migrations, monitoring, security, and capacity planning. Deployment must handle partial success: one shard can be on a different schema version if a rollout fails. Use migration tooling that records per-shard state and can resume safely. Do not assume a successful script on the first shard means the fleet is complete.
I count operational effort in the architecture decision. Ten small servers can require more care than one large server, even when each query is faster. Automation is essential, but automation needs clear failure reporting and a tested rollback path.
Protect the Routing Layer
The shard map is critical infrastructure. If it is unavailable or wrong, data can appear missing or writes can land in the wrong place. Back it up, restrict changes, log migrations, and make lookup behavior resilient. Use unique identifiers that remain unambiguous across shards. Global uniqueness and referential integrity across databases require application or service design.
A cache of routing data improves lookup speed but creates stale-map risk during moves. Define how long it can live and how clients refresh it. I prefer explicit versioning and controlled cutover to a mystery cache invalidation rule. Routing correctness is data correctness.
Adopt Sharding With a Threshold
Set a measurable threshold for when the existing architecture no longer meets response, capacity, or recovery goals. Pilot with a small domain and test a shard move, cross-shard report, backup restore, and partial outage. If those operations cannot be run confidently, the design is not ready for the core workload.
Sharding can unlock scale when independent data groups genuinely need independent resources. It also makes simple tasks distributed tasks. Use it after exhausting lower-cost improvements and with an operating model that can move data safely. The split should be a business boundary the team can explain without a diagram full of arrows.
A migration plan should also identify the source of truth during cutover. Dual writes create a window for mismatches unless conflict handling is explicit. Prefer a controlled copy, change capture, verification, and routing switch with one clear write owner. Practice reversing the route before moving the largest tenant.
Related reading on this blog: Database Sharding: How to Identify a Shard Key? and Table Partitioning for Slow Performance.
Sharding is not a faster table, it is a distributed data contract with operational cost.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
