DEV Community

Kevin George
Kevin George

Posted on • Originally published at datastories.aletasystems.com on

Containers | Quick Start SQL Server Containers

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

Top comments (0)