--- name: azuresql-db-from-sql-server description: "Use when a project runs mcr.microsoft.com/mssql/server in Docker or compose and local dev should behave like Azure SQL Database: switch to its container, flag SQL Server-only features." --- # Migrate from the SQL Server image to the Azure SQL Database container This skill converts an existing local SQL Server setup (the SQL Server image `mcr.microsoft.com/mssql/server`) into the **Azure SQL Database** container so local dev matches Azure SQL Database behavior. The two are not the same engine: | | SQL Server image | Azure SQL Database container | |---|---|---| | Image | `mcr.microsoft.com/mssql/server` | `sqldbpreview-dpgaeqhmgphzd4bk.azurecr.io/azure-sql/db-dev:latest` | | `SERVERPROPERTY('EngineEdition')` | 2/3/4/8 | **5** | | `SERVERPROPERTY('Edition')` | e.g. 'Developer Edition' | **'SQL Azure'** | | DB model | one instance, many DBs, `USE` works | master for provisioning only; user DB for work; a user-database session returns `Msg 40508` on `USE`, exactly as in the cloud; a `master` connection is a provisioning session where the filter is not enforced | If a project is using the SQL Server image but wants Azure-faithful local dev, **stop and switch to the container.** This skill is self-contained; for full container detail see the **azuresql-db-container** skill. ## When to use - The project Dockerfile, compose file, or run script references `mcr.microsoft.com/mssql/server` or `mssql/server`. - There is an sqlcmd plus SA password setup, or someone says "SQL Server in Docker" / "a local mssql container". - The user asks for "SQL Server locally" but actually targets Azure SQL Database. ## Migration steps ### 1. Detect SQL Server usage Search the repo for the SQL Server image and the patterns that move with it: ```bash grep -rniE 'mcr\.microsoft\.com/mssql/server|mssql/server|ACCEPT_EULA|MSSQL_SA_PASSWORD|SA_PASSWORD|sqlcmd|Server=localhost,1433|Database=master' . 2>/dev/null ``` Anything that hits is a candidate for rewrite below. ### 2. Sign in to the preview registry The image is in a private preview registry; sign in once with the shared pull-only credentials provided when you sign up at https://aka.ms/sqldbcontainerpreview-signup (they may be rotated during the preview): ```bash docker login sqldbpreview-dpgaeqhmgphzd4bk.azurecr.io ``` Registry and tag are provisional during Private Preview. ### 3. Rewrite the image and add --platform Replace the SQL Server image with the Azure SQL Database image. The Azure image is x64 only, so on a non-x64 host add `--platform linux/amd64` (Docker) or `platform: linux/amd64` (compose). - Old: `mcr.microsoft.com/mssql/server:2022-latest` - New: `sqldbpreview-dpgaeqhmgphzd4bk.azurecr.io/azure-sql/db-dev:latest` Keep `ACCEPT_EULA=Y`, require a unique `MSSQL_SA_PASSWORD` from the caller's environment, keep the SA login, and keep port 1433. See [references/migrate-compose.md](references/migrate-compose.md) for a before/after compose. ### 4. Start the container and provision appdb first The engine does **not** auto-create databases on connect, and it is **not** ready the instant `docker run` returns. Use the canonical start recipe: it picks a free host port, adds `--platform` only on non-x64 hosts, waits for readiness with a retry loop, and provisions `appdb` inside that same loop. The `-b -l 2` flags make a SQL error set the exit code so transient startup errors (e.g. `Msg 913`) are retried, not masked. ```bash : "${MSSQL_SA_PASSWORD:?Set MSSQL_SA_PASSWORD to a unique development secret}" 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 /bin/sh -c 'SQLCMDPASSWORD="$MSSQL_SA_PASSWORD" exec /opt/mssql-tools18/bin/sqlcmd -S localhost -U sa -C -b -l 2 -Q "IF DB_ID('"'"'appdb'"'"') IS NULL CREATE DATABASE appdb;"' \ >/dev/null 2>&1; do sleep 2; done echo "ready on localhost,$HOST_PORT" ``` A `master` connection is for provisioning only; do real work on `appdb`. ### 5. Re-point connection strings from master to the user DB SQL Server projects often connect straight to `master` (or rely on a DB that SQL Server auto-creates). Azure SQL Database will not auto-create a database. Select the target database in the connection string (`Database=appdb`, or `-d appdb` for sqlcmd). Avoid `USE` to switch databases. In a user-database session (the Azure-faithful context where you develop), `USE` returns `Msg 40508`, exactly as in Azure SQL Database in the cloud. A `master` connection is a provisioning session where the Azure statement filter is not enforced, so `USE` appears to work there, but `master` is for provisioning only, not application work. Standardize on one `SQL_CONNECTION_STRING` env var, substituting the `HOST_PORT` selected above for ``: ``` Server=localhost,;Database=appdb;User Id=sa;Password=;TrustServerCertificate=true ``` House style spells the keywords `User Id=` / `Password=` / `Database=`, which keeps the examples consistent. `Uid=` and `Pwd=` are documented SqlClient synonyms and work too. For sqlcmd use `-C` to trust the self-signed cert and `-d appdb` to pick the database. ### 6. Seed AFTER provisioning (no auto-init folder) The image does **not** auto-run `/docker-entrypoint-initdb.d/*.sql`. That is a Postgres/MySQL convention and is not honored here; do not rely on it. Seed by running your script against the provisioned database: ```bash docker exec -i sqldb /bin/sh -c 'SQLCMDPASSWORD="$MSSQL_SA_PASSWORD" exec /opt/mssql-tools18/bin/sqlcmd -S localhost -U sa -C -d appdb -i /dev/stdin' < seed.sql ``` ### 7. Remove SQL Server-only features Some features exist only in the SQL Server image and must be removed or replaced. Flag and fix every hit. Open [references/sql-server-vs-azure-feature-matrix.md](references/sql-server-vs-azure-feature-matrix.md) when you meet a feature this list does not name. - **SQL Server Agent** jobs: not available; use an external scheduler. - **FILESTREAM / FileTable**: not supported; store blobs in columns or external storage. - **Full Service Broker** cross-instance messaging: not supported. - **Cross-server distributed transactions** (MS DTC, linked servers): not supported. - **Windows Auth / NTLM / Integrated Security**: not supported; use SA / SQL auth. - **Instance-level tuning with `sp_configure`**: the procedure is **absent**, not blocked. Calling it returns `Msg 2812` ("Could not find stored procedure 'sp_configure'"), which is a missing object rather than a refusal, so there is no permission to grant and no flag to unblock. Delete the inherited tuning step; it has no translation on this engine. - **`BACKUP` / `RESTORE`**: refused in every session with `Msg 40510`, exactly as in Azure SQL Database in the cloud. A `.bak` is not a migration route into this engine; use SqlPackage with a bacpac or dacpac (see the **azuresql-db-import** skill). - **`ALTER DATABASE ... SET RECOVERY`**: refused with `Msg 40517`, which is the other refusal shape. `ALTER DATABASE SET` itself is supported and only this option is refused, so a carried-over configuration script can be half accepted and leave the database in a state nobody intended. Drop the recovery-model step. ### 8. Verify identity Confirm you are on the Azure SQL Database engine: ```bash docker exec -i sqldb /bin/sh -c 'SQLCMDPASSWORD="$MSSQL_SA_PASSWORD" exec /opt/mssql-tools18/bin/sqlcmd -S localhost -U sa -C -d appdb -Q "SELECT SERVERPROPERTY('"'"'EngineEdition'"'"') AS EngineEdition, SERVERPROPERTY('"'"'Edition'"'"') AS Edition;"' ``` Expect `EngineEdition = 5` and `Edition = SQL Azure`. ## Vectors (if the project uses embeddings) The Azure SQL Database engine has a native `VECTOR(n)` type and `VECTOR_DISTANCE('cosine', a, b)`. Insert with `CAST(CAST(? AS NVARCHAR(MAX)) AS VECTOR(n))` where **n is a LITERAL, never a bind parameter** (a parameter dimension fails with "Incorrect syntax near '@P3'"). `CREATE VECTOR INDEX` (DiskANN) **works on this image**, as the Known limitations page says. It needs `SET QUOTED_IDENTIFIER ON` and at least 100 rows with non-null vectors (`Msg 42266` below that). Full-scan top-k stays exact and stays the right choice for a small table. The `azuresql-db-rag` skill carries the rules the index imposes. ## Validation rules - Image is `sqldbpreview-dpgaeqhmgphzd4bk.azurecr.io/azure-sql/db-dev:latest`, not `mcr.microsoft.com/mssql/server`. - `appdb` is created on a master connection before any app connects to it. - App connection strings use `Database=appdb`, never `Database=master` for real work. - No `USE ` statements: in a user-database session, `USE` returns `Msg 40508`, exactly as in Azure SQL Database in the cloud; a `master` connection is a provisioning session where the filter is not enforced, but `master` is for provisioning only. Select the database in the connection string. - `--platform linux/amd64` present on non-x64 hosts only. - `EngineEdition` returns 5. - 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 versions: - **`sqlpackage`**: SqlPackage 170.4.83.3 - **`dotnet`**: .NET SDK 8.0.421 Verify the installed commands before changing resources: ```bash sqlpackage /Version dotnet --version ``` A missing command, an older version, or a nonzero version check is a prerequisite failure. ## Check it worked - **Positive verification:** Require assessment and deployment commands to exit zero, then compare the selected schema and source-bound row counts on source and target. - **Cleanup verification:** Remove migration-only staging files and target resources while leaving the source database unchanged. - Report success only after both observations pass. ## Do not - Do not keep `mcr.microsoft.com/mssql/server`; it is a different engine. - Do not rely on database auto-creation or on `/docker-entrypoint-initdb.d/*.sql`. - Do not use `USE appdb`; select the database in the connection string. - Do not call a non-x64 host "supported"; just add `--platform linux/amd64` on non-x64 hosts. - Prefer `User Id=` / `Password=` in the .NET-style string. That is house style, not a requirement: `Uid=` / `Pwd=` are valid synonyms. - Do not pass the vector dimension as a bind parameter. - Do not keep SQL Agent, FILESTREAM, full Service Broker, cross-server distributed transactions, or Windows Auth. - Do not go looking for a permission or a flag that makes `sp_configure` run. `Msg 2812` says the procedure is not there, so there is nothing to grant. - Do not carry over a `BACKUP`/`RESTORE` step (`Msg 40510`) or an `ALTER DATABASE ... SET RECOVERY` step (`Msg 40517`). ## References - [references/sql-server-vs-azure-feature-matrix.md](references/sql-server-vs-azure-feature-matrix.md): what carries over, what changes, what is gone. Read it when triaging SQL Server-only features found in step 7. - [references/migrate-compose.md](references/migrate-compose.md): before/after docker-compose with a provision step. Read it when rewriting a compose file. ## Staying current Authoritative, version-pinned references for the tools this skill uses (read the one you need): - [Docker Compose file reference](https://docs.docker.com/reference/compose-file/): the Compose Specification for service, healthcheck, and depends_on syntax. - [SqlConnection connection string keywords](https://learn.microsoft.com/en-us/dotnet/api/microsoft.data.sqlclient.sqlconnection.connectionstring): the full connection-string keyword table. 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.