SQL Server - Docker Container

In this article, we'll be creating a docker container that automates the process of running SQL Server and restoring a database (in this case, the Adventureworks database). 

Configure Docker Image

Download the AdventureWorks2017 Image

wget https://github.com/Microsoft/sql-server-samples/releases/download/adventureworks/AdventureWorks2017.bak

Create a file 'Dockerfile'.

# Use the official image as a parent image.
FROM mcr.microsoft.com/mssql/server:2019-latest

# Set environment variables.
ENV ACCEPT_EULA=Y
ENV SA_PASSWORD=YourStrong@Passw0rd

# Make SQL Server scripts available to run later in the Dockerfile.
COPY . /tmp/

# Grant permissions for to our scripts to execute
USER root
RUN chmod +x /tmp/run-initialization.sh

# Switch back to mssql user.
USER mssql

# Start as entry point.
CMD /bin/bash ./tmp/run-initialization.sh & /opt/mssql/bin/sqlservr

Next, we will need a shell script run-initialization.sh that will be used in the Dockerfile. This script will restore the Adventureworks database:

#!/bin/bash

# Start SQL Server, start the script to restore the database, and then import the /tmp/AdventureWorks2017.bak database backup
# In the background, tail -f anything (so Docker doesn't exit)
/opt/mssql/bin/sqlservr & /tmp/restore-database.sh & tail -f /dev/null

And then we have restore-database.sh:

#!/bin/bash

# Wait for SQL Server to start up.
echo "Waiting for SQL Server to start..."
sleep 20s

# Restore the Adventureworks database.
/opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P YourStrong@Passw0rd \
-Q "RESTORE DATABASE [AdventureWorks] FROM DISK = N'/tmp/AdventureWorks2017.bak' WITH FILE = 1, MOVE N'AdventureWorks2017' TO N'/var/opt/mssql/data/AdventureWorks2017.mdf', MOVE N'AdventureWorks2017_Log' TO N'/var/opt/mssql/data/AdventureWorks2017_Log.ldf', NOUNLOAD, REPLACE, STATS = 5"

This script waits for SQL Server to start, then restores the Adventureworks database. Make sure that AdventureWorks2017.bak is in the same directory as your Dockerfile and scripts.

Now, build the Docker image from your Dockerfile:

docker build -t adventureworks-image .

And finally, run the Docker container:

docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=YourStrong@Passw0rd" -p 1433:1433 --name adventureworks-container adventureworks-image

With this approach, every time you start the container, it will start SQL Server and restore the Adventureworks database. Please note that replacing a password in the scripts and Dockerfile with your own strong password is required.

Connect from Test Data Automation

Assuming none of the credentials were changed you can connect to the database with the following settings.

Server type: Database Engine

Server name: The IP address of your Docker host and port number, in the format <host_ip>,1433

Authentication: SQL Server Authentication

Login: SA

Password: Enter the password you used when creating the Docker container ("YourStrong@Passw0rd" as per the previous instructions)

In the Test Data Automation instance we used the following settings for the Connection details.

And for the Security tab.