Day 5: Database per Service

Database per Service means every microservice owns a private datastore that no other service can read or write directly.

Intro

Database per Service means every microservice owns a private datastore that no other service can read or write directly. Other services get at that data only through the owning service’s API or through events it publishes. In our insurance claims system, the Claims service owns the claims database, the Policy service owns the policy database, and Claims can never run a SQL query against Policy tables. This is what gives each service the freedom to change its schema, choose its database technology, and scale on its own.

Why we need this

Business reasons

• Insurance teams (Claims, Policy, Billing, Fraud) release on different cadences. A regulatory change to billing must not wait for the claims release train.
• Different parts of the business have different data needs: claims need transactional integrity, fraud scoring needs fast lookups and analytics, document search needs full-text indexing.
• Ownership must be clear. When a data quality problem appears in policy data, exactly one team is accountable.

Technical reasons

• A shared schema is an implicit API. Any table or column can be depended on by anyone, so no service can safely rename a column, split a table, or change a data type.
• A shared database is a shared failure domain and a shared performance domain: one runaway report query in Billing can lock rows the Claims service needs.
• Independent scaling is impossible if all services hit one database server. You scale the whole database or nothing.
• Independent technology choice (SQL Server for claims, PostgreSQL for policy, Redis or Cosmos DB for read-heavy lookups) is only possible when nobody else is coupled to the engine.

What problem it solves

Problem (from the topic list): shared databases couple codebases, cause schema lockouts, and block independent scaling.

What goes wrong without it

Imagine the Claims service and the Policy service share one SQL Server database InsuranceDb. Over time:

1. The Claims team writes a query that joins Claims directly to Policies to show the policy holder’s name. It is quick and works.
2. The Billing team also reads Policies.PremiumAmount directly.
3. The Policy team wants to split Policies into Policies and PolicyCoverages and rename PremiumAmount to AnnualPremium.
4. They cannot. Two other teams’ code would break, and nobody has a complete list of who reads what. The change needs a cross-team meeting, a coordinated release, and a big-bang deployment.
5. Meanwhile a month-end billing report scans Policies and causes lock waits in the Claims intake flow. Claim submissions time out during month end.

The system looks like microservices (separate deployables) but behaves like a distributed monolith. You pay the operational cost of microservices and get none of the independence.

When it is needed (and when it is NOT)

It fits when

• Services are split along business boundaries (see Day 1 and Day 2) and each team owns its service end to end (Day 4).
• Different services have clearly different data shapes, load profiles, or compliance needs (for example, payment data with stricter controls).
• You need independent deployments and independent scaling.
• You are ready to handle the consequences: no cross-service joins, no cross-service ACID transactions, and eventual consistency.

It is overkill or wrong when

• You have a small team (roughly under 8 to 10 developers) and one deployable. A modular monolith with schema-per-module gives most of the benefit with far less complexity.
• Two “services” are so chatty and so transactionally coupled that they always change together. That is a sign the boundary is wrong; merge them rather than splitting their database.
• The domain needs strong cross-entity consistency on almost every operation and you have no appetite for sagas.
• You are mid-migration and cannot afford to split data yet (see Day 6, Shared Database as an interim step).

How to identify the problem (key signals)

1. Schema change paralysis. A column rename needs sign-off from three teams. Pull requests sit for weeks waiting for “who else uses this table?”.
2. Cross-service SQL. Search the code base: JOIN statements that reference another team’s tables, or one service’s connection string pointing at another’s schema.
3. One connection string everywhere. Every service’s configuration has the same server and database name.
4. Noisy-neighbour incidents. Slow query or lock wait metrics on one database spike whenever an unrelated service runs a batch job. Deadlock graphs show tables from different domains.
5. Coordinated releases. Deployments must be scheduled together because a migration script affects multiple services.
6. Unclear data ownership. When a bad value appears in a table, several teams have write access and nobody can say who wrote it.
7. Scaling is all-or-nothing. The database is at 90% DTU/vCore because of one workload, and the only fix is to buy a bigger tier for everything.

Flow Diagram

Each service owns a private store; cross-service data travels only through APIs and events.

flowchart LR
    CS["Claims Service"] --> CDB[("ClaimsDb - SQL Server")]
    PS["Policy Service"] --> PDB[("PolicyDb - PostgreSQL")]
    CS -- "GET /policies/id" --> PS
    PS -. "PolicyUpdated event" .-> BUS{{"Service Bus"}}
    BUS -.-> CS
    CS -- "update local snapshot" --> CDB
    CS -. "Direct SQL JOIN blocked" .-x PDB

Level 1: Beginner

Analogy. Think of each department in an insurance office having its own locked filing cabinet. If Claims needs a customer’s policy details, they do not open the Policy department’s cabinet; they walk over and ask the Policy clerk, who looks it up and hands over a copy. The Policy department can reorganize its cabinet any way it likes because nobody else depends on the folder layout. The clerk’s counter (the API) is the only contract.

Minimal example. Two services, each with its own database. Claims does not query the policy tables; it calls the Policy API.

Policy service (ASP.NET Core minimal API, .NET 10, owns PolicyDb):

using Microsoft.EntityFrameworkCore;

var builder = WebApplication.CreateBuilder(args);
builder.Services.AddDbContext<PolicyDbContext>(o =>
    o.UseNpgsql(builder.Configuration.GetConnectionString("PolicyDb")));

var app = builder.Build();

app.MapGet("/policies/{policyNumber}", async (string policyNumber, PolicyDbContext db) =>
{
    var p = await db.Policies.AsNoTracking()
        .Where(x => x.PolicyNumber == policyNumber)
        .Select(x => new PolicyDto(x.PolicyNumber, x.HolderName, x.Status, x.CoverageLimit))
        .FirstOrDefaultAsync();
    return p is null ? Results.NotFound() : Results.Ok(p);
});

app.Run();

public record PolicyDto(string PolicyNumber, string HolderName, string Status, decimal CoverageLimit);

public class Policy
{
    public int Id { get; set; }
    public string PolicyNumber { get; set; } = "";
    public string HolderName { get; set; } = "";
    public string Status { get; set; } = "Active";
    public decimal CoverageLimit { get; set; }
}

public class PolicyDbContext(DbContextOptions<PolicyDbContext> options) : DbContext(options)
{
    public DbSet<Policy> Policies => Set<Policy>();
}

Claims service (owns ClaimsDb, calls Policy over HTTP):

var builder = WebApplication.CreateBuilder(args);
builder.Services.AddDbContext<ClaimsDbContext>(o =>
    o.UseSqlServer(builder.Configuration.GetConnectionString("ClaimsDb")));
builder.Services.AddHttpClient("policy", c =>
    c.BaseAddress = new Uri(builder.Configuration["Services:Policy"]!));

var app = builder.Build();

app.MapPost("/claims", async (SubmitClaim cmd, ClaimsDbContext db, IHttpClientFactory f) =>
{
    var policy = await f.CreateClient("policy")
        .GetFromJsonAsync<PolicyDto>($"/policies/{cmd.PolicyNumber}");
    if (policy is null || policy.Status != "Active")
        return Results.BadRequest("Policy not active");

    var claim = new Claim { PolicyNumber = cmd.PolicyNumber, Amount = cmd.Amount, Status = "Submitted" };
    db.Claims.Add(claim);
    await db.SaveChangesAsync();
    return Results.Created($"/claims/{claim.Id}", claim);
});

app.Run();

public record SubmitClaim(string PolicyNumber, decimal Amount);
public record PolicyDto(string PolicyNumber, string HolderName, string Status, decimal CoverageLimit);
public class Claim { public int Id { get; set; } public string PolicyNumber { get; set; } = ""; public decimal Amount { get; set; } public string Status { get; set; } = ""; }
public class ClaimsDbContext(DbContextOptions<ClaimsDbContext> options) : DbContext(options)
{
    public DbSet<Claim> Claims => Set<Claim>();
}

Note that Claim stores only PolicyNumber (a reference by business key), not a foreign key into the Policy database. There is no cross-database foreign key.

Level 2: Intermediate

In a real .NET + Angular + database application the pain points are: reading data that lives elsewhere, keeping copies fresh, and keeping the UI simple.

Rule 1: reference by ID, not by foreign key. The Claims table stores PolicyNumber or PolicyId. Integrity across services is enforced by application logic (validate on write) and by events (react when a policy is cancelled).

Rule 2: keep a small local read model for data you need often. Calling the Policy API on every claim view adds latency and couples availability. Instead, Claims subscribes to PolicyCancelled and PolicyUpdated events and stores only the fields it needs (status, coverage limit) in a local table.

// ClaimsService: local projection of policy data (owned by Claims, only what Claims needs)
public class PolicySnapshot
{
    public string PolicyNumber { get; set; } = "";   // key
    public string Status { get; set; } = "";
    public decimal CoverageLimit { get; set; }
    public DateTime LastUpdatedUtc { get; set; }
}

// Handler for an integration event delivered via Azure Service Bus
public class PolicyUpdatedHandler(ClaimsDbContext db)
{
    public async Task HandleAsync(PolicyUpdated evt, CancellationToken ct)
    {
        var snap = await db.PolicySnapshots.FindAsync([evt.PolicyNumber], ct);
        if (snap is null)
        {
            snap = new PolicySnapshot { PolicyNumber = evt.PolicyNumber };
            db.PolicySnapshots.Add(snap);
        }
        // Ignore stale, out-of-order events
        if (evt.OccurredUtc <= snap.LastUpdatedUtc) return;

        snap.Status = evt.Status;
        snap.CoverageLimit = evt.CoverageLimit;
        snap.LastUpdatedUtc = evt.OccurredUtc;
        await db.SaveChangesAsync(ct);
    }
}

public record PolicyUpdated(string PolicyNumber, string Status, decimal CoverageLimit, DateTime OccurredUtc);

The event consumer must be idempotent (Day 17) and the publisher should use a transactional outbox (Day 12). Without those, the local copy can silently drift.

Rule 3: cross-service queries go through API composition (Day 10) or a dedicated read model (CQRS, Day 8). For the Angular claim details screen, do not have the browser call five services. Let a BFF or the Claims API compose the view.

// Angular 22 - standalone component, signals via toSignal(HttpClient)
import { Component, inject, signal } from '@angular/core';
import { HttpClient } from '@angular/common/http';
import { toSignal } from '@angular/core/rxjs-interop';

interface ClaimDetails {
  claimId: number;
  status: string;
  amount: number;
  policyHolder: string; // composed by the backend, not joined in the browser
}

@Component({
  selector: 'app-claim-details',
  template: `
    @if (claim(); as c) {
      <h2>Claim {{ c.claimId }} - {{ c.status }}</h2>
      <p>{{ c.policyHolder }} claims {{ c.amount | currency }}</p>
    } @else {
      <p>Loading...</p>
    }
  `,
})
export class ClaimDetailsComponent {
  private http = inject(HttpClient);
  claim = toSignal(this.http.get<ClaimDetails>('/api/claims/42'));
}

Rule 4: each service owns its migrations. Each service runs its own EF Core migrations against its own database in its own pipeline.

// Run migrations at deploy time as a separate step (preferred), not at app startup in production.
// dotnet ef migrations add AddClaimStatusIndex --project ClaimsService
// dotnet ef migrations bundle --project ClaimsService -o claims-migrate.exe   // executed by the pipeline

Rule 5: choose the datastore per workload, only when it adds value. Claims (transactional, relational) on Azure SQL; Policy on PostgreSQL; a claim-search index on Azure AI Search. Do not diversify for its own sake; every extra engine is extra operational skill your team must have.

Level 3: Advanced

Isolation levels (what “private” really means). “Database per service” is a logical rule, not a physical one. There are four realisations, in increasing isolation:

1. Private tables within a shared schema (weakest, needs discipline)
2. Schema per service in one database
3. Database per service on a shared server or elastic pool
4. Dedicated server or cluster per service (strongest, most expensive)

What matters is that access is enforced. Give each service its own database login that can only touch its own schema. Do not rely on convention.

-- SQL Server: enforce ownership with separate logins and schemas
CREATE LOGIN claims_svc WITH PASSWORD = '<from Key Vault>';
CREATE USER claims_svc FOR LOGIN claims_svc WITH DEFAULT_SCHEMA = claims;
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::claims TO claims_svc;
DENY  SELECT ON SCHEMA::policy TO claims_svc;   -- explicit block

On Azure, prefer Microsoft Entra managed identity authentication over passwords, with one identity per service.

Performance and scalability

• Cross-service reads become network calls. Measure p95 latency of composed calls and cap fan-out. Cache reference data (with a TTL and event-driven invalidation).
• Avoid the chatty N+1 problem across services: expose batch endpoints (GET /policies?ids=1,2,3) instead of looping.
• Each database can be tuned separately: different indexing, different maintenance windows, different tier or size.

Data consistency

• No distributed ACID transaction. Multi-service business operations use a Saga (Day 7). Design compensating actions up front.
• Accept and design for eventual consistency: show “pending” states in the UI.
• Referential integrity is your job. Plan for dangling references (a claim referencing a deleted policy) and use soft delete or tombstone events.

Security

• Smaller blast radius: a leaked credential exposes one service’s data, not everything.
• Data classification per service (PII, payment, health) allows differing encryption, retention, and auditing. Use Transparent Data Encryption by default, and Always Encrypted or column-level protections where required.
• Never let a reporting tool connect straight to service databases in production. Feed a separate analytics store from events or CDC.

Failure modes

• The owning service is down: dependants either fail or serve stale local copies. Decide per use case.
• Event consumer lag: local copies are stale. Monitor the lag and alert.
• Dual writes: updating your DB and publishing an event as two separate steps will eventually lose one of them. Use the outbox.

Common mistakes

• Splitting the database but leaving a “shared reference data” database that everyone reads. That is a shared database again.
• Creating a “data access service” that simply exposes generic CRUD over tables. That leaks the schema through an API.
• Copying entire entities into every consumer. Copy only the fields you need, and treat them as read-only.
• Running reporting queries against production service databases.
• Splitting the database before the service boundaries are stable.
• Ignoring the cost: ten small databases can cost more and take more operations effort than one.

Level 4: Expert and Architect view

Trade-off comparison

Patterns it combines with

• Saga (Day 7) for multi-service transactions
• CQRS (Day 8) and API Composition (Day 10) for cross-service reads
• Domain Event (Day 11) and Transactional Outbox (Day 12) for propagating changes
• Idempotent Consumer (Day 17) so replicated data stays correct
• Decompose by Subdomain (Day 2) to decide where the boundaries go
• Strangler Fig (Day 49) and Shared Database (Day 6) for getting there from a monolith

ADR (architecture review style)

Title: ADR-005 Each microservice owns a private database

Status: Proposed

Context: The claims platform is being split into Claims, Policy, Billing, and Fraud services owned by separate teams. Today all modules share one SQL Server database, and schema changes require cross-team coordination. Month-end billing jobs cause lock contention with claim intake.

Decision: Each service owns its data store. No service may connect to another service’s database. Data is exchanged only through service APIs and published integration events. Each service uses its own credentials (managed identity) and has its own migration pipeline. Default engines are Azure SQL Database (transactional services) and Azure Database for PostgreSQL flexible server; other engines require an ADR of their own.

Consequences: (+) independent schema evolution, release, and scaling; smaller blast radius; clearer ownership. (-) no cross-service joins or ACID transactions, so we adopt Sagas and eventual consistency; more databases to operate and monitor; reporting needs a separate analytics path; higher baseline cost. Mitigation: elastic pool for low-traffic services, platform-provided templates for migrations and monitoring.

Alternatives considered: shared database with schema per service (rejected as the target state, accepted as a temporary step), a single “data platform” team owning all data (rejected because it re-creates a central bottleneck).

Azure implementation

Services that support this pattern

• Azure SQL Database for relational, transactional services. Options include single databases and elastic pools.
• Azure Database for PostgreSQL flexible server for PostgreSQL workloads.
• Azure Cosmos DB for globally distributed or document-shaped data with per-service containers.
• Azure Cache for Redis for cache and fast lookups (a supplement, not a system of record).
• Azure Service Bus (queues and topics) or Event Grid for propagating events between services.
• Azure Key Vault for secrets, and Microsoft Entra ID managed identities for passwordless database access.
• Azure Container Apps or Azure Kubernetes Service (AKS) to host the services; Azure Monitor / Application Insights for observability.

How to configure

1. Create one database per service. For SQL, group low-traffic services in an elastic pool so they share compute and cost.
2. Turn on Microsoft Entra authentication and create a database user from each service’s managed identity (CREATE USER [claims-svc-identity] FROM EXTERNAL PROVIDER;), granting only the needed roles.
3. Put each database on a private endpoint inside a VNet and disable public network access.
4. Keep connection details in App Configuration or Key Vault, referenced by the service’s identity, not by shared secrets.
5. Enable backup retention, geo-redundancy where the recovery objective needs it, and diagnostic settings to a Log Analytics workspace.
6. Give each service’s pipeline permission to run migrations only against its own database.

Example connection with managed identity (Azure SQL, Microsoft.Data.SqlClient):

// appsettings: "ClaimsDb": "Server=tcp:sql-claims.database.windows.net;Database=ClaimsDb;Authentication=Active Directory Default;Encrypt=True;"
builder.Services.AddDbContext<ClaimsDbContext>(o =>
    o.UseSqlServer(builder.Configuration.GetConnectionString("ClaimsDb")));

Pricing and tier considerations (always confirm current figures on the Azure pricing pages before budgeting, as prices and offers change by region)

• Azure SQL Database offers DTU-based and vCore-based purchasing. vCore has provisioned and serverless compute; serverless can auto-pause when idle, which suits low-traffic services, dev, and test. Service tiers are General Purpose, Business Critical, and Hyperscale. Elastic pools share resources across many databases and are usually the cost-effective way to run many small per-service databases.
• Azure Database for PostgreSQL flexible server has Burstable, General Purpose, and Memory Optimized compute tiers. Burstable is for low or variable load and dev/test; General Purpose and Memory Optimized are for production. Storage is billed separately, and high availability (zone redundant or same zone) roughly doubles compute cost because a standby is provisioned.
• Cosmos DB is billed by provisioned throughput (RU/s), autoscale, or serverless; choose per container.
• Cost driver to plan for: the number of databases. Budget for baseline cost per database (or pool) plus backup storage, and use tags per service for cost attribution.

Reference architecture (text)

Azure Front Door and API Management sit at the edge. Behind them, Azure Container Apps host four services (Claims, Policy, Billing, Fraud) inside one VNet. Claims uses Azure SQL Database (in an elastic pool with Billing), Policy uses Azure Database for PostgreSQL flexible server, and Fraud uses Cosmos DB. Each database is reachable only through a private endpoint by its service’s managed identity. Services publish integration events to an Azure Service Bus topic (using the outbox pattern); subscribers such as Claims (for PolicyUpdated) update their local snapshots. Application Insights and Log Analytics collect traces and metrics; Key Vault holds any remaining secrets. A separate analytics path (for example events landed in Azure Data Lake and queried through Microsoft Fabric or Synapse) serves reporting so nobody queries service databases directly.

Teaching guide for my team

2-minute beginner explanation

“Each service has its own database and it is the only one allowed to touch it. If the Claims service needs a policy’s status, it asks the Policy service through its API. It never opens the Policy tables. Why? Because then the Policy team can change their tables any time without breaking anyone, and a slow query in one service cannot freeze another. The price we pay is that we can’t join across services in SQL, so we ask for the data over APIs or keep a small copy updated by events.”

5-minute intermediate explanation

Start with the shared-database pain story (Section 2). Show the four levels of isolation and stress that the rule is about access, not hardware. Then cover the three ways to get data you don’t own: (1) call the API on demand, (2) keep a local read-only snapshot updated by events, (3) build a read model or use API composition for screens. Explain that consistency becomes eventual, so multi-step business operations use sagas, and events must be published reliably with the outbox and consumed idempotently. Finish with the ADR trade-offs: more operations and cost in exchange for autonomy.

Hands-on exercise

Task: Build two ASP.NET Core services (Policy on PostgreSQL, Claims on SQL Server or a second PostgreSQL database) using Docker Compose. Claims must validate a policy on claim submission by calling the Policy API, then extend it to keep a local PolicySnapshot updated from a PolicyUpdated message (RabbitMQ or Azure Service Bus emulator for local work).

Expected outcome:

• Two containers, two separate databases with separate credentials; the Claims login cannot read Policy tables (verify with a failing SELECT).
• Submitting a claim works with the Policy service running, and with it stopped the claim still works using the snapshot.
• Renaming a column in the Policy database (with a migration) requires no change or redeploy in Claims.
• Sending the same PolicyUpdated event twice leaves the snapshot unchanged the second time.

Interview-style questions

1. Why can’t Claims just join to the Policy tables if both are in the same SQL Server? Because that makes the schema a hidden public API, blocks independent change, and couples release and performance. Same server is fine; shared access is not.
2. How do you keep data consistent across services without distributed transactions? Use sagas with compensating actions, reliably published events (transactional outbox), and idempotent consumers, and accept eventual consistency.
3. Does “database per service” mean one physical server per service? No. It means private ownership and access. Services can share a server or an elastic pool as long as access is isolated and enforced.

Mastery checklist

• I can explain why a shared schema is an implicit API and give a concrete example of the damage it does.
• I can name the four isolation levels (private tables, schema, database, server) and pick one with a reason.
• I can design how a service gets data it does not own using an API call, a local snapshot, or a composed read, and say when to use each.
• I can enforce ownership technically (separate logins or managed identities, denied permissions), not just by convention.
• I can describe how consistency is handled: saga, outbox, and idempotent consumers.
• I can set up the pattern on Azure (elastic pool or per-service databases, private endpoints, managed identity, Key Vault).
• I can recognise when the pattern is overkill and a modular monolith is the better choice.
• I can write an ADR that states the trade-offs honestly, including added cost and operations effort.

Key takeaway

A service’s database is part of its private implementation, not its interface: expose data through APIs and events, and you keep the freedom to change, deploy, and scale each service on its own, at the price of eventual consistency and more moving parts.