Scaling SQL Server and When to Split Services
On this page
Learning Objective
Scale a SQL Server based system in the right order, tuning before adding hardware, using read replicas with read-your-writes safeguards, partitioning and sharding when needed, then decide whether to extract services using explicit criteria, extract along the module seams safely, and secure service-to-service communication.
Concept Explanation
Database scaling has an order of operations, and skipping steps is expensive. First, make the existing queries efficient: the indexes, projections, and N+1 fixes of Phase 4 usually yield tenfold improvements at no infrastructure cost. Second, cache reads that are repeated and tolerate slight staleness, as in Day 67. Third, scale up: a larger tier or more cores is often the cheapest option in engineering time. Fourth, scale reads out with replicas. Only when a single primary cannot handle the write volume or the data size do you reach for partitioning, sharding, or splitting the data across services. Each later step adds complexity that will be paid in every future feature, so exhaust the earlier ones first, and prove with measurements that you have.
Read replicas take read traffic off the primary. Azure SQL Database offers readable secondaries in several tiers, and Hyperscale supports named replicas; a connection string with ApplicationIntent set to ReadOnly routes to a replica. The catch is replication lag: a replica may be a fraction of a second behind, so a user who saves a change and immediately reloads might not see it, which breaks the feeling of correctness. The usual remedies are to route requests that follow a write, or the same user's requests for a short window afterward, to the primary, to send only reads that can tolerate staleness, such as feeds and reports, to replicas, and to return the fresh entity in write responses so the client does not need to reread. Design which reads are safe explicitly, and never send authorization checks or read-modify-write cycles to a replica, since a stale membership row could grant a permission that was just revoked.
For very large tables, partitioning divides data within one database by a key, commonly time, so old partitions can be archived or dropped cheaply and queries that filter on the key touch fewer partitions. Sharding divides data across separate databases, for multi-tenant products typically by tenant identifier, so each shard holds a subset of tenants and load is spread. It brings real costs: a shard map to find where a tenant lives, cross-shard queries that are hard or impossible, rebalancing when a tenant outgrows a shard, and schema changes to run everywhere. Notion described sharding its PostgreSQL data into hundreds of logical shards in 2021 after the single database could no longer keep up, and the engineering effort was substantial, which illustrates that sharding is a serious commitment.
Splitting into services is a separate decision from scaling data, and it is driven by organization and independence more than raw performance. Reasons that justify a service boundary include separate teams that need to deploy independently, parts with very different scaling or availability profiles, a need for fault isolation so one failure does not take everything down, different data ownership or compliance requirements, and technology that genuinely differs. Costs include network calls in place of function calls, distributed transactions replaced by sagas, harder debugging, more deployment pipelines, and security work for each new interface. Segment, a customer data company, published a well-known 2018 account of moving back from more than a hundred microservices to a monolith because the operational overhead outweighed the benefits, which shows that services are a tool with a price.
If you do extract a service, follow the seams from Day 70. Verify the module already owns its data and communicates through contracts and events. Move its tables to a separate database with a migration that copies and then switches over, following expand and contract. Replace in-process calls with a network API and replace the in-process event dispatch with a broker. Run both paths side by side under a flag, shift traffic gradually, and remove the old path, the strangler fig approach. Then secure the new boundary as a first-class part of the design: every service has its own managed identity, service-to-service calls authenticate with tokens for a specific audience, so a stolen token from one service cannot call another it should not reach, each service has its own database login with only its own permissions, and the network allows only the required paths. Do not create a distributed monolith, where services must deploy together and share a database; that keeps all the costs and none of the benefits.
| Step | Do this | Cost |
|---|---|---|
| 1. Tune | Indexes, projections, N+1 fixes, query plans | Lowest, biggest wins |
| 2. Cache | HybridCache and Redis for repeated reads | Low, invalidation care |
| 3. Scale up | Larger tier or more cores | Money, no code |
| 4. Read replicas | ApplicationIntent ReadOnly for tolerant reads | Lag handling, routing rules |
| 5. Partition or shard | By time, or by tenant across databases | High, permanent complexity |
| 6. Split services | When teams, scaling, isolation or compliance require it | Highest, network and operations |
Worked Example: Read Routing with Lag Safeguards, a Decision Matrix, an Extraction Plan and Service Authentication
The listings show replica routing, criteria for extraction, the steps to extract Notifications, and authenticated service-to-service calls.
// Two contexts: writes and read-your-writes on the primary, tolerant reads on a readable secondary.
builder.Services.AddDbContext<AppDb>(o => o.UseSqlServer(cfg.GetConnectionString("Primary")));
builder.Services.AddDbContext<ReadDb>(o => o.UseSqlServer(cfg.GetConnectionString("Replica"))); // ...;ApplicationIntent=ReadOnly
// The replica login has SELECT only, so a coding mistake cannot write through it.
public sealed class ConsistencyWindow(IMemoryCache cache)
{
// After a user writes, send their reads to the primary for a few seconds so they always see their own changes.
public void MarkWrite(Guid userId) => cache.Set($"rw:{userId}", true, TimeSpan.FromSeconds(5));
public bool RecentlyWrote(Guid userId) => cache.TryGetValue($"rw:{userId}", out _);
}
public sealed class FeedReader(AppDb primary, ReadDb replica, ConsistencyWindow window, ICurrentUser user)
{
public IQueryable<Post> Posts() => window.RecentlyWrote(user.Id) ? primary.Posts : replica.Posts; // tolerant read: lag is acceptable
// Authorization and read-modify-write NEVER use the replica: a stale membership row could grant revoked access.
public Task<bool> IsMemberAsync(Guid projectId, CancellationToken ct) =>
primary.ProjectMembers.AnyAsync(m => m.ProjectId == projectId && m.UserId == user.Id, ct);
}Decision matrix: should we extract Notifications into its own service?
Criterion Evidence needed Score
Independent deploy cadence needed Two teams blocked by shared releases > 2x per month yes / no
Very different scaling profile Peak load 20x the rest of the system on its own yes / no
Failure isolation required Email provider outages have caused API incidents yes / no
Own data and clear contract already Module has its own schema and only contracts (Day 70) yes / no
Team ownership exists A team that will own, deploy, and be on call for it yes / no
Rule: extract only when at least three are "yes", including a clear contract and an owning team.
If the honest answer is "it feels cleaner", stay a module. Re-evaluate quarterly.Extraction plan (strangler fig), each step deployable and reversible
1. Contract first: Notifications exposes only INotificationService and NotificationRequested events; add contract tests.
2. Separate data: give the module its own database; copy data with expand/contract; dual write behind a flag; verify counts.
3. Add the network API: same operations over HTTP, versioned, with an OpenAPI document; the host calls it through a typed client with resilience (Day 69).
4. Switch the transport: in-process event dispatch becomes Service Bus topics with idempotent consumers (Day 66).
5. Route traffic gradually: a flag sends 1%, 10%, 100% of calls to the service; automatic rollback on error-rate breach (Day 89).
6. Remove the in-process implementation and the shared tables; record an ADR with the reasons and the results.// Service-to-service authentication: the caller proves its identity with a token for THIS audience only.
builder.Services.AddHttpClient<NotificationsClient>(c => c.BaseAddress = new Uri("https://notifications.internal.example/"))
.AddHttpMessageHandler(sp => new ManagedIdentityTokenHandler(new DefaultAzureCredential(), scope: "api://notifications/.default"))
.AddStandardResilienceHandler();
public sealed class ManagedIdentityTokenHandler(TokenCredential credential, string scope) : DelegatingHandler
{
protected override async Task<HttpResponseMessage> SendAsync(HttpRequestMessage request, CancellationToken ct)
{
var token = await credential.GetTokenAsync(new TokenRequestContext([scope]), ct); // cached and refreshed by the credential
request.Headers.Authorization = new("Bearer", token.Token);
return await base.SendAsync(request, ct);
}
}
// The Notifications service validates the token: audience is itself, and the caller must be the API's identity (an app role).
builder.Services.AddAuthentication().AddJwtBearer(o => { o.Authority = authority; o.Audience = "api://notifications"; });
builder.Services.AddAuthorizationBuilder().AddPolicy("CallerIsTaskBoardApi",
p => p.RequireAuthenticatedUser().RequireClaim("roles", "Notifications.Send").RequireClaim("azp", apiClientId));
app.MapPost("/notifications", Send).RequireAuthorization("CallerIsTaskBoardApi");
// A token stolen from any other service, or a user token, cannot call this endpoint: wrong audience, missing role, or wrong caller.Whatever the choice, record it in an architecture decision record with the measurements that led to it, so the next engineer knows why the system looks as it does and what would change the decision.
Real-World Examples
- Notion published an account in 2021 of sharding its monolithic PostgreSQL database after growth threatened to exhaust its capacity, splitting data by workspace identifier into 480 logical shards spread over 32 physical databases. The post describes the months of preparation, the dual writes and verification used to migrate safely, and the fact that sharding was chosen only after simpler options were exhausted.
- In 2018, Segment's engineering team described in Goodbye Microservices how a system that had grown to more than a hundred services for its destination integrations became slower to change and harder to operate, and how consolidating into a single service restored developer productivity. The account is a widely cited counterweight to splitting by default, and it stresses that the cost of services shows up in team velocity and operations.
Review Questions & Answers
Replicas can lag behind the primary, so a membership row that was just revoked may still exist on the replica and grant access that should be denied. Permission decisions must use the most current data, so they go to the primary, while only reads that can tolerate slightly stale data use replicas.
It is a set of services that must be deployed together and share a database or tightly coupled contracts, so it keeps all the costs of distribution, such as network calls, complex debugging, and multiple pipelines, without the independence that services are meant to provide. A well-structured single deployable is simpler and more reliable.
Query and index improvements often deliver order-of-magnitude gains at almost no operational cost, while replicas and shards add permanent complexity to every feature and operation. Measuring and exhausting cheap options first avoids paying for complexity that better queries would have made unnecessary.
A token issued for one service's audience is rejected by other services, so an attacker who steals it cannot use it to call unrelated services. Combined with role and caller checks, each service accepts only the specific callers that need it, which contains a compromise to a small part of the system.