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
|
1 2 3 4 |
FROM mcr.microsoft.com/mssql/server:2017-latest COPY . / RUN chmod +x /db-init.sh CMD /bin/bash ./entrypoint.sh |
Entrypoint Script
|
1 2 3 4 5 |
#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.
|
1 2 3 4 5 6 7 |
# 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
|
1 2 3 4 5 6 7 8 9 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
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“.
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]"'
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.
