Run SQL Server with Docker

 

Why

    1. Developer machines
      On my developer machines I have installed various version of SQL Server. These run services in the background and are difficult to stop and uninstall.Containers offer the opportunity to install then destroy and start again.
    2. Development and Testing
      Store images/containers and then recreate
    3. Future: create images which derive from base images and store and share in a repository possibly in Azure Container Registry

Get Started:

Install docker for windows

https://docs.docker.com/docker-for-windows/install/

Turn on virtualisation
Hyper-V
Docker

Quickstart: Run SQL Server container images with Docker

https://docs.microsoft.com/en-us/sql/linux/quickstart-install-connect-docker?view=sql-server-2017

Note on using PowerShell

Some docker commands do not work inside the ISE.
For those, or all commands use PowerShell console window

Connect from outside the container

From SqlCmd or SSMS

Use the IP address of the machine that hosts your container (probably your machine)
e.g. 10.0.0.21,1433

The function inspect gives information about the container, but not the host IP:
docker inspect

docker inspect –format ‘{{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}’ <container id>

 

 

Restore a database into the image

Now, we have SQL running in docker, it would be useful to restore a database into that.

  1. Outside Docker
    Backup database
    Tip: SSMS backup in UI, but then “Script action to new query window” and store it
  2. Copy file into the running image
    Use Bash
    docker exec -it sql1 “bash”Then Linux commands to make directory
    Exit Bash
    docker cp MyDatabase.bak sql1:\var\opt\mssql\backup
  3. Inside Docker
    Restore database – use script – may need to Move files

 

Bake files into an image for reuse

docker commit sql1 sqltestco:sql2017
docker images

Clean up with:
docker rmi sqltestco:sql2017

 

Use image to create docker images

Now we have an image with our database in it. We may want to use it once or many times.

Keep changing the name and the port
I found that –name needs to come first

docker run –name sqltestco1 -d -p 1434:1433 sqltestco:sql2017
docker run –name sqltestco2 -d -p 1435:1433 sqltestco:sql2017

Now have 3 instances of SQL running.

As needed:

docker start
docker stop
docker rm

 

Push images to Docker Hub

Use:
docker images
docker tag <imagename>:<tagname> <dockerId>/<repository>:<tagname>
docker login (good idea to spell everything correctly)
docker push <dockerId>/<repository>:<tagname>

Took a few attempts – got there. Private repository.

 

 

Troubleshooting

Seem to get a conflict if other containers exist but are stopped. Possibly good practice to be aware of all containers with:

docker ps -a

Error response from daemon: … userland proxy

Error response from daemon: driver failed programming external connectivity on endpoint … Error starting userland proxy: mkdir /port…

Restart docker – fixed it for me.

 

See also

Pluralsight : Getting started with Docker on Windows

https://app.pluralsight.com/library/courses/docker-windows-getting-started/table-of-contents

 

 

 

Advertisements