Author : HASSAN MD TAREQ

Preparing Docker

  • install docker
  • switch to windows container

Running SQL Server on Docker

Pull image

docker pull microsoft/mssql-server-windows-developer:2017-latest

Build and run container from image

must run commands in powershell or powershell tab in cmder

docker run `
--name FooSqlServer `
-e sa_password=FooSqlServer101 `
-e 'ACCEPT_EULA=Y' `
-p 1433:1433 `
-d microsoft/mssql-server-windows-developer:2017-latest

Test connection

  • command: sqlcmd –S <YourIP>,<Port> -U sa -P password
  • -S : flag for server
  • -U : user
  • sa -P : server administrator password
  • to check it: docker inspect --format '{{.NetworkSettings.Networks.nat.IPAddress}}' FooSqlServer (should show ip like: 172.19.30.0) or ``
  • no space between before & after ‘,’ (wrong: ip, port, right: ip,port)
  • The number 1> means that it is connected and ready to receive sentences to execute

Command to execute in powershell:

sqlcmd -S 172.19.29.211,1433 -U sa -P FooSqlServer101

# Show  all the variables set
1> :ListVar

Persisting data - decoupling data persistence from container

  • To ensure data will persist even after docker container is removed, we need to decouple data persistence from the container
  • SQL Server container is running on docker
  • Each docker container has associated writeable layer
  • By default container writes data to writable layer
  • When container is removed, all data in writable layer will be gone,
  • If we save data to our database, container will write data to writable layer. When sql server container is removed, all data will be gone

2 ways we can decouple data persistence from container (writable layer)

  • By exposing host directory to container
  • By uing volume

Decoupling by exposing host directory to container

Decoupling by using volume

  • Docker creates a small Linux VM and exposes it to all containers (to provide kernel services)
  • Volumes are created inside Linux VM and mapped to a container if that container is started with volume ( i.e. ... -v foo_volume:/var/opt/mssql for linux container)
  • SQL Server will save data to /var/opt/mssql and /var/opt/mssql is mapped to foo_volume. So, data will be to foo_volume
  • We can create volume first and then mount it to container while starting/running the container (run vs start - run = create from image + start, should be used for first time)
  • Create Volume: https://docs.docker.com/storage/volumes/#create-and-manage-volumes
  • Courtesy: persisting-sql-server-data-in-docker-containers using volume
docker volume create foo_volume   # default: C:\ProgramData\Docker\volumes

docker volume ls

docker volume inspect foo_volume  # will show Mountpoint

Now volume path form Mountpoint (docker volume inspect foo_volume) ND run container with -v (must use powershell or powershell tab in cmder to execute commands)

docker run `
--name FooSqlServer `
-e sa_password=FooSqlServer101 `
-e 'ACCEPT_EULA=Y' `
-p 1433:1433 `
-d microsoft/mssql-server-windows-developer:2017-latest `
-v c:\ProgramData\Docker\volumes\foo_volume:c:\foo_volume

(did not work ‘…file not found error…’ for windows container)

Notes:

  • we are using windows container, therefore using windows style path (use linux style path when using linux container)
  • c:\foo_volume => inside container
  • C:\ProgramData\Docker\volumes\foo_volume => in host machine
  • foo_volume is mapped to container’s c:\foo_volume
  • We can skip creating volume since docker will create the volume if does not exists

Next: connecting-ssms-to-sql-server-in-docker