How to import a .bacpac file into a docker container running SQL Server 2019

Ehab Mowafak Okal

Apr 15, 2021 — 7 min read

In this post I'll explain how to setup a dockerfile running SQL server 2019, and how to import and deploy the .bacpac file to the SQL server by using SQLpackage.exe to automate the process. We'll run the container with docker-compose, in order to eliminate the hassle of supplying volumes, ports and environment variables when running the container each time :)

Setting up the Dockerfile

Applying all the mentioned steps below, will result into the following dockerfile:

# Base image 
FROM mcr.microsoft.com/mssql/server:2019-CU2-ubuntu-16.04

# Elevate to root to install required packages
USER  root

# Get and install unzip
RUN  apt-get  update  &&  apt-get  install  unzip  -y

# Install SQLPackage for Linux and make it executable
RUN  wget  -progress=bar:force  -q  -O  sqlpackage.zip  https://go.microsoft.com/fwlink/?linkid=2113331 \
&&  unzip  -qq  sqlpackage.zip  -d  /opt/sqlpackage \
&&  chmod  +x  /opt/sqlpackage/sqlpackage \
&&  chown  -R  mssql  /opt/sqlpackage \
&&  mkdir  /tmp/db \
&&  chown  -R  mssql  /tmp/db

# Lower the privilege
USER mssql

# Add the BACPAC to the image
COPY site.bacpac /tmp/db/db.bacpac

# Configure external build arguments to allow configurability.
ARG  DBNAME
ARG  PASSWORD
ARG  ACCEPT_EULA

# Configure the required environmental variables
ENV  ACCEPT_EULA=$ACCEPT_EULA
ENV  SA_PASSWORD=$PASSWORD

# Launch SQL Server, confirm startup is complete, deploy the BACPAC, then terminate SQL Server.
# See https://stackoverflow.com/a/51589787/488695
RUN  (  /opt/mssql/bin/sqlservr  &  )  |  grep  -q  "Service  Broker  manager  has  started" \
&&  /opt/sqlpackage/sqlpackage  /a:Import  /tsn:.  /tdn:${DBNAME}  /tu:sa  /tp:$SA_PASSWORD  /sf:/tmp/db/db.bacpac \
&&  rm  -r  /tmp/db \
&&  pkill  sqlservr \
&&  rm  -r  /opt/sqlpackage
  
#Run the SQL Server
CMD  /opt/mssql/bin/sqlservr

The first step in achieving our goal is to create a dockerfile which will use SQL Server 2019 as it's base image, specifically this version : FROM mcr.microsoft.com/mssql/server:2019-CU2-ubuntu-16.04 . It's a good practice to use specific versions of docker images than using the latest, which could introduce a game breaking change.
The second step in achieving our goal is to elevate the permission level by switching to root user , in order to run apt-get to install packages, adjust the ownership of folders or perform other operations which required elevated user privileges.
```
# Elevate to root to install required packages
USER root
```
The first package we will get and install is unzip, since SQLpackage.exe is downloaded as a zip. And we need to have unzip installed and ready when downloading and extracting SQLpackage.exe zip folder.
```
# get and install unzip
RUN  apt-get  update  &&  apt-get  install  unzip  -y
```
The second package we get, extract and install as mentioned above is SQLpackage.exe:
```
# Install SQLPackage for Linux and make it executable
RUN  wget  -progress=bar:force  -q  -O  sqlpackage.zip  https://go.microsoft.com/fwlink/?linkid=2113331 \
&&  unzip  -qq  sqlpackage.zip  -d  /opt/sqlpackage \
&&  chmod  +x  /opt/sqlpackage/sqlpackage \
&&  chown  -R  mssql  /opt/sqlpackage \
&&  mkdir  /tmp/db \
&&  chown  -R  mssql  /tmp/db
```
Note: We change the ownership of the folders to enable the removal of SQLpackage and the imported bacpac in the end of the process. In order to do that it's important to ensure that mssql user is the owner of the folders.

chown -R mssql /opt/sqlpackage : Change the ownership of SQLPackage to use mssql.

mkdir /tmp/db : Create directory which we will store the .bacpac file in

chown -R mssql /tmp/db : Change the ownership of folder that will have the .bacpac file to use mssql.
We switch to a lower privileged user with less permissions called : mssql
```
# Lower the privilege
USER mssql
```
The reason for lowering to the less lower privileged user, in this case to the user mssql. Is that the base image for SQL Server 2019 which we use to build upon for our container, is built more secure than it's 2017 version.

With one of the main security improvement being that 2019 version no longer uses the root user to build the image, instead a less privileged user called mssql is utilized.

It is why we first switch to use the root user in order to use apt-get , then switch over to mssql.
We copy the .bacpac file from our local system into the container.
```
# Add the BACPAC to the image
COPY site.bacpac /tmp/db/db.bacpac
```
Note: Remember that the user context is now mssql.
Since we are going to run the docker container (Dockerfile) using docker-compose, we need to "open up" the Dockerfile, and pass it some variables which comes from the docker-compose.yml file which in turn gets the variables from the .env file.

The reason for this setup is to have the .env file be the single source of truth, in regards to the variables which is being used by both the dockerfile and docker-compose file.

.env file is a simple file containing key value pair of variables to be used while working in a certain environment, below is an example of what our .env file will contain:
```
MSSQL_PASSWORD=StrongPassw0rd!
MSSQL_PORT=1433
MSSQL_ACCEPT_EULA=Y
MSSQL_DATABASENAME=Testdb
MSSQL_DATA_DIR=/var/opt/sqlserver/data
MSSQL_LOG_DIR=/var/opt/sqlserver/log
MSSQL_BACKUP_DIR=/var/opt/sqlserver/backup
```
We'll be focusing on the four top variables which will be used for this post, while the rest the variables are used in order to setup persistent storage of the database by using named volumes and volume mounting.

You can learn more about how to setup a SQL Server 2019 docker container with persistent storage by following this post

In order for the dockerfile to get the variables from the docker-compose file, we'll be using ARG values:
```
ARG some_variable_name
```
You can also give the ARG value a default value:
```
# with a hard-coded default:
ARG some_variable_name=default_value
```
Note: If you don't provide a value and the ARG doesn't have a default value, you'll get an error.

For our example well set the following ARG's :
```
# Configure external build arguments to allow configurability.
ARG  DBNAME
ARG  PASSWORD
ARG  ACCEPT_EULA
```
ARG DBNAME is used when calling SQLpackage.exe import command, where DNAME is passed to name the newly imported database.

ARG PASSWORD is used for both setting the environment variable (ENV) for SQL server, and is used when calling SQLpackage.exe import command.

ARG ACCEPT_EULA is used as a environment variable (ENV) for SQL server.
Next step we setup the required environment variables in order to run SQL Server 2019:
```
# Configure the required environmental variables
ENV  ACCEPT_EULA=$ACCEPT_EULA
ENV  SA_PASSWORD=$PASSWORD
```
As you can see we are using the previously defined ARG's values to set the environment variables value, this is done by adding a dollar sign + the name of the ARG variable: $PASSWORD .

This practice is called using dynamic on-build env values , which means:
Once the image is built, you can launch the container and provide environment variables in two different ways :
1. From the command line with docker run command
2. Using docker-compose.yml file

The final part of dockerfile is to run SQL Server, then use SQLpackage.exe import command and pass it the previously set ARG's values.

After the import of the bacpac is done, we remove the tmp/db folder which the bacpac is located.

We stop the SQL Server and remove the SQLpackage.exe , by removing /opt/sqlpackage.

Then we run the SQL Server again.

# Launch SQL Server, confirm startup is complete, deploy the BACPAC, then terminate SQL Server.
# See https://stackoverflow.com/a/51589787/488695
RUN  (  /opt/mssql/bin/sqlservr  &  )  |  grep  -q  "Service  Broker  manager  has  started" \
&&  /opt/sqlpackage/sqlpackage  /a:Import  /tsn:.  /tdn:${DBNAME}  /tu:sa  /tp:$SA_PASSWORD  /sf:/tmp/db/db.bacpac \
&&  rm  -r  /tmp/db \
&&  pkill  sqlservr \
&&  rm  -r  /opt/sqlpackage
  
#Run the SQL Server
CMD  /opt/mssql/bin/sqlservr

Note: The reason for the user switch is that commands are executed in the context of the current user account, and if we would wait after the run command is done to switch to the mssql user. Then mssql won't be able to read, write or execute anything in the folders, since they are created with root user as the context running the commands.

Therefor it's an important best practice to observer and apply the correct privileges and permissions when creating and calling commands in a dockerfile.

Docker-compose.yml

As mentioned in the beginning of this post we'll be running the docker container, by building and running it from a docker-compose file.

Below is and example of how to setup a docker-compose file, a more detailed explanation is found in the post : SQL Server 2019 docker container with persistent storage post

version: '3.7'  
services:
	mssql-bacpac:
		container_name: mssql-bacpac
	build:
		context: ./Database
		dockerfile: Dockerfile
		args:
		  DBNAME: ${DATABASENAME}
		  PASSWORD: ${MSSQL_PASSWORD}
		  ACCEPT_EULA : ${MSSQL_ACCEPT_EULA}
	env_file:
		- .env
	ports:
		- ${MSSQL_PORT:-1433}:1433
	volumes:
		- sqlsystem:/var/opt/mssql/
		- sqldata:/var/opt/sqlserver/data
		- sqllog:/var/opt/sqlserver/log
		- sqlbackup:/var/opt/sqlserver/backup
	healthcheck:
		test: ['CMD', '/opt/mssql-tools/bin/sqlcmd', '-U', 'sa', '-P', '${MSSQL_PASSWORD}', '-Q', 'select 1']
		interval: 10s
		timeout: 3s
		retries: 2
			  
volumes:
	sqlsystem:
	sqldata:
	sqllog:
	sqlbackup:

As you can see we are build the previously setup dockerfile , which in this example is located inside the folder called Database:

	build:
		context: ./Database
		dockerfile: Dockerfile

Inside the build we also pass in the environment variables which will be used inside the dockerfile when building it, this is done by adding args: + the variable names from the .env file :

args:
  DBNAME: ${DATABASENAME}
  PASSWORD: ${MSSQL_PASSWORD}
  ACCEPT_EULA : ${MSSQL_ACCEPT_EULA}

.env file

Here's the content of the .env file used for this example:

MSSQL_PASSWORD=StrongPassw0rd!
MSSQL_PORT=1433
MSSQL_ACCEPT_EULA=Y
MSSQL_DATABASENAME=Testdb
MSSQL_DATA_DIR=/var/opt/sqlserver/data
MSSQL_LOG_DIR=/var/opt/sqlserver/log
MSSQL_BACKUP_DIR=/var/opt/sqlserver/backup

Running the container

Go to your terminal where you docker-compose.yml file is located and run docker-compose up --build, which will build the container, apply the bacpac , remove the bacpac file and SQLpackage when the import is done and then runs the SQL server :)

And in the future you just run docker-compose up, which will run a cached version of the docker container.

Written: 2021-03-20

How to import a .bacpac file into a docker container running SQL Server 2019

Ehab Mowafak Okal

Setting up the Dockerfile

Docker-compose.yml

.env file

Running the container

Read more

What I have learned from creating a Pdf AI reading SaaS

Dockerizing a .NET Core application with dotnet watch

How to setup WSL, and how to use it