Sharding by Customer: Routing Queries to the Right Database

Splitting a database is easy compared with sending every request to the correct half. Sharding by customer keeps each customer's workload together and uses a map to locate it. The routing rules, shared reports, and customer moves become part of your application design.

Try Simpler Options Before Sharding by Customer

Confirm the actual bottleneck before splitting storage. Review query plans, indexes, retention, compression, resource sizing, and workload scheduling. Archiving cold data and separating reporting can remove pressure without changing every application request. Table partitioning helps selected maintenance and access patterns within one database, but it does not distribute one database across independent engines.

I ask what one database can no longer provide. Storage, write throughput, isolation, and operational recovery are different reasons. A measured limitation and a growth forecast justify a design discussion. An expensive query alone justifies tuning that query first. Sharding is a substantial application commitment, so give the simpler fixes a fair test before accepting its ongoing cost.

Build the Routing Directory for Sharding by Customer

The sample uses three new databases on one test instance. Two hold customer data and one holds the directory. This demonstrates routing syntax rather than horizontal hardware scaling. Run it only where you are authorized to create disposable databases. The guards stop the setup if any demonstration database already exists, preserving an earlier rehearsal.

USE master;
IF DB_ID(N'RoutingLab') IS NOT NULL OR DB_ID(N'ShardA') IS NOT NULL
   OR DB_ID(N'ShardB') IS NOT NULL
    THROW 50001,'Use fresh demonstration database names.',1;
CREATE DATABASE RoutingLab;
CREATE DATABASE ShardA;
CREATE DATABASE ShardB;
GO
USE RoutingLab;
CREATE TABLE dbo.Shard
    (ShardID int NOT NULL PRIMARY KEY,DatabaseName sysname NOT NULL UNIQUE,
     IsEnabled bit NOT NULL);
CREATE TABLE dbo.CustomerShard
    (CustomerID int NOT NULL PRIMARY KEY,ShardID int NOT NULL,
     RouteState varchar(12) NOT NULL,
     CONSTRAINT FK_CustomerShard_Shard FOREIGN KEY(ShardID) REFERENCES dbo.Shard(ShardID),
     CONSTRAINT CK_CustomerShard_State CHECK(RouteState IN('Active','Moving')));
INSERT dbo.Shard VALUES(1,N'ShardA',1),(2,N'ShardB',1);
INSERT dbo.CustomerShard VALUES(101,1,'Active'),(202,2,'Active');

Keep database names controlled by administrators, not supplied as arbitrary request text. The unique customer mapping prevents two active destinations in this simple design. Production directories also need versioning, move coordination, health policy, and cache invalidation. Choose how a request responds when a customer is unknown, disabled, or moving. It should not guess another shard.

Give Every Shard the Same Contract

Each shard needs compatible tables, types, constraints, and application schema versions. Include CustomerID in keys and predicates even when a test shard contains one customer. Real shards host several customers. A local OrderID alone does not identify an order across the estate. This setup deliberately creates the same table structure in both destinations.

USE ShardA;
CREATE TABLE dbo.CustomerOrder
    (CustomerID int NOT NULL,OrderID bigint NOT NULL,TotalCents bigint NOT NULL,
     CONSTRAINT PK_CustomerOrder PRIMARY KEY(CustomerID,OrderID));
INSERT dbo.CustomerOrder VALUES(101,1,2500);
GO
USE ShardB;
CREATE TABLE dbo.CustomerOrder
    (CustomerID int NOT NULL,OrderID bigint NOT NULL,TotalCents bigint NOT NULL,
     CONSTRAINT PK_CustomerOrder PRIMARY KEY(CustomerID,OrderID));
INSERT dbo.CustomerOrder VALUES(202,1,4100);
GO
USE RoutingLab;

I test routing with colliding local order identifiers on purpose. That exposes a client that silently treats an order number as globally unique. Either use a composite identity containing customer identity or choose a deliberate global identifier strategy. Foreign keys stay local to a database. Cross shard relationships require a different consistency and validation design.

Route Values as Parameters and Names as Identifiers

Look up one active route, validate the database, then quote its identifier. Database identifiers cannot be ordinary SQL parameters. Customer values can and should be parameters. The sample uses local three part names, so it does not require linked servers. For shards on different instances, route application connections to approved destinations instead of treating this local syntax as a universal router.

DECLARE @customer_id int=101,@database sysname,@sql nvarchar(max);
SELECT @database=s.DatabaseName
FROM dbo.CustomerShard AS c JOIN dbo.Shard AS s ON s.ShardID=c.ShardID
WHERE c.CustomerID=@customer_id AND c.RouteState='Active' AND s.IsEnabled=1;
IF @database IS NULL OR DB_ID(@database) IS NULL
    THROW 50002,'No active customer destination is available.',1;
SET @sql=N'SELECT CustomerID,OrderID,TotalCents FROM '+QUOTENAME(@database)
    +N'.dbo.CustomerOrder WHERE CustomerID=@customer;';
EXEC sys.sp_executesql @sql,N'@customer int',@customer=@customer_id;

The tenant predicate remains essential after routing. Routing chooses a location, while authorization determines which customer the caller can access. Avoid broad cross database privileges for every application login. Use a reviewed access design, such as explicit permissions or appropriately signed modules. Do not enable TRUSTWORTHY just to make a demonstration's permission problem disappear.

Use Synonyms as Stable Shard Names

A synonym hides a fixed object's physical name. It does not route dynamically by customer. Create one stable synonym per destination for selected administrative or routing queries. Never drop and recreate a shared synonym on every request to point at that request's customer. Other sessions see the same synonym and would race with the change.

CREATE SYNONYM dbo.OrdersOnA FOR ShardA.dbo.CustomerOrder;
CREATE SYNONYM dbo.OrdersOnB FOR ShardB.dbo.CustomerOrder;
SELECT CustomerID,OrderID,TotalCents
FROM dbo.OrdersOnA WHERE CustomerID=101;

Review permissions on the synonym and its base object. The synonym does not grant universal access or enforce customer isolation. Its base object can also be missing until execution time. Include resolution tests in deployment checks. Stable names simplify selected callers, but schema consistency and destination availability still belong in operational monitoring.

Account for Reports That Cross Customers

A local UNION ALL demonstrates a cross shard report. It needs an explicit authorization and completeness policy before it becomes a production reporting path. A missing shard should not quietly produce a smaller business total. At larger scale, feed a reporting store rather than repeatedly fan out every interactive report across all operational shards.

SELECT N'A' AS ShardName,CustomerID,SUM(TotalCents) AS TotalCents
FROM dbo.OrdersOnA GROUP BY CustomerID
UNION ALL
SELECT N'B',CustomerID,SUM(TotalCents)
FROM dbo.OrdersOnB GROUP BY CustomerID;

Define whether the report needs one consistent point in time or accepts independently collected shard data. Reconcile extraction checkpoints and delayed shards. Global uniqueness, reference data, schema rollout, and backup restore coordination become explicit tasks too. The databases got smaller, but the operational notebook did not. Splitting data creates work that a single database previously handled together.

Move a Customer With a Cutover Protocol

Moving a customer requires more than updating CustomerShard. Pause or fence writes, copy every related table, validate keys and totals, and switch the route atomically within the directory's protocol. Invalidate route caches and deal with in flight requests. Resume writes only after the new destination is authoritative. Keep a rollback plan that accounts for writes after cutover.

Which component stops a stale cached route from writing to the old shard? Answer that before the first move. Use routing epochs or another tested fencing rule when the application needs them. A RouteState value alone only helps callers that honor it. Compare customer scoped data at both locations and record validation evidence before removing the old copy under the retention policy.

Operate Sharding by Customer as a Critical Service

Monitor directory availability, shard health, schema versions, and unmapped customer requests. Back up the directory with a recovery plan that agrees with the shard data. Rehearse a shard restore and confirm that routes point to the recovered authoritative copy. Test duplicate requests and partial failures, because retries now cross more boundaries than one local transaction.

I favor a small routing contract that every caller follows. Sharding by customer works when placement, authorization, and movement stay explicit. The useful design is the entire lifecycle, not the dynamic SELECT alone. Keep the map controlled, the shard schema consistent, and the difficult global queries visible in the plan before moving production customers.

Related reading on this blog: Database Sharding: How to Identify a Shard Key? and One Trick of Handling Dynamic SQL to Avoid SQL Injection Attack?.

A shard map is not just a lookup table, it is a contract for data placement.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.