Containers | Quick Start SQL Server Containers

‘It works on my machine’, ‘What version are you using’, ‘check your config’ - another developer

Components

Let me try and explain the graphic above

1. Container Registry: A place where container-images are hosted and can be downloaded onto your local docker instance. A common public repository is docker-hub. You can also have private container repositories, lookup azure container registry
2. Docker on your machine, for illustrative purposes I have broken it down into two parts
   1. a local repository of container images
   2. a space that represents running containers, an instance of a container. You can have multiple instances of a container running

The docker engine can run in either Windows or Linux mode but not both simultaneously (at the moment), this is important as you cannot run a Linux SQL Server side by side with a windows SQL server or more realistically, you cannot use a windows-SQL-server with a Linux Web server. This is one of the reasons you will see that I tend to use Linux containers over windows.

Container WorkFlow

1. We Get the container image
   1. for windows microsoft/mssql-server-windows-developer by running docker pull microsoft/mssql-server-windows-developer:latest
   2. for linux microsoft/mssql-server-linux by running docker pull microsoft/mssql-server-linux:latest
2. Run (start) the container docker run -d -p 14333:1433 --name myDocker --hostname myDocker -e SA_PASSWORD='$3cureP@ssw0rd' -e ACCEPT_EULA=Y microsoft/mssql-server-windows-developer
   1. for windows docker run -d -p 14333:1433 --name myDocker --hostname myDocker -e SA_PASSWORD='$3cureP@ssw0rd' -e ACCEPT_EULA=Y microsoft/mssql-server-windows-developer
   2. for linux docker run -d -p 14333:1433 --name myDocker --hostname myDocker -e SA_PASSWORD='$3cureP@ssw0rd' -e ACCEPT_EULA=Y microsoft/mssql-server-linux
3. Connect
   1. using SSMS Server localhost,14333 , sa , $3cureP@ssw0rd
   2. using Powershell Invoke-Sqlcmd -ServerInstance "localhost,14333" -Database master -Username sa -Password '$3cureP@ssw0rd' -Query 'SELECT DB_NAME() as DatabaseName'
4. Stop the container docker stop myDocker
5. Start (Resume/Restart) the container docker start myDocker
6. Destroy the container docker rm myDocker --force

Note: Linux or Windows is not based on your OS but on the runtime version of docker-desktop as illustrated below, you can see switch to windows containers indicates I am currently running in Linux container mode.

Parameters:

Docker Reference docker run

-p 14333:1433 | --expose

-p defines the port mapping between the host machine and the container. For example, an httpd image exposes port 80, and similarly, the mssql-server-windows-developer exposes 1433.

The reason I mapped port 14333 to 1433 is that on my machine I have a SQL Server Developer Edition installed and running. If I bind to 1433:1433 then there would have been a port conflict between the docker instance and the local SQL Server instance.

--name myDocker

A friendly name for your container that allows you to run commands such as docker inspect myDocker vs docker inspect <ContainerGuidID>

--hostname myDocker

This is the value of the hostname which I needed specifically for JDBC URL. Please note that you can use localhost / 127.0.0.1 too

--env | -e

-e is a docker parameter for environment variables, the two variables exposed by the mssql-server-windows-developer image are

• SA_PASSWORD: it sets the sa password. (the password matches complexity requirements).
• ACCEPT_EULA: a flag to indicate you accept the EULA.

--volume

Simplistically, using --volume allows you to share a folder between host and container.

A few reasons you would do this are:

• I want to persist my .mdf files to my local disk.
• I have a .bak / .mdf file that is shared between developers
• I need to use BULK INSERT
• Any other reason that requires me to share files between the host and container instance.

Host: C:\DockerPersistedStorage\Containers\myDocker

Example: --volume 'C:\DockerPersistedStorage':'C:\DockerPersistedStorage' would map 'C:\DockerPersistedStorage' on your host machine to 'C:\DockerPersistedStorage' on the Docker instance.

Example: --volume 'C:\DockerPersistedStorage':'/path/on/linux/file/system/DockerPersistedStorage' would map 'C:\DockerPersistedStorage' on your host machine to '/path/on/linux/file/system/DockerPersistedStorage' on the Docker instance.