SuperAPI AI Directory
Partner with us中文AI newsFavorites
Skillsazuresql-db-connections

azuresql-db-connections

Data & analytics

View repository

Updated 10-16 00:31

About this skill

Reliable connections on the Azure SQL Database container (pooling + retry)

Make the app's database connections reliable with connection pooling and retry / transient-fault handling. This is the Azure SQL engine (Private Preview), not the SQL Server image.

Verified on 2026-09-05 against the container image sqldbpreview-dpgaeqhmgphzd4bk.azurecr.io/azure-sql/db-dev:latest, reporting EngineEdition 5, Edition SQL Azure, build 12.0.2000.8. All five executable checks behind this skill passed: the engine identity, Msg 40508 for USE, a TCP session on the mapped port, and the two resource-governance dynamic management views returning nothing locally. Of the ten transient error numbers in the retry list below, nine are in this build's sys.messages and Msg 10929 is not. It stays in the list because it is a cloud resource-governance error the container has no reason to raise, and it is sourced from Microsoft Learn rather than from that run.

Why do this locally (local-to-cloud parity)

The local container rarely drops a connection, so it is tempting to skip pooling and retry. Do not. Azure SQL Database in the cloud throttles and drops connections during failovers, scaling, and load; a client with no retry surfaces those as hard errors. Build pooling and retry now, against the local container, and the same code survives in the cloud with no rewrite. For the full promote-to-cloud story see the azuresql-db-local-to-cloud skill.

Verify identity once running: SELECT SERVERPROPERTY('EngineEdition') returns 5 and SERVERPROPERTY('Edition') returns 'SQL Azure'. For full engine detail see the azuresql-db-container skill.

The container does not carry the throttling telemetry the cloud does, so do not write a local diagnostic against it. sys.dm_db_resource_stats and sys.dm_user_db_resource_governance are absent from this engine: OBJECT_ID returns NULL for both, and a query against either does not run at all here. Their absence is not evidence that the cloud does not throttle. It is the reason the retry policy below has to be built without being able to provoke the fault locally.

The engine and the connection contract

  • Image: sqldbpreview-dpgaeqhmgphzd4bk.azurecr.io/azure-sql/db-dev:latest (x64 / linux/amd64, Private Preview registry). Sign in first: docker login sqldbpreview-dpgaeqhmgphzd4bk.azurecr.io with the shared pull-only credentials from https://aka.ms/sqldbcontainerpreview-signup (they may rotate). On a non-x64 host add --platform linux/amd64 (Docker) or platform: linux/amd64 (compose).
  • Do NOT use mcr.microsoft.com/mssql/server (the SQL Server image).
  • Required env: ACCEPT_EULA=Y and a complex, caller-supplied MSSQL_SA_PASSWORD. The engine listens on 1433.
  • The engine does NOT auto-create databases. CREATE DATABASE appdb on a master connection first. Do not USE to switch databases: a user-database session returns Msg 40508. Select the database in the connection string (Database=appdb).
  • Apps read one env var, SQL_CONNECTION_STRING. Strings use User Id= / Password= / Database= and TrustServerCertificate=true. sqlcmd uses -C.

Start the container and provision appdb

: "${MSSQL_SA_PASSWORD:?Set MSSQL_SA_PASSWORD to a unique local administrator password}"
HOST_PORT=1433; while lsof -nP -iTCP:"$HOST_PORT" -sTCP:LISTEN >/dev/null 2>&1; do HOST_PORT=$((HOST_PORT+1)); done
PLATFORM=(); case "$(docker info -f '{{.Architecture}}' 2>/dev/null)" in x86_64|amd64) ;; *) PLATFORM=(--platform linux/amd64);; esac
docker rm -f sqldb 2>/dev/null
docker run -d --name sqldb "${PLATFORM[@]}" -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD" \
  -p "127.0.0.1:$HOST_PORT:1433" sqldbpreview-dpgaeqhmgphzd4bk.azurecr.io/azure-sql/db-dev:latest
until docker exec sqldb sh -c \
  'SQLCMDPASSWORD="$MSSQL_SA_PASSWORD" exec /opt/mssql-tools18/bin/sqlcmd -S localhost -U sa -C -b -l 2 -Q "$1"' sh \
  "IF DB_ID('appdb') IS NULL CREATE DATABASE appdb;" \
  >/dev/null 2>&1; do sleep 2; done
echo "ready on localhost,$HOST_PORT"

Use this non-executable connection-string template with the selected HOST_PORT:

Server=localhost,<HOST_PORT>;Database=appdb;User Id=sa;Password=<MSSQL_SA_PASSWORD>;TrustServerCertificate=true

Pooling: reuse connections, do not reopen per query

  • Keep pooling on (it is on by default in most drivers) and let one pool serve the app.
  • Set a bounded Max Pool Size (default 100 in .NET) so a spike cannot open unlimited connections. Size it to real concurrency, not a guess.
  • A small Min Pool Size keeps a few connections warm and cuts cold-start latency.
  • One connection string means one pool. Do not build strings dynamically per request (each distinct string is a separate pool) and do not open a fresh, unpooled connection per call.
  • Always close/dispose connections (or use using / with / context managers) so they return to the pool instead of leaking.

Retry: only for transient faults, with backoff

Retry only a transient fault: throttling, a brief failover, a dropped idle connection. In Azure SQL these arrive as specific error numbers: 40501 throttling, 40613 database unavailable, 49918/49919/49920 busy, 4060, 10928, 10929, 40197, 233, plus connection-timeout and broken-pipe socket errors.

  • Retry only transient errors. Retrying a non-transient error (login failure 18456, syntax error, constraint violation, permission denied) just fails slower and hides the real bug.
  • Use exponential backoff with a cap and a small jitter, and a bounded attempt count (for example 5 attempts). Do not hammer a throttled server.
  • Non-idempotent writes: a retry can double-apply an INSERT that committed before the connection dropped. Use client-generated keys, MERGE, or a transaction the retry re-runs as a whole. EF Core's execution strategy handles this when the work goes through its Execute/transaction API.
  • Prefer a framework retry policy over hand-rolled loops where one exists (EF Core EnableRetryOnFailure for .NET). Hand-roll only for raw drivers.

Per-stack

Open references/retry-snippets.md when you are wiring one of these stacks; it carries copy-pasteable pooling config and transient-only retry for each:

  • .NET (Microsoft.Data.SqlClient): pooling keywords (Max Pool Size, Min Pool Size, Pooling=true) and connection-string retry keywords (ConnectRetryCount, ConnectRetryInterval); plus EF Core EnableRetryOnFailure (the SqlServer execution strategy).
  • Node (mssql / tedious): pool config (max / min / idleTimeoutMillis) and a transient-error retry wrapper.
  • Python (pyodbc): connection reuse and a tenacity retry decorator that retries only transient ODBC errors.

Keep the single SQL_CONNECTION_STRING contract: pooling and retry are tuned in code and in driver-specific keywords, not by inventing new env vars.

Validation rules

  • Retry fires only on transient errors; non-transient errors (auth, syntax, constraint) surface immediately.
  • Retry uses bounded attempts with exponential backoff, and non-idempotent writes are made safe to re-run (keys, MERGE, or a retriable transaction).
  • Pooling is on with a bounded Max Pool Size; connections are disposed and returned to the pool, never opened per query.
  • One connection string / one pool; the app still reads a single SQL_CONNECTION_STRING.
  • Runs against the engine image with EngineEdition 5; appdb was created on a master connection before the app connected.
  • If a validation rule above fails, or you had to deviate from this skill to make the task work, that is a bug in this skill: load the azuresql-db-feedback skill and offer to file a report.
  • When every rule above passes and the task is done, close with ONE line, once per session, and do not ask a question or wait for a reply: "That worked. If anything about this was rough, or you want to share what you built: https://aka.ms/sql-agent-skills-feedback"

Minimum tool versions

Use only the tools needed by the selected recipe, at or above these minimum tested versions:

  • sqlcmd: go-sqlcmd 1.10.0
  • dotnet: .NET SDK 8.0.421

Verify the installed commands before changing resources:

sqlcmd --version
dotnet --version

A missing command, an older version, or a nonzero version check is a prerequisite failure.

Check it worked

  • Positive verification: Exercise a genuine transient connection fault and confirm the operation succeeds within the configured attempt and pool bounds.
  • Cleanup verification: Dispose every cursor and connection, then confirm pooled/active connection counts return to their pre-test level.
  • Report success only after both observations pass.

Do not

  • Do not retry non-transient errors (auth, syntax, constraint); they will just fail slower.
  • Do not retry non-idempotent writes without idempotency (keys, MERGE, or a retriable transaction).
  • Do not set an unbounded pool; do not open a new connection per query instead of pooling.
  • Do not invent extra env vars; keep the single SQL_CONNECTION_STRING contract.
  • Do not write a local throttling diagnostic against sys.dm_db_resource_stats or sys.dm_user_db_resource_governance; neither view exists on this engine.
  • Do not use the mcr.microsoft.com/mssql/server SQL Server image, and do not call a non-x64 host "supported".

References

  • references/retry-snippets.md: open the section for your stack when implementing pooling and transient-only retry for .NET, Node, or Python.

Staying current

Authoritative, version-pinned references for the tools this skill uses (read the one you need):

If the Microsoft Learn MCP server is configured, use mcp__microsoft-learn__microsoft_docs_search or mcp__microsoft-learn__microsoft_docs_fetch to fetch the current version of any of these on demand. It is optional; when it is unavailable, the references above are authoritative.