🧱 SQL Server with Compose (Docker or Podman)¶

# Optional global engine switch
ENGINE=${ENGINE:-docker}  # set ENGINE=podman if you use Podman

$ENGINE compose up -d
$ENGINE compose logs -f mssql
$ENGINE exec -it mssql /bin/bash

# File: compose.yaml
# Purpose: Local SQL Server 2022 with a one-time init job. Works on Docker or Podman.

services:
  mssql:
    image: mcr.microsoft.com/mssql/server:2022-latest
    container_name: mssql
    restart: unless-stopped

    environment:
      ACCEPT_EULA: "Y"                # Mandatory
      MSSQL_SA_PASSWORD: "Str0ng!Passw0rd"  # Must meet SQL Server complexity rules
      MSSQL_PID: "Developer"          # Developer (free) | Express | Standard | Enterprise (licensed)
      TZ: "Europe/Vilnius"

    ports:
      - "1433:1433"                   # If busy, use "11433:1433" and adjust clients to port 11433

    volumes:
      - mssql_data:/var/opt/mssql           # Data files live here
      - ./backups:/var/opt/mssql/backups:Z  # Optional: host-mounted backups dir (SELinux :Z ok on Docker too)

    # Simple TCP healthcheck that doesn't rely on sqlcmd being present in this image
    healthcheck:
      test: ["CMD-SHELL", "bash -c 'echo > /dev/tcp/127.0.0.1/1433'"]
      interval: 10s
      timeout: 3s
      retries: 30
      start_period: 30s

  # One-time init job: runs *.sql in ./initdb against the mssql service after it's healthy
  mssql-init:
    image: mcr.microsoft.com/mssql-tools:latest
    container_name: mssql-init
    depends_on:
      mssql:
        condition: service_healthy
    environment:
      MSSQL_SA_PASSWORD: "Str0ng!Passw0rd"
    volumes:
      - ./initdb:/initdb:ro,Z
    entrypoint: ["/bin/bash", "-lc"]
    command: >
      '
      set -euo pipefail;
      HOST=mssql; PORT=1433; USER=sa; PASS="$MSSQL_SA_PASSWORD";
      # wait for TCP just in case
      for i in {1..60}; do
        (echo > /dev/tcp/${HOST}/${PORT}) >/dev/null 2>&1 && break || sleep 2;
      done;
      # run all .sql files in lexical order; ignore if directory empty
      shopt -s nullglob;
      for f in /initdb/*.sql; do
        echo \"[mssql-init] Running: $f\";
        /opt/mssql-tools/bin/sqlcmd -S ${HOST},${PORT} -U ${USER} -P \"${PASS}\" -i \"$f\";
      done;
      echo \"[mssql-init] Done.\"
      '

    # Run once on first `up`; remove after success
    restart: "no"

volumes:
  mssql_data:

$ENGINE compose up -d
$ENGINE compose logs -f mssql      # wait for: "SQL Server is now ready for client connections"
$ENGINE compose logs -f mssql-init # see init job output (it exits when done)
$ENGINE ps

./initdb/
 ├─ 01-database-and-login.sql
 ├─ 02-schema.sql
 └─ 03-seed.sql

-- Create database
IF DB_ID(N'AppDb') IS NULL
  CREATE DATABASE AppDb;
GO

-- Create server login
IF SUSER_ID(N'app_login') IS NULL
  CREATE LOGIN app_login WITH PASSWORD = 'AppUser!234';
GO

-- Map a database user to the login
USE AppDb;
GO
IF USER_ID(N'app_user') IS NULL
  CREATE USER app_user FOR LOGIN app_login;
GO

-- Grant a sane role; tighten later as needed
ALTER ROLE db_owner ADD MEMBER app_user;
GO

USE AppDb;
GO

IF OBJECT_ID(N'dbo.customers', N'U') IS NULL
BEGIN
  CREATE TABLE dbo.customers(
    id INT IDENTITY(1,1) PRIMARY KEY,
    email NVARCHAR(255) NOT NULL UNIQUE,
    first_name NVARCHAR(100) NOT NULL,
    last_name NVARCHAR(100) NOT NULL,
    status NVARCHAR(20) NOT NULL DEFAULT N'ACTIVE',
    created_at DATETIME2 NOT NULL DEFAULT SYSDATETIME()
  );
END
GO

CREATE INDEX IX_customers_email ON dbo.customers(email);
GO

USE AppDb;
GO

MERGE dbo.customers AS tgt
USING (VALUES
  (N'ada@example.com',  N'Ada',   N'Lovelace', N'ACTIVE'),
  (N'grace@example.com',N'Grace', N'Hopper',   N'ACTIVE')
) AS src(email, first_name, last_name, status)
ON tgt.email = src.email
WHEN MATCHED THEN
  UPDATE SET first_name = src.first_name, last_name = src.last_name, status = src.status
WHEN NOT MATCHED THEN
  INSERT(email, first_name, last_name, status)
  VALUES(src.email, src.first_name, src.last_name, src.status);
GO

# Start an interactive sqlcmd without installing tools on the host
$ENGINE run --rm -it --network host mcr.microsoft.com/mssql-tools:latest \
  /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P 'Str0ng!Passw0rd'

CREATE LOGIN dev_login WITH PASSWORD = 'Dev!Passw0rd';
GO
USE AppDb;
GO
CREATE USER dev_user FOR LOGIN dev_login;
GO
ALTER ROLE db_datareader ADD MEMBER dev_user;  -- read-only example
GO
QUIT

$ENGINE compose run --rm mssql-init

$ENGINE compose down -v     # WARNING: also deletes the mssql_data volume (all data)
$ENGINE compose up -d

# open a shell in the server container
$ENGINE exec -it mssql /bin/bash

# then:
# Full backup to the mounted host directory
/opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P 'Str0ng!Passw0rd' -Q \
  "BACKUP DATABASE AppDb TO DISK = N'/var/opt/mssql/backups/AppDb_full.bak' WITH INIT, COMPRESSION, STATS = 10;"

-- inside sqlcmd
ALTER DATABASE AppDb SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
RESTORE DATABASE AppDb FROM DISK = N'/var/opt/mssql/backups/AppDb_full.bak' WITH REPLACE, STATS = 10;
ALTER DATABASE AppDb SET MULTI_USER;
GO

spring.datasource.url=jdbc:sqlserver://localhost:1433;databaseName=AppDb;encrypt=true;trustServerCertificate=true
spring.datasource.username=app_login
spring.datasource.password=AppUser!234
spring.jpa.hibernate.ddl-auto=validate

$ENGINE compose logs -f mssql
$ENGINE exec -it mssql /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P 'Str0ng!Passw0rd' -Q "SELECT @@VERSION;"
$ENGINE exec -it mssql /bin/bash
$ENGINE compose down
$ENGINE compose down -v   # DANGER: wipes data