Preparing Docker

• install docker
• switch to windows container

Running SQL Server on Docker

• Courtesy: https://nexxtjump.com/2017/12/12/step-by-step-guide-to-run-sql-server-in-a-windows-docker-container/
• Using SQL Server Developer edition (all features are available)

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

• Courtesy: persisting-sql-server-data-in-docker-containers by mapping host directory

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