loading...

Containers | Quick Start SQL Server Containers

kgeorge314 profile image Kevin George Originally published at datastories.aletasystems.com on ・3 min read

‘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

  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

container-components

  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.

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.

Discussion

pic
Editor guide