[PERF] Azure SQL: avoid 2.29 GB first-provision cold start

[thomhurst]

Summary

Floci-AZ itself starts in milliseconds, but the first Azure SQL logical-server provisioning request blocks while Floci-AZ downloads and starts a full SQL engine image.

In a clean environment, this pulled mcr.microsoft.com/azure-sql-edge:latest, reported by Docker as a 2.29 GB image, and kept the ARM PUT request open for approximately 3 minutes 12 seconds. Clients with ordinary HTTP timeouts report failure even though the server eventually becomes Ready.

This issue proposes separating fast Azure SQL control-plane emulation from the optional heavyweight SQL data plane. It does not propose reimplementing SQL Server or T-SQL.

Environment

• Floci-AZ: 0.9.0
• Container image: floci/floci-az
• SQL engine image selected by Floci-AZ: mcr.microsoft.com/azure-sql-edge:latest
• Docker-reported SQL engine image size: 2.29 GB
• SQL EULA enabled with FLOCI_AZ_SERVICES_SQL_ACCEPT_EULA=Y
• Docker socket mounted so Floci-AZ could provision the engine container

Reproduction

Start Floci-AZ with Azure SQL enabled:

docker run --rm \
  --name floci-az \
  -p 4577:4577 \
  -e FLOCI_AZ_SERVICES_SQL_ACCEPT_EULA=Y \
  -v /var/run/docker.sock:/var/run/docker.sock \
  floci/floci-az

From a clean Docker image cache, create a logical SQL server through the ARM contract:

curl --request PUT \
  'http://localhost:4577/subscriptions/00000000-0000-0000-0000-000000000000/resourceGroups/floci-local/providers/Microsoft.Sql/servers/repro-sql?api-version=2021-11-01' \
  --header 'content-type: application/json' \
  --data '{
    "location": "uksouth",
    "properties": {
      "administratorLogin": "sqladmin",
      "administratorLoginPassword": "StrongPassw0rd!"
    }
  }'

Observed timeline

The following timestamps came from one first-provision run:

Time	Event
20:17:04	ARM PUT received
20:17:04	Pull started for mcr.microsoft.com/azure-sql-edge:latest
20:20:06	SQL Server TCP port opened; engine initialization still running
20:20:16	SQL Server reported ready

Total elapsed time: approximately 3 minutes 12 seconds.

Relevant log sequence:

Starting SQL Server container: server=repro-sql image=mcr.microsoft.com/azure-sql-edge:latest
Pulling image: mcr.microsoft.com/azure-sql-edge:latest
SQL Server TCP ...:1433 is open — waiting for engine init…
SQL Server ready: server=repro-sql endpoint=...:1433

Floci-AZ process startup remained fast; the delay was entirely within the first Azure SQL resource provisioning request.

Impact

• A client with a 10-second timeout reports the create operation as failed.
• Provisioning continues after the client disconnects, so a later refresh shows the resource as Ready.
• Users cannot distinguish a genuine failure from background work that will eventually succeed.
• Retrying can create confusing concurrent or duplicate provisioning attempts.
• CI and local-development workflows pay a multi-gigabyte download and multi-minute startup cost even when they only need ARM lifecycle behavior.
• The behavior makes Azure SQL feel inconsistent with the millisecond-scale feedback expected from Floci-AZ.

Increasing every client timeout is only a partial workaround. It still leaves an HTTP request open during an unbounded image pull and engine startup.

Expected behavior

Control-plane-only workflows should remain fast. Creating, listing, inspecting, and deleting a logical Azure SQL server should not require a real SQL engine unless the caller explicitly needs data-plane connectivity.

When a real engine is requested, provisioning should be asynchronous and observable instead of holding the ARM request open for several minutes.

Concrete findings

Engine image

The Docker Hub page for Microsoft SQL Server is a catalogue/documentation page. The actual supported image is pulled from Microsoft Container Registry:

mcr.microsoft.com/mssql/server:2025-latest

Release builds should pin a tested CU tag and digest rather than use mutable latest.

The existing default, mcr.microsoft.com/azure-sql-edge:latest, is no longer suitable. Azure SQL Edge retired on September 30, 2025 and no longer receives security updates or fixes:

• Azure SQL Edge lifecycle
• Official SQL Server container image
• Current MCR tags

Changing images does not remove the fundamental cost. The current SQL Server 2025 manifest transfers approximately 0.59 GB compressed but expands to roughly 2 GB locally. Microsoft documents a minimum 2 GB RAM requirement and supports the Linux image only on Intel/AMD x86-64 hosts, not ARM translation:

• SQL Server Linux container requirements

A lightweight, cross-platform, fully compatible SQL Server substitute is therefore not available. The clean solution is architectural separation, not a different fake SQL engine.

Current implementation findings

• A control-plane-only mocked switch already exists, but defaults to false; ordinary ARM workflows therefore still select the heavyweight path by default.
• Engine pull, container creation, and readiness waiting execute synchronously inside logical-server PUT.
• Readiness currently means “TCP opened, then sleep for up to ten seconds,” not “SQL authentication and query succeeded.”
• Database PUT records metadata only. It does not execute CREATE DATABASE; compatibility tests currently perform that DDL themselves.
• Runtime endpoint details such as localhost and localPort leak into ARM resource responses.

Recommended design

1. Replace mocked with an explicit data-plane provider

floci-az:
  services:
    sql:
      enabled: true
      data-plane:
        provider: none       # none | managed | external

Providers:

• none — default. Azure-compatible ARM state only; no Docker socket, image, EULA, TDS endpoint, or startup cost.
• managed — Floci-AZ manages real SQL Server containers.
• external — Floci-AZ uses an existing SQL Server endpoint supplied by the developer, Docker Compose, Testcontainers, or CI.

When provider=none, logical servers and databases should become ready immediately. Existing emulator-specific connection discovery should return a clear DataPlaneNotEnabled error rather than an unusable port 0.

2. Use asynchronous desired-state reconciliation

For managed and an unavailable external engine:

1. Validate request.
2. Persist desired server state as Creating.
3. Return promptly using the real Azure SQL long-running-operation contract.
4. Reconcile engine state in a bounded background worker.
5. Transition resource and operation to Ready/Succeeded or Failed.

Azure SQL server create supports 202 Accepted with a Location header:

• Servers - Create or Update
• ARM asynchronous operation semantics

Required concurrency behavior:

• Repeated equivalent PUT while creating returns the existing operation.
• Conflicting updates are serialized or return Azure-compatible 409 ConflictingServerOperation.
• Failure remains observable as Failed; resource state must not be silently deleted.
• A later PUT can retry failed provisioning.
• Delete during provisioning cancels or supersedes pending work and eventually cleans up its lease/container.

3. Optimize managed mode without weakening default fidelity

floci-az:
  services:
    sql:
      data-plane:
        provider: managed
        startup: eager-async
      managed:
        image: "mcr.microsoft.com/mssql/server:<tested-CU>@sha256:<digest>"
        pid: Developer
        warm-pool-size: 1

• Begin pull/start asynchronously when Floci-AZ starts in managed mode, rather than waiting for first ARM request.
• Maintain one warm dedicated engine lease for the common one-logical-server development case.
• Use a dedicated engine per logical server by default. This preserves server-level login, database-name, and isolation semantics.
• A shared-engine topology may be added as an explicit speed/fidelity trade-off, but should not be the default: duplicate login and database names across logical servers cannot be represented faithfully in one SQL Server instance.
• Keep external first-class. It gives ARM developers and CI zero Floci-managed pull/start cost and is the supported path on ARM hosts.

Managed sidecar failures remain non-fatal to Floci-AZ. Control-plane requests must continue working and expose engine failure through SQL resource/operation state.

4. Make real data-plane mode real

Use Microsoft JDBC driver internally:

• Readiness: authenticate and execute SELECT 1 until deadline.
• Database PUT: execute idempotent CREATE DATABASE.
• Database DELETE: execute DROP DATABASE.
• Server deletion/reset: remove created databases/logins and release/remove the engine.
• EULA validation applies only to managed.

ARM responses should remain Azure-shaped, including {server}.database.windows.net as fullyQualifiedDomainName, with no localPort extension. Emulator-specific reachable endpoint information stays outside the ARM resource representation.

5. Test and local-development workflow

• Run unit, ARM, SDK-management, Terraform, OpenTofu, and Azure CLI tests with provider=none.
• Run SQL/TDS compatibility in a separate x86-64 job with one engine reused for the whole suite.
• Reset databases/schema between tests; do not restart SQL Server per test.
• Start image pull, Floci-AZ build, and compatibility-test dependency resolution concurrently.
• Provide a Docker Compose SQL profile and documented prewarm command:

docker pull mcr.microsoft.com/mssql/server:<tested-tag>
docker compose --profile sql up

Do not use H2, SQLite, PostgreSQL compatibility modes, Babelfish, or a custom partial TDS/T-SQL implementation as the Azure SQL data plane. Those approaches are faster but create misleading compatibility and false-positive tests.

Acceptance criteria

• none | managed | external data-plane providers replace the ambiguous mocked boolean.
• none is the default and performs no SQL image pull, container start, Docker access, or EULA check.
• Control-plane server/database CRUD returns promptly and remains available through list/get APIs.
• Managed mode uses a supported, configurable, release-pinned mcr.microsoft.com/mssql/server image rather than retired Azure SQL Edge.
• Managed engine pull/start happens outside the ARM request thread.
• Engine-backed provisioning uses Azure-compatible long-running-operation polling.
• Clients can observe Creating, Ready, and Failed resource outcomes plus InProgress, Succeeded, and Failed operation outcomes.
• Equivalent retries are idempotent; conflicting concurrent operations behave deterministically.
• SQL readiness requires successful authentication and SELECT 1, not TCP-open plus sleep.
• Database create/delete operations reconcile to real CREATE DATABASE/DROP DATABASE in data-plane modes.
• Deleting/resetting a server cleans its databases, logins, engine lease, and dedicated container where applicable.
• ARM response shapes contain no emulator-only localPort field.
• Connection discovery reports a clear error when the data plane is disabled.
• Documentation covers EULA, supported x86-64 hosts, minimum memory, image size, cold start, pre-pulling, Compose, and external-engine configuration.

Non-goals

• Reimplementing SQL Server or T-SQL inside Floci-AZ.
• Removing the existing real-engine path for users who require data-plane fidelity.

Related context

• Add Azure SQL Database support #20 introduced the initial Azure SQL Database support direction using SQL Server containers internally.