Using Docker (Compose) to host SQL Server AdventureWorks DB

Recently, I am learning the SQL Server performance tunning and the book I am using is using the AdventureWorks database as the sample database. And I don’t want to install the SQL Server Engine in my Surface, not even the Express version. So I am thinking about using the Docker to host my database. After some investigations and tries, I figured it out.

Below is the folder structure.

folder structure

Now, let me go through some steps to build your own AdventureWorks database on your local computer.

Download the DB data file.

Microsoft provided AdventureWorks databases for a long time. The latest one is AdventureWorks2019. Here is the official document .

You can choose the bak file you prefer to use.

Prepare the DB restore scripts

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11


USE [master]
GO

-- Use this command to restore the original MDF and LDF file.
RESTORE FILELISTONLY FROM DISK = "/var/opt/mssql/backup/AdventureWorks2019.bak"

-- Use files generated by the last step to create the database.
-- For the file path, you can see the output message from the first command.
RESTORE DATABASE AdventureWorks2019 FROM DISK = "/var/opt/mssql/backup/AdventureWorks2019.bak" WITH MOVE "AdventureWorks2017" TO "/var/opt/mssql/data/AdventureWorks2019.mdf", MOVE "AdventureWorks2017_log" TO "/var/opt/mssql/data/AdventureWorks2019.ndf"

GO

Create docker file for the AdventureWorks DB

Here is the init.sh file which will be used as the COMMAND in the docker compose yaml file.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21


#!/bin/bash

isReady=0

while [ $isReady -eq 0 ]
do
    echo "Try again"

    /opt/mssql-tools/bin/sqlcmd -S adventure-sql -U sa -P $SA_PASSWORD -d master -Q "select 1" > /dev/null

    if (( $? == 0 ))
    then
        let isReady=1

        echo "Success connect"
    fi

    echo "Exit code is " $?
done

/opt/mssql-tools/bin/sqlcmd -S adventure-sql -U sa -P $SA_PASSWORD -d master -i /app/init.sql

Since the container which host the SQL Server and the container which will be used to restore the DB will starts simultaneously, restoring the database will be failed until the SQL Server container finishes the initialization. So I add the retry strategy to the shell file.

Here is the dockerfile content.

1
2
3
4
5
6
7
8
9


FROM mcr.microsoft.com/mssql-tools:latest

USER root
COPY ["Support/db/*", "/app/"]
COPY ["Source/*", "/var/opt/mssql/backup/"]

RUN find /app -type f -exec sed -i "s/\r//" {} \;
# Remember to add the permission.
RUN chmod -R ugo+x /app/*.sh

Create the docker compose YAML

As you can see, there are two containers aforementioned in the last step. One for the SQL Server itself and another one is for the restoration.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27


version: "3.8"

services:
  adventure-sql:
    image: mcr.microsoft.com/mssql/server:2019-latest
    environment:
      - ACCEPT_EULA=Y
      - 'SA_PASSWORD=yourStrong(!)Password'
    ports:
      - 1433:1433
    volumes:
      - shared-data:/var/opt/mssql/backup

  adventure-works:
    depends_on:
        - adventure-sql
    build:
      context: .
      dockerfile: ./Support/mssql.dockerfile
    environment:
      - 'SA_PASSWORD=yourStrong(!)Password'
    command: /app/init.sh
    volumes:
      - shared-data:/var/opt/mssql/backup
    
volumes:
  shared-data:

Running snapshot

Now, all you have to do is running below commands in your PowerShell (Windows) or Shell (Linux/MAC).

1
2
3


cd \the-root-folder-of-this-project\

docker-compose up

BTW, if you see below error message. Please check whether port 1433 has been taken or not. You can check this web page to learn more.

running error