Containers | QuickStart SQL Server Containers

Containers, Local Development, DLM with Containers · 14 Jan 2020

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

container-components

Components

Let me try and explain the graphic above

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
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

container-components

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
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
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'
Stop the container docker stop myDocker
Start (Resume/Restart) the container docker start myDocker
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.

container-components

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.