I am currently updating several of my GitHub projects to use Docker, so it's easier for users to get started. The WideWorldImporters project requires a SQL Server 2022 database and we'll need to restore Microsofts WideWorldImporters OLTP Database to it:
This should have been a very straightforward process, but the sqlcmd
coming with the SQL Server 2022 Docker image apparently
hangs when restoring a database. So we also need to download the latest set of mssql-tools
when building the image. Maybe this
article saves a poor soul some minutes.
All code can be found in a Git repository at:
Creating the SQL Server 2022 Docker Image
We put the T-SQL Code to restore the database into a file restore.sql
:
RESTORE DATABASE WideWorldImporters
FROM DISK = "/tmp/backup/WideWorldImporters-Full.bak"
WITH
MOVE "WWI_Primary" TO "/var/opt/mssql/data/WideWorldImporters.mdf",
MOVE "WWI_Userdata" TO "/var/opt/mssql/data/WideWorldImporters_UserData.ndf",
MOVE "WWI_Log" TO "/var/opt/mssql/data/WideWorldImporters.ldf",
MOVE "WWI_InMemory_Data_1" TO "/var/opt/mssql/data/WideWorldImporters_InMemory_Data_1"
GO
After the database has been restored, we'll add a user wwi
with the db_datareader
and db_datawriter
roles assigned. In a file users.sql
we add:
USE [WideWorldImporters]
GO
CREATE LOGIN wwi WITH PASSWORD = N'340$Uuxwp7Mcxo7Khy'
GO
CREATE USER wwi FOR LOGIN wwi
GO
EXEC sp_addrolemember N'db_datareader', N'wwi'
GO
EXEC sp_addrolemember N'db_datawriter', N'wwi'
GO
These two files should be executed once the SQL Server is ready, so we put it into a shell script create-db.sh
. Instead
of complex logic, I just wait 30 seconds for the SQL Server to finish initialization:
#!/bin/bash
# Wait for SQL Server to complete
sleep 30s
# Run the setup script to create the DB and the schema in the DB
/opt/mssql-tools18/bin/sqlcmd -S localhost -C -U sa -P $MSSQL_SA_PASSWORD -d master -i restore.sql
/opt/mssql-tools18/bin/sqlcmd -S localhost -C -U sa -P $MSSQL_SA_PASSWORD -d master -i users.sql
In the entrypoint.sh
, which is the Entrypoint for the Docker image, we then run the create-db.sh
script in the background and spin up the SQL Server using the sqlservr
executable:
#!/bin/bash
# Start the script to restore the DB and user
/tmp/backup/create-db.sh &
/opt/mssql/bin/sqlservr
Now we can write the Dockerfile
, which installs the latest mssql-tools18
(including sqlcmd
) and copies
the scripts to the Docker image. Finally our entrypoint.sh
is set as the Entrypoint, so it starts with the
container.
FROM mcr.microsoft.com/mssql/server:2022-latest
WORKDIR /tmp/backup
# Install Packages, because built-in sqlcmd has a bug ...
USER root
RUN apt-get update \
&& ACCEPT_EULA=Y apt-get install mssql-tools18 unixodbc-dev -y \
&& wget -q https://github.com/Microsoft/sql-server-samples/releases/download/wide-world-importers-v1.0/WideWorldImporters-Full.bak
COPY restore.sql .
COPY users.sql .
COPY create-db.sh .
COPY entrypoint.sh .
ENTRYPOINT ["/bin/bash", "/tmp/backup/entrypoint.sh"]
version: '3.8'
networks:
wwi:
driver: bridge
services:
db:
container_name: wwi-sqlserver2022
build:
context: ./db/
dockerfile: Dockerfile
networks:
- wwi
ports:
- "1533:1433"
healthcheck:
test: /opt/mssql-tools18/bin/sqlcmd -S localhost -U sa -P "$$MSSQL_SA_PASSWORD" -C -Q "SELECT 1" || exit 1
interval: 10s
timeout: 3s
retries: 10
start_period: 10s
environment:
ACCEPT_EULA: "Y"
MSSQL_SA_PASSWORD: "StrongPassword@"
Conclusion
And that's it! Running a docker-compose up
will now build a Docker image with an SQL Server 2022 and the WideWorldImporters Backup restored.