Day 6: Shared Database (Interim)

The Shared Database pattern lets several services temporarily read and write the same physical database while their business logic already lives in separate service codebases.

Intro

The Shared Database pattern lets several services temporarily read and write the same physical database while their business logic already lives in separate service codebases. It is not the target architecture; it is a deliberate, time-boxed stepping stone during a monolith-to-microservices migration. It buys you speed and keeps ACID transactions intact, at the price of coupling at the schema level, which you must actively manage and eventually remove (see Day 5: Database per Service).

Why we need this

Splitting a monolith’s code is usually far easier than splitting its data. In our insurance claims system, the monolith ClaimsApp owns one SQL Server database InsuranceDb with 400+ tables, hundreds of stored procedures, reporting views, and nightly jobs. If the business wants a new Claims service and a new Policy service in the next quarter, migrating all data, rewriting every join, and building sagas for every cross-table transaction first would stall delivery for a year.

Business reasons:

• Deliver visible service boundaries quickly and show migration progress to stakeholders.
• Avoid a risky big-bang data migration while regulators, auditors and finance reports still read the same tables.
• Keep existing ACID transactions (for example “create claim + reserve funds”) working while teams learn distributed patterns.

Technical reasons:

• Data ownership boundaries are often unknown at the start. You discover the real seams by watching which service touches which tables.
• Moving to a database per service needs data synchronisation, CDC or events, and consistency handling (Saga, Outbox). Those need time, skills and tooling that the team may not have yet.

What problem it solves

Problem (from the topic list): decoupling data immediately is cost-prohibitive during migration.

Without an interim option, a team has only two bad choices: (a) do not split the monolith at all, or (b) split code and data together in one huge step. Option (b) requires rewriting every cross-domain query as API calls, replacing every multi-table transaction with a saga, and migrating live data with no downtime, all before any service ships. Projects in this situation commonly stall or get abandoned.

Shared Database resolves this by separating the two migrations: first split the code (services with their own repos, pipelines, deployments), then split the data later, one table group at a time, once ownership is proven.

What goes wrong if you treat the shared DB as permanent: any schema change breaks unknown consumers, teams block each other’s releases, one service’s slow query starves another, and you end up with a distributed monolith (all the operational cost of microservices with none of the independence).

When it is needed (and when it is NOT)

Good fit:

• Mid-migration from a monolith where the schema is large and heavily entangled (foreign keys, shared views, stored procedures).
• Services still need a single ACID transaction across tables that will later belong to different services.
• A small number of services (2 to 4) and one or two teams that talk daily.
• A read-only reporting or legacy integration that must keep working during the transition.
• There is a written plan and date to retire the shared access.

Wrong choice:

• Greenfield systems: start with a database per service (Day 5) or a modular monolith with schemas per module.
• Many teams with independent release cadences: schema coordination becomes the bottleneck.
• Services with very different load profiles or SLAs (for example a claims-intake API vs. a nightly actuarial batch): noisy-neighbour risk.
• Regulated data that must be isolated (for example medical records vs. payments) where access separation is legally required.
• When “interim” has no exit date. That is how permanent shared databases are born.

How to identify the problem (key signals)

Signals that you are in a shared-database situation that needs managing (or leaving):

1. A column rename or type change needs a “who uses this?” email chain, and someone always breaks in production.
2. Two services deploy together on the same day “because of the migration script”.
3. Blocking and deadlocks between unrelated services show up in sys.dm_tran_locks or Azure SQL Query Store, involving the same hot tables (for example Claims, Payments).
4. One connection string with db_owner rights is copied across several repos and pipelines.
5. Service A contains SQL that reads tables “belonging” to Service B, or joins across them (code smell: JOIN Policy.Policies inside the Claims service).
6. DB CPU or DTU spikes correlate with one service’s batch job while the other service’s p95 latency degrades.
7. Integration tests need the entire database restored to run a single service’s tests.

Flow Diagram

A temporary shared database with schema-level ownership, and the exit path to separate databases.

flowchart TB
    subgraph Interim["Interim state"]
      CL["Claims service"] -- "read/write" --> SC["schema: claims"]
      PO["Policy service"] -- "read/write" --> SP["schema: policy"]
      CL -- "read-only" --> V["view: policy.vw_PolicySnapshot"]
      V --> SP
    end
    Interim --> E1["1. Declare table owners"]
    E1 --> E2["2. Replace foreign reads with APIs/events"]
    E2 --> E3["3. Move table group to owner's DB"]
    E3 --> E4["4. Revoke grants, drop shared views"]
    E4 --> T["Target: Database per Service"]

Level 1: Beginner

Analogy: several restaurants (services) rent one shared kitchen (database). It is cheap and quick to open, but if one restaurant rearranges the shelves, all others cannot find their ingredients, and one cook hogging the oven slows everybody down. The interim goal is: agree on which shelf belongs to whom, then eventually build separate kitchens.

Minimal example: two services in the same SQL Server database, each with its own schema and its own login. Rule: a service writes only to tables in its schema.

-- One database, ownership expressed as schemas + separate logins
CREATE SCHEMA claims;
CREATE SCHEMA policy;
GO

CREATE TABLE policy.Policies (
    PolicyId     INT IDENTITY PRIMARY KEY,
    PolicyNumber NVARCHAR(30) NOT NULL UNIQUE,
    Status       NVARCHAR(20) NOT NULL
);

CREATE TABLE claims.Claims (
    ClaimId      INT IDENTITY PRIMARY KEY,
    PolicyId     INT NOT NULL,      -- logical reference only (no cross-schema FK; see Level 3)
    Amount       DECIMAL(18,2) NOT NULL,
    Status       NVARCHAR(20) NOT NULL
);
GO

CREATE USER claims_svc FOR LOGIN claims_svc;
CREATE USER policy_svc FOR LOGIN policy_svc;

-- Each service owns its schema...
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::claims TO claims_svc;
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::policy TO policy_svc;
-- ...and gets at most read-only access to the other during migration
GRANT SELECT ON SCHEMA::policy TO claims_svc;

The key idea: even though the database is shared, ownership is explicit. Only the owner writes.

Level 2: Intermediate

In a .NET (current LTS, .NET 10) + Angular + SQL Server application, each service keeps its own DbContext mapped only to its schema. Where the service must read foreign data, it uses a narrow read-only view or a read-only context, never the other service’s entities.

Claims service DbContext (owns claims, reads policy through a view):

using Microsoft.EntityFrameworkCore;

public class ClaimsDbContext(DbContextOptions<ClaimsDbContext> options) : DbContext(options)
{
    public DbSet<Claim> Claims => Set<Claim>();
    public DbSet<PolicySnapshot> PolicySnapshots => Set<PolicySnapshot>(); // read-only

    protected override void OnModelCreating(ModelBuilder b)
    {
        b.HasDefaultSchema("claims");
        b.Entity<Claim>().ToTable("Claims");

        // Read-only projection over a view exposed by the Policy owner
        b.Entity<PolicySnapshot>(e =>
        {
            e.HasNoKey();
            e.ToView("vw_PolicySnapshot", "policy");
        });
    }
}

public class Claim
{
    public int ClaimId { get; set; }
    public int PolicyId { get; set; }
    public decimal Amount { get; set; }
    public string Status { get; set; } = "Submitted";
}

public class PolicySnapshot
{
    public int PolicyId { get; set; }
    public string PolicyNumber { get; set; } = "";
    public string Status { get; set; } = "";
}

The view is the contract, owned and versioned by the Policy team:

CREATE VIEW policy.vw_PolicySnapshot AS
SELECT PolicyId, PolicyNumber, Status FROM policy.Policies;

Service endpoint (minimal API) that validates the policy through the view, then writes only into its own schema:

app.MapPost("/claims", async (SubmitClaim cmd, ClaimsDbContext db) =>
{
    var policy = await db.PolicySnapshots
        .Where(p => p.PolicyId == cmd.PolicyId && p.Status == "Active")
        .FirstOrDefaultAsync();
    if (policy is null) return Results.BadRequest("Policy not active.");

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

public record SubmitClaim(int PolicyId, decimal Amount);

Angular side: the frontend is unaware of the shared database. It calls each service’s API (through a gateway later, Day 19) using a typed service:

@Injectable({ providedIn: 'root' })
export class ClaimsApi {
  private http = inject(HttpClient);
  submit(policyId: number, amount: number) {
    return this.http.post<{ claimId: number }>('/api/claims', { policyId, amount });
  }
}

Migration hygiene at this level: one connection string per service (own login, least privilege), schema changes as versioned migrations owned by the owning service, and a written table-ownership register.

Level 3: Advanced

Performance and scalability:

• All services share one database’s CPU, memory, tempdb, log throughput and connection pool limits. One service’s runaway query hurts all. Mitigate with Query Store, Resource Governor (SQL Server / Managed Instance), or by scaling up the single database and moving noisy read workloads to a read replica (elastic pools do not help, because there is only one database).
• Use read replicas (Azure SQL Business Critical / Hyperscale read scale-out) for read-heavy consumers such as reporting, with ApplicationIntent=ReadOnly.
• Watch connection counts: N services x M instances x pool size can exhaust the max workers/sessions of the tier.

Security:

• One login per service, least privilege, no db_owner. Deny writes to tables you do not own. Use Microsoft Entra managed identities on Azure SQL instead of passwords.
• Consider row-level security or views to limit what other services can read (for example hide personal data from the reporting service).

Failure modes:

• Cross-schema foreign keys and cross-service transactions lock you in. Every FK from claims.Claims.PolicyId to policy.Policies blocks later separation. Prefer logical references (validated in code or by view) so tables can be split without changing constraints.
• Long transactions spanning two services’ tables cause blocking and deadlocks. Set consistent access order and short transactions; use READ_COMMITTED_SNAPSHOT ON to reduce reader/writer blocking.
• Schema migration races: two pipelines applying migrations to the same database. Give each service its own migration history table (in EF Core: HistoryTable("__EFMigrationsHistory", "claims")) and apply migrations from one controlled step.
• Backward-incompatible change to a view or table read by another service. Use expand/contract: add the new column, deploy consumers, then remove the old one.

Common mistakes:

• Calling it “interim” and never scheduling the exit.
• Letting services join each other’s tables directly (the tight coupling you are trying to escape).
• Sharing one connection string and DbContext library across services.
• Sharing stored procedures that contain business logic used by several services, so the logic is not really in the service.
• Using the shared database as an integration bus (polling each other’s tables for status changes) instead of moving to events (Day 11, Day 12).

Exit path (the part the pattern is actually for): (1) declare the owner per table, (2) replace foreign reads with API calls or replicated read models, (3) move each table group into the owner’s new database using backup/restore, replication or CDC, (4) revoke the old grants, (5) delete the shared views.

Level 4: Expert and Architect view

Trade-off comparison:

Combines with: Strangler Fig (Day 49) for progressive extraction; Database per Service (Day 5) as the destination; API Composition (Day 10) to replace cross-service joins; Domain Event (Day 11) and Transactional Outbox (Day 12) to replace table polling; Saga (Day 7) to replace cross-schema transactions; CQRS (Day 8) for read models.

ADR-style justification (for an architecture review):

• Title: ADR-006 Temporary shared SQL database for Claims and Policy services.
• Status: Accepted, expires 2027-03-31.
• Context: The claims monolith has one 400-table database with heavy cross-domain foreign keys, views and stored procedures. Two teams must ship the Claims and Policy services this quarter. Data ownership is not yet proven and the team has no saga or CDC experience.
• Decision: Both services use the existing database with schema-per-service, separate least-privilege logins, no cross-schema writes, and read-only access through owner-versioned views. No new cross-schema foreign keys.
• Consequences: Faster delivery and retained ACID transactions. Accepted risks: shared capacity, schema-level coupling and coordinated releases for view changes. Mitigations: Query Store alerts, expand/contract migrations, ownership register.
• Exit criteria: Each table group moved to its owner’s database, cross-service reads replaced by APIs or events, shared logins revoked. Review at each quarter; if not started by the expiry date, escalate.

Azure implementation

Services that implement or support this:

• Azure SQL Database (single database) as the shared database; Azure SQL Managed Instance if the monolith depends on SQL Agent, cross-database queries or other instance-level features.
• Azure App Service or Azure Container Apps / AKS to host the separate services.
• Microsoft Entra ID managed identities for passwordless per-service database access.
• Azure Monitor / Application Insights, Query Store and SQL Insights / Database Watcher for spotting cross-service contention.
• Azure DevOps or GitHub Actions for per-service pipelines; migrations run from a single controlled step.
• Later for the exit: Azure Data Factory, transactional replication (Managed Instance), or Azure Service Bus plus the Outbox pattern.

How to configure:

1. Create the Azure SQL logical server with Entra-only authentication. Add each service’s managed identity as a contained user: CREATE USER [claims-svc-mi] FROM EXTERNAL PROVIDER;.
2. Grant per-schema permissions exactly as in Level 1 (GRANT ... ON SCHEMA::claims). Grant only SELECT on views for foreign data.
3. Connection string in each service, with the managed identity (Microsoft.Data.SqlClient Authentication=Active Directory Default), stored in App Configuration or environment variables, not shared.
4. Enable Query Store (on by default in Azure SQL Database) and READ_COMMITTED_SNAPSHOT (on by default in Azure SQL Database). Add Azure Monitor alerts on CPU, deadlocks, and blocked processes.
5. Optionally tag queries with Application Name per service in the connection string so Query Store and DMVs show which service caused what.

Pricing and tier considerations (verify current numbers on the Azure SQL Database pricing page before quoting, since prices vary by region and change):

• vCore model is the recommended purchasing model: General Purpose (remote storage, cost-effective, most migrations start here), Business Critical (local SSD, low latency, built-in readable secondary), Hyperscale (large databases, fast scale and read replicas). Serverless compute is available in General Purpose and Hyperscale but auto-pause is a poor fit for a busy shared database.
• Because all services hit one database, size it for the sum of the workloads plus headroom. This is the pattern’s hidden cost: you cannot scale services independently.
• Budget for the exit: the second, third, and later databases add cost. Elastic pools can share compute across the per-service databases once you split.
• Reserved capacity / Azure Hybrid Benefit can reduce cost for steady-state usage.

Reference architecture (text): Angular SPA served from Azure Static Web Apps calls an Azure API Management gateway. APIM routes to two Container Apps: Claims service and Policy service, each with its own managed identity and CI/CD pipeline. Both connect to one Azure SQL Database over a private endpoint in a VNet. Inside the database there are two schemas, claims and policy, with separate contained users and a read-only view policy.vw_PolicySnapshot. Application Insights and Query Store feed one Azure Monitor workbook that shows per-service load. A migration roadmap tracks moving each schema into its own Azure SQL Database later.

Teaching guide for my team

Explain to a beginner in 2 minutes: “Our old app has one big database. We are splitting the app into services, but we cannot split the database on day one. So for a while, services share the database, but each one owns its own schema and only writes there. Think of shared kitchen, marked shelves. It is a bridge, not a home: we cross it, and then we build separate databases.”

Explain to an intermediate developer in 5 minutes: Cover: (1) why data is harder to split than code; (2) schema-per-service, separate logins, least privilege; (3) reads across boundaries only via owner-versioned views; (4) no cross-schema FKs, no cross-service joins in code; (5) expand/contract migrations and per-service migration history; (6) shared capacity risk and how Query Store shows it; (7) the exit plan: APIs and events replace direct reads, then move data. Draw the before/after on a whiteboard.

Hands-on exercise: Using a local SQL Server (or Azure SQL) database, create schemas claims and policy, two logins/users, and the view from Level 2. Build a small .NET minimal API for claims with the ClaimsDbContext. Then (a) prove claims_svc can insert into claims.Claims but gets a permission error on INSERT INTO policy.Policies; (b) rename a column in policy.Policies and update only the view so the Claims service keeps working without redeploying. Expected outcome: permission error for (a); Claims service works unchanged for (b), showing the view acts as a stable contract.

Interview-style questions:

1. Why would you use a shared database in a microservices migration when it is called an anti-pattern? As a deliberate, time-boxed step: it delivers code separation quickly and keeps ACID transactions while ownership and sync mechanisms are prepared. It is only acceptable with clear ownership rules and an exit plan.
2. How do you reduce coupling while still sharing the database? Schema per service, separate least-privilege logins, writes only to owned tables, reads across boundaries through owner-versioned views, no cross-schema foreign keys, and expand/contract schema changes.
3. How do you leave the shared database? Per table group: confirm one owner, replace foreign reads with APIs, events or replicated read models, copy data to the owner’s database (backup/restore, replication or CDC), switch the connection, revoke old grants, and remove the shared views.

Mastery checklist

• I can explain why splitting code first and data second lowers migration risk.
• I can design schema-per-service ownership with separate logins and least-privilege grants.
• I can implement read-only cross-service access through a view and a keyless EF Core entity.
• I can list at least five signals that a shared database has become a problem.
• I can perform a backward-compatible (expand/contract) schema change without breaking another service.
• I can diagnose cross-service blocking or resource contention using Query Store and wait statistics.
• I can write an ADR with explicit exit criteria and an expiry date.
• I can plan the extraction of one table group into its own database.

Key takeaway

A shared database is a scaffold, not a foundation: use it with strict ownership rules to move fast now, and schedule its removal from day one.