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
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.
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).
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.
-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
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.
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.
FROM mcr.microsoft.com/mssql/server:2017-latest COPY . / RUN chmod +x /db-init.sh CMD /bin/bash ./entrypoint.sh
/db-init.sh & /opt/mssql/bin/sqlservr
Note: Be sure to note that the SA password is being used in this script.
sleep 25s echo "[+] Running SQL Setup Script" /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P 4TVR.a_E72}7Mg -d master -i 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.
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“.
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="yourNewP@44word"'