azuresql-db-schema-migration

Schema migrations on the Azure SQL Database container

Apply schema migrations to the local Azure SQL Database container the same way you would against the cloud, so dev and prod stay identical. This is the Azure SQL Database engine (SELECT SERVERPROPERTY('EngineEdition') returns 5, Edition returns 'SQL Azure'), not the SQL Server image mcr.microsoft.com/mssql/server. If a tool or template points at the SQL Server image, stop and use the image below instead.

The one rule that breaks every migration tool

The engine does NOT auto-create databases on connect. Every migration tool assumes the target database already exists. So:

1. Provision appdb on a master connection FIRST.
2. Then point the migration tool at the user database (Database=appdb).

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 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. Always select the target database in the connection string (Database=appdb, or -d appdb for sqlcmd). A master connection is for provisioning only; run migrations against appdb.

Start the container and provision appdb (canonical recipe)

The engine is not ready the instant docker run returns. Wait with a retry loop and create appdb inside that same loop. Image is x64 only; on a non-x64 host the recipe adds --platform linux/amd64 automatically. The registry is private during Private Preview, so sign in first.

docker login sqldbpreview-dpgaeqhmgphzd4bk.azurecr.io   # pull-only creds by signing up at https://aka.ms/sqldbcontainerpreview-signup

# Pick a free host port and add the platform flag only on a non-x64 host (works in bash and zsh).
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=YourStr0ng_Passw0rd" \
  -p "$HOST_PORT:1433" sqldbpreview-dpgaeqhmgphzd4bk.azurecr.io/azure-sql/db-dev:latest
until docker exec sqldb /opt/mssql-tools18/bin/sqlcmd -S localhost -U sa -P "YourStr0ng_Passw0rd" -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"