Skip to main content

ADR-018: Local SQL Server Container for Development

Status​

Accepted (supersedes the "no local SQL Server" position from ADR-016 section)

Context​

The original infrastructure plan (documented in Arc42 section 7.3 and ADR-016) stated "no local SQL Server" -- developers should connect directly to Azure SQL MI. This made sense during the documentation-only phase when the only database interaction was read-only schema extraction.

Now that the .NET solution is scaffolded and BestEx implementation is imminent, the calculus has changed:

  • The production MI database (PS608 / MWFI Holdings) is read-only -- we can never write to it. It's a client's live data, used strictly as a reference for schema and business rule validation.
  • Unit and integration tests need a writable database with repeatable, synthetic test data.
  • dotnet watch development loops benefit from sub-millisecond database latency, not 50-100ms round-trips to Azure.
  • Offline development is impossible without a local database.
  • Azure SQL MI charges for compute and storage regardless of whether queries come from local dev or production workloads. Unnecessary dev traffic adds cost.

Decision​

Add a SQL Server 2022 Linux container (mcr.microsoft.com/mssql/server:2022-latest) to the Docker Compose dev profile as pssaas-db.

The local database:

  • Uses the same pscat_/rmcat_/pxcat_ schema as the MI database
  • Is seeded with a schema-only DDL script (no customer data)
  • Is populated with synthetic test data for development and testing
  • Is the default database for the dev profile (appsettings.Development.json)
  • Is ephemeral -- data is lost when the container is removed (no persistent volume initially)

Azure SQL MI remains the reference for:

  • Real schema validation (ground truth)
  • Production deployment
  • Future tenant databases

Consequences​

Positive​

  • Fast development loop -- local database queries in sub-millisecond, no network latency
  • Safe writes -- tests can INSERT/UPDATE/DELETE without touching any real data
  • Offline capable -- development works without internet connectivity
  • Reduced Azure cost -- no dev query traffic hitting the MI
  • Test isolation -- repeatable synthetic data, not live data that changes daily

Negative​

  • Schema sync responsibility -- the local DDL script must be kept in sync with the MI schema. EF Core migrations will eventually replace the seed script.
  • One more container -- adds ~500MB memory footprint when running

Neutral​

  • Migration from local container to MI is a connection string change. SQL Server 2022 and Azure SQL MI are engine-compatible -- T-SQL, EF Core behavior, and schema DDL are identical.