Docker: MSSQL Server

Microsoft has created several images for MSSQL Server on Docker Hub. They have support for Windows and Ubuntu containers. It’s very easy to customize these images with setup scripts, execute commands, or build a custom image.

Pulling a Ubuntu 2019 SQL Server

docker pull mcr.microsoft.com/mssql/server:2019-CU5-ubuntu-16.04

Pulling a Windows 2019 SQL Server

docker pull mcr.microsoft.com/mssql/server:2019-latest

DockerHub: Microsoft SQL Server

GitHub: Azure Data SQL Samples Repository

Running a SQL Server Image in Docker

Make sure your password meets the requirements of SQL Server. Be sure to include alphanumeric characters, special characters, and be at least 8 characters long.

Flags

This command will start a container in a detached state (-d). The -e flags are used to map environmental variables for configuring the SQL server.

-d – detached state
-e – environmental variables
-p – port mapping host:container
–name – container name
-v – volume host_path:container_path

Note: for Windows use Windows paths (C:\path\example\) and for Linux use Linux paths (/path/example).

Password: 4\TVR.a_E72}7/Mg

Environmental Variables

ACCEPT_EULA – You must accept the end-user license agreement.
SA_PASSWORD – Server Administrator’s password. Your login will be “SA”.
MSSQL_PID – Product ID (Developer, Express, Standard, Enterprise, EnterpriseCore). The standard PID is Developer.

You can see more environmental variables here.

Starting a MSSQL Container

docker run -d -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=4\TVR.a_E72}7/Mg' -p 1433:1433 --name container_name mcr.microsoft.com/mssql/server:2017-latest

Executing Commands with Sqlcmd 

Sqlcmd runs SQL against the database. See the flags below.

https://docs.microsoft.com/en-us/sql/tools/sqlcmd-utility?view=sql-server-2017

Sqlcmd Flags

-S – Server (IP, Hostname, or localhost)
-U – User login… “SA”
-P – Server admin’s password
-E – Trusted connection
-d – Database
-Q – SQL Command

docker exec -it container_name /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P 4\TVR.a_E72}7/Mg

Backing Up a Database

BACKUP DATABASE [mrjb_TestDatabase] TO DISK = /var/opt/mssql/backup/MyBackup.bak'

docker exec -it container_name /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "4\TVR.a_E72}7/Mg"
-Q "BACKUP DATABASE [mrjb_TestDatabase] TO DISK = N'/var/opt/mssql/backup/MyBackup.bak' WITH NOFORMAT, NOINIT, NAME = 'WideWorldImporters-full', SKIP, NOREWIND, NOUNLOAD, STATS = 10"

Managing SQL Server in the Container

To gain access to the MSSQL server Docker container you will need to use the “exec” command with interactive flags (-i) and pseudo-terminal (-t).

docker exec -it container_name /bin/bash

Restarting MSSQL Server

sudo systemctl restart mssql-server

Microsoft.com: Configure SQL Server settings on Linux

Persisting Data into a Docker MSSQL Server Container

Persisting data can be done by mounting a volume or using a volume mount. If you’re just looking for something quick and dirty use a volume mount like this.

-v data:/var/opt/mssql

docker run -d -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=4\TVR.a_E72}7/Mg' -v data:/var/opt/mssql -p 1433:1433 --name container_name mcr.microsoft.com/mssql/server:2017-latest

Using a Script to Set Up an MSSQL Database

You can download a sample project at my GitHub: MSSQL Setup Script. I’ll break that process down. It’s rather easy using a Dockerfile.

Executing a shell script to set up a database can be done by creating a SQL, Shell script and a custom entry-point script.

Dockerfile

FROM mcr.microsoft.com/mssql/server:2017-latest
COPY . /
RUN chmod +x /db-init.sh
CMD /bin/bash ./entrypoint.sh

Entrypoint Script

#start SQL Server, start the script to create/setup the DB
#You need a non-terminating process to keep the container alive. 
#In a series of commands separated by single ampersands the commands to the left of the right-most ampersand are run in the background. 
#So - if you are executing a series of commands simultaneously using single ampersands, the command at the right-most position needs to be non-terminating
/db-init.sh & /opt/mssql/bin/sqlservr

db-init.sh

Note: Be sure to note that the SA password is being used in this script.

# wait for the SQL Server to come up
sleep 25s

echo "[+] Running SQL Setup Script"

# run the setup script to create the DB and the schema in the DB
/opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P 4TVR.a_E72}7Mg -d master -i db-init.sql

db-init.sql

USE master
GO
CREATE DATABASE mrjb_TestDatabase
GO
USE [mrjb_TestDatabase]
GO 
CREATE TABLE test (col1 int)
GO 

Building and running this image will execute the custom SQL setup script.

Docker Compose MSSQL Server

If you look at the code above for the custom image. You can use Docker Compose to run a build task against that Dockerfile by doing something similar to below.

I recommend checking out what Microsoft did. GitHub: SQL Server Samples

version: '3.3'

services:   
  mssql:
    build: ./docker/mssql/
    container_name: mrjb_TestDatabase
    ports:
      - 1433:1433
    restart: always
    environment:
      ACCEPT_EULA: 'Y'
      SA_PASSWORD: '4TVR.a_E72}7Mg' # pw is also in db-init.sh

Alternatively to building a custom image you could also write something like this. Lukasz Kurzyniec shared this on a post.
https://github.com/Microsoft/mssql-docker/issues/2

I also recommend checking out Lukasz Kurzyniec’s .NET Core Boiler Plate

version: "3.6"

services:
  mssql:
    image: mcr.microsoft.com/mssql/server:2017-latest
    container_name: mssql
    command: /bin/bash ./entrypoint.sh
    ports:
      - 1433:1433
    environment:
      - ACCEPT_EULA=Y
      - MSSQL_PID=Express
      - SA_PASSWORD=SomeStrongPwd123
    volumes:
      - dbdata:/var/opt/mssql/data
      - ./db/mssql/docker-entrypoint.sh:/entrypoint.sh
      - ./db/mssql/docker-db-init.sh:/db-init.sh
      - ./db/mssql/mssql-cars.sql:/db-init.sql

Connecting with SQL Management Studio (SSMS)

You can easily connect to the local database by using SQL Management Studio (SSMS). Set the Server Name to “localhost“; Login to “SA“; and password to “4\TVR.a_E72}7/Mg“.

https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver15

(SQL Management Studio)

Change the MSSQL Administrator’s Password

docker exec -it container_name /opt/mssql-tools/bin/sqlcmd \ -S localhost -U SA -P "4\TVR.a_E72}7/Mg" -Q 'ALTER LOGIN SA WITH PASSWORD="[email protected]"'

Read more on Microsoft.com

Active Directory: Joining the Domain with Ubuntu Images

// TODO

SQL Server Agents

// TODO

Udemy Videos for Docker

If you’re a big fan of Docker I highly recommend you watch the Doctor Captain’s (Bret Fisher) videos. They are over 19.5 hours of videos covering Docker, Kubernetes, Docker Compose, Swarm, and the Docker Registry.

https://www.udemy.com/course/docker-mastery/