SQL Server Database Sharding: Strategies, Shard Keys, and Architecture

One SQL Server database holds every customer's orders, and you're weighing whether it can keep carrying the load. Sharding splits the rows across several databases with the same tables. The application reads a key from each row, such as the customer ID, and connects to the database that owns the row. Shard when you need a hard boundary between customers, or more write capacity than one server gives. For reads or maintenance alone, a readable secondary or table partitioning is the smaller change.

What SQL Server database sharding means

A shard is one of the databases that hold the split rows. Every shard has the same tables and holds its own share of the rows. In production, each shard usually runs on its own server, so the load is spread across machines. The value that picks the shard is the shard key: a customer ID, a tenant ID, a region code.

The routing rule maps a shard key to its shard. A shard is an ordinary database and doesn't know that it belongs to a set, so the routing rule lives outside the shards, usually in the application. On Azure SQL Database, Microsoft's Elastic Database client library keeps a map of the shards and opens a connection to the shard that holds a given key. Routing each request is still the application's job.

Sharding compared with vertical splits, partitioning and replication

Sharding as described above is horizontal: every shard has all the columns and a share of the rows. Vertical sharding splits the other way. It moves some columns, or whole tables, into another database. Both databases keep the primary key, so the rows can be matched again.

Table partitioning isn't sharding: all partitions of a single table or index must reside in the same database. It splits a table for easier maintenance, and you still connect to one database.

Readable secondaries solve a different problem, which is reads. An Always On availability group can carry readable secondary replicas. With read-only routing configured, the availability group listener sends each read-intent connection to a readable secondary. Queries on a secondary run under snapshot isolation, whatever isolation level they set. Its data usually trails the primary by a few seconds.

If you need somewhere to run reports, a readable secondary is a smaller change than sharding. If the pressure is on maintenance windows rather than on writes, start with partitioning or data compression.

When sharding makes sense

Four situations justify splitting the data:

• a multi-tenant system, where one tenant's load must not reach another, and a separate database is a harder boundary than a TenantID column
• data residency, where customers of different regions have to be stored in different countries
• a write rate that one instance can't absorb
• maintenance on one group of customers that has to leave the others untouched

In each case, every shard holds fewer rows, so its indexes, backups and restores are smaller.

Transactions get more expensive. A transaction that spans two databases on the same instance is a distributed transaction, and the instance manages it internally. A transaction that reaches another instance through a linked server is promoted to a distributed transaction that MS DTC coordinates. Inside an MS DTC transaction, you can't set a savepoint: SAVE TRANSACTION isn't supported. Design each transaction to stay inside one shard.

Consistency gets weaker. A foreign key can reference only tables in the same database, so a rule that spans shards has to live in triggers or in the application. A report over all customers has to be assembled from every shard. Every migration runs once per shard, and if the migration fails on one shard, that shard stays on the old schema. Each shard is also one more database to back up, patch and monitor.

How to choose a shard key

The key has to appear in almost every important query. A query without the key has no shard to go to, so it has to read every shard. The key also has to spread rows and writes evenly, or one database does the work while the others idle. And the key has to be stable. Moving a row to another database takes a delete and an insert over two connections.

If your most frequent queries don't filter by the key you picked, every one of them reads all shards. Look again at partitioning or at readable secondaries.

Build two shards in sqlcmd

The statements below split one table of orders across two databases on one SQL Server 2025 instance, with the customer as the shard key. In production, the shards usually sit on separate instances, and only the connection strings change.

1. Connect with sqlcmd

sqlcmd -S localhost -U sa -C

-S names the server and -U the login. Leave out -P, and sqlcmd prompts for the password. The sqlcmd documentation calls -P insecure. -C trusts the server's certificate without validating it. Use it for a test server with a self-signed certificate: the sqlcmd of the mssql-tools18 package validates the certificate by default.

The login needs the CREATE DATABASE, CREATE ANY DATABASE or ALTER ANY DATABASE permission. If you still have to install SQL Server, start with creating a database in SQL Server.

2. Create one database per shard

CREATE DATABASE OrdersDB1;
GO
CREATE DATABASE OrdersDB2;
GO

GO isn't a T-SQL statement. It's a command that sqlcmd recognizes as the end of a batch. At each GO, sqlcmd sends the statements typed since the previous GO to the server.

3. Create the same table in every shard

USE OrdersDB1;
GO
CREATE TABLE dbo.Orders (
    OrderId     int  NOT NULL PRIMARY KEY,
    CustomerId  int  NOT NULL,
    OrderDate   date NOT NULL,
    Quantity    int  NOT NULL
);
GO

Run the same CREATE TABLE after USE OrdersDB2;. Keep CustomerId in every row, even though the shard's name already says whether the customer is odd or even. With the column, a shard can be split again, and a query can check that each row sits in the right shard.

OrderId has no IDENTITY. An identity column's seed is the value for the first row loaded into the table, and each shard's table counts from its own seed. Both shards would hand out order 1, so the application assigns the number instead.

4. Route each row by its shard key

Send an odd CustomerId to OrdersDB1 and an even one to OrdersDB2. That rule is a hash of the simplest kind, CustomerId % 2, and the application computes the remainder before it opens a connection. For customer 3, the remainder is 1, so customer 3's orders go to OrdersDB1. In sqlcmd, USE takes the place of the application's connection to the chosen shard:

USE OrdersDB1;
GO
INSERT INTO dbo.Orders (OrderId, CustomerId, OrderDate, Quantity)
VALUES (1, 1, '2023-07-15', 5),
       (2, 3, '2023-07-16', 2);
GO

USE OrdersDB2;
GO
INSERT INTO dbo.Orders (OrderId, CustomerId, OrderDate, Quantity)
VALUES (3, 2, '2023-07-15', 1),
       (4, 4, '2023-07-16', 3);
GO

OrdersDB1 now holds orders 1 and 2, of customers 1 and 3. OrdersDB2 holds orders 3 and 4, of customers 2 and 4. A query for one customer reads one shard. A query for every order of 2023-07-15 has to read both.

Route, rebalance and report across shards

What a third shard does to the rule

Suppose the two shards fill up and you add a third database, OrdersDB3. The rule becomes CustomerId % 3, with remainder 1 on OrdersDB1, 2 on OrdersDB2 and 0 on OrdersDB3. For the first six customers:

Four of the six customers change shard, and all their orders move with the customers. Any six consecutive IDs give the same count. Microsoft's Sharding pattern warns that with any hash(key) mod N rule, adding or removing a shard reassigns most keys. The page names consistent hashing as the scheme that moves only a small share of the keys.

Keep the rule in a shard map

A shard map is a table with one row per customer, which names the customer's shard. Moving one customer then changes one row of the map. Keep the map in a small catalog database of its own:

CREATE DATABASE ShardCatalog;
GO
USE ShardCatalog;
GO
CREATE TABLE dbo.ShardMap (
    CustomerId     int     NOT NULL PRIMARY KEY,
    ShardDatabase  sysname NOT NULL
);
GO
INSERT INTO dbo.ShardMap (CustomerId, ShardDatabase)
VALUES (1, 'OrdersDB1'), (2, 'OrdersDB2'), (3, 'OrdersDB1'), (4, 'OrdersDB2');
GO

The application reads the map before it connects:

DECLARE @CustomerId int = 4;

SELECT ShardDatabase
FROM ShardCatalog.dbo.ShardMap
WHERE CustomerId = @CustomerId;
GO

The map's row for customer 4 names OrdersDB2. To move customer 4 to a new shard, OrdersDB3, copy the customer's orders there, update the customer's row in the map, and delete the orders from OrdersDB2. Every other customer stays where it is.

For a customer with no row in the map, the lookup returns nothing. The routing code has to handle that before it builds a connection string. A map with one row per customer grows with the customer list. A map of ID ranges stays small: each row holds the first and the last ID of a range, and the lookup uses BETWEEN.

Query across shards

While the shards share an instance, a view in a separate reporting database can combine them with three-part names:

CREATE DATABASE OrdersReporting;
GO
USE OrdersReporting;
GO
CREATE VIEW dbo.AllOrders
AS
SELECT OrderId, CustomerId, OrderDate, Quantity FROM OrdersDB1.dbo.Orders
UNION ALL
SELECT OrderId, CustomerId, OrderDate, Quantity FROM OrdersDB2.dbo.Orders;
GO
SELECT OrderDate, SUM(Quantity) AS Units
FROM dbo.AllOrders
GROUP BY OrderDate
ORDER BY OrderDate;
GO

The view returns the orders of both shards as one set, and the query adds up the units of each day. Microsoft calls such a view a partitioned view. A partitioned view can also route an INSERT, but only when each member table has a CHECK constraint with a range or a list of values. An odd-or-even rule fits neither form, so this view is for reading.

Once the shards sit on separate servers, each branch of the view needs a linked server and a four-part name, Server.Database.dbo.Orders. A report without the shard key in its WHERE clause then reads every server each time it runs. Copy the shards' rows into the reporting database on a schedule instead, so reports run on the reporting database rather than on the shards.

Each shard still needs its own plan for an instance that goes down, such as failover clustering. Each shard also has its own locks and deadlocks, which concurrency and deadlock management covers.

Keep every shard on the same schema in DbSchema

Before each release, you have to know that every shard still has the same tables, columns and indexes as the others. DbSchema compares a shard with a saved model of the schema and lists every difference.

1. Connect DbSchema to OrdersDB1 through the SQL Server JDBC driver, as Connect to Database shows. In Reverse Engineer the Database, tick the schema that holds Orders and press Load. DbSchema draws the tables on a diagram.
2. Save the model with File → Save Model to File, and commit the .dbs file with File → Git Collaboration, as Git Collaboration describes.
3. Add a connection to OrdersDB2 with Database → Add Connection. If the shard's schema has another name than the model's, set Schema Mapping on the connection's Access and Model tab.
4. Choose that connection with the connection button in the toolbar. When DbSchema asks how to bring the model and the database together, choose Compare, then decide difference by difference. In the Synchronize with the Database dialog that opens, press Compare.
5. The Synchronize Model and Database dialog lists one row per difference. In each row, choose the side that wins: the model or the database. Commit in the database runs the statements for the rows set to the database, and Generate SQL script writes them without running them.

Saving the model changes only the .dbs file, and Commit in the database changes only the connected shard. While DbSchema is connected to a shard, every change you confirm in the diagram runs in that shard at once. To edit the model alone, choose Database → Offline (connect later) first. The schema synchronization page covers each step.

To compare the data rather than the structure, connect to each shard in turn and run the same query in the SQL Editor. For a wider walkthrough, see Design SQL Server Schemas Visually with DbSchema.

Shard when you need isolation or write capacity that a smaller change can't give. Pick the key that your most frequent queries already filter by, and plan the routing rule and the reporting path with the key. Then download DbSchema, reverse-engineer one shard, and compare the others with its model. Connecting, reverse-engineering, the diagram and the SQL Editor are in the free Community Edition. Saving the model to a file and synchronizing it with each shard need DbSchema Pro.

Design and visualize
your database schema

Edit referenced records
in related tables

Query your data
visually too

Reuse the SQL
generated

Free Download