- DarkLight
SQL Server - Docker Container
- DarkLight
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.