Microsoft continues its effort to release open source software and frameworks, with their recent release of Microsoft SQL Server that runs on Windows, Mac and Linux. This is easy to deploy and get up and running quickly with Docker containers. There are three main containers that you may want to reference as you get started.
- https://hub.docker.com/r/microsoft/mssql-server-linux/
- https://hub.docker.com/r/microsoft/mssql-server-windows-developer/
- https://hub.docker.com/r/microsoft/mssql-server-windows-express/
The first link is for the Linux container for MSSQL, where as the second and third links are for the developer and express images for Windows only.
To pull an image and get started, it’s as simple as executing a docker run with a few environment variables.
docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=P@$$w0RD' -e 'MSSQL_PID=Express' -p 1433:1433 -d microsoft/mssql-server-linux:latest
To document a few of the variables we have thrown into this command,
- -e is for Environment Variable
- ACCEPT_EULA accepts the MSSQL terms of use
- SA_PASSWORD sets the root sa user password
- MSSQL_PID sets the edition of MSSQL including Developer, Express, Standard, Enterprise, and EnterpriseCore
- -p is for exposing the Host_Port:Container_Port
- -d allows the container to start up in the background (daemon)
- lastly followed by the image name
If you need to jump on the MSSQL container and execute a few raw SQL commands, you can enter the container with the following:
docker exec -it <container_id|container_name> /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P P@$$w0RD
For example, if you need to create a new database to be used by another application.
create database NAMEOFDB; GO
Type ‘exit’ in the terminal to get out of the interactive SQL shell. I also recommend downloading/installing SQL Operations Studio which is a cross-platform application for interacting with a MSSQL server.
Persistent Storage
Docker run takes an image and spins up a container. That container is available on the machine until the host destroys the container through a destructive command, ‘docker rm containername’. So when a container is stopped and restarted, it will save the databases. However, containers are meant to be spun up and torn down. For example, if a hot fix for MSSQL is released, all you should have to do is spin up a new container and point to the latest release of the image. MSSQL stores databases in the /var/opt/mssql
There are two options, mapped host directories and data volume containers. From the docker doc’s,
Volumes are the preferred mechanism for persisting data generated by and used by Docker containers.
First you need to create a new volume with the following docker command. After you create the volume, you can inspect it and later remove it if you need to. When you inspect it, you should see the directory (Mounts__Source) that houses the volume on disk.
docker volume create NAME_OF_VOLUME docker inspect NAME_OF_VOLUME docker volume rm NAME_OF_VOLUME
Then you can update your run of the image with an additional argument to map to the volume,
docker run ... --mount source=NAME_OF_VOLUME,target=/var/opt/mssql ...
The other choice to persist databases in the container is to used a mapped host location. In this fashion, you don’t need to create the volume before hand.
docker run ... -v <host directory>:/var/opt/mssql...
Photo by Mr Cup / Fabien Barral on Unsplash
