How to import a .bacpac file into a docker container running SQL Server 2019
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 usemssql
.mkdir /tmp/db
: Create directory which we will store the .bacpac file inchown -R mssql /tmp/db
: Change the ownership of folder that will have the .bacpac file to usemssql
. -
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 calledmssql
is utilized.It is why we first switch to use the
root
user in order to useapt-get
, then switch over tomssql
. -
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, whereDNAME
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 :-
From the command line with docker run command
-
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