Run sql server in docker

2023-12-05 10:13 由 TigerLu 发表于 #数据库

Pull docker image

Pull the latest image of SQL Server 2022

docker pull mcr.microsoft.com/mssql/server:2022-latest

Run in container

docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=<password>" -p 11433:1433 --name <sqlserver2022> --hostname <sqlserver2022> -d mcr.microsoft.com/mssql/server:2022-latest

It's recommended to use the same string for 'name' and 'hostname', could be 'sqlserver2022' or anything you like.

Connect to SQL Server

1. Start an interactive bash shell inside your running container

docker exec -it sqlserver2022 "bash"

2. Inside the bash shell, connect locally with 'sqlcmd'

/opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "<password>"

3. You can connect with SSMS too, using the public IP address, followed by comma separator and then the port (xxx.xx.xx.xxx,port)

Persist DB

Using data volume containers

docker volume create sqlserver-2022

docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=<password>" -p 11433:1433 --name <sqlserver2022> --hostname <sqlserver2022> -v sqlserver-2022:/var/opt/mssql -d mcr.microsoft.com/mssql/server:2022-latest

The target should be '/var/opt/mssql', otherwise it won't work.

Mount a host directory as data volume

docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=<password>" -p 11433:1433 --name <sqlserver2022> --hostname <sqlserver2022> -v <host directory>/data:/var/opt/mssql/data -v <host directory>/log:/var/opt/mssql/log -v <host directory>/secrets:/var/opt/mssql/secrets -d mcr.microsoft.com/mssql/server:2022-latest

Connect to SQL Server from another container

1. Run the container with the specified network <network-name>

docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=<password>" -p 11433:1433 --name <sqlserver2022> --hostname <sqlserver2022> -v sqlserver-2022:/var/opt/mssql --network <network-name> --network-alias <network-alias> -d mcr.microsoft.com/mssql/server:2022-latest

2. Update app's DB connection string, the host name in 'Data Source' should be the <network-alias> defined in the previous step

"ConnectionStrings": {
    "Xxx-Context": "Data Source=<network-alias>,1433;Database=<DB-name>;User ID=sa;Password=<password>;MultipleActiveResultSets=true;Encrypt=False" 
}

3. Run the app container in the same network <network-name>

docker run -p 32774:80 --name <app-name> --network <network-name> -d <app-image>

Instead of launching each container separately, you could also choose docker-compose to launch all the containers with a single command, and no need to specifically create the network, docker-compose will create it automatically for you.

services:
  <service-name>: //service-name automatically become a network alias 
    image: mcr.microsoft.com/mssql/server:2022-latest
    container_name: <container-name>
    ports:
      - 21433:1433
    environment:
      - ACCEPT_EULA=y
      - MSSQL_SA_PASSWORD=<password>
    volumes:
      - <volume-name>:/var/opt/mssql

  <service-name>:
    image: <app-image-name>
    container_name: <container-name>
    ports:
      - 32774:80

volumes:
  <volume-name>: 
    external: true //mark external to 'true' to use a volume outside the compose project, otherwise a new [COMPOSE_PROJECT_NAME]_<volume-name> volume will be automatically created and used.

networks:
  default: // The auto created network is called [COMPOSE_PROJECT_NAME]_default by default, you can custmized it in networks attribute
    name: <customized-network-name>

The [COMPOSE_PROJECT_NAME] environment is the compose file's parent folder name by default, while you can set/redefine it through the command line parameter '-p', or use the .env file to make it trackable.

COMPOSE_PROJECT_NAME=<costomized-project-name>

Finally, run the compose.yaml file to launch all containers

docker compose up -d

References:

https://learn.microsoft.com/en-us/sql/linux/quickstart-install-connect-docker?view=sql-server-ver16&pivots=cs1-bash