Data DevOps: Deploying SQL Server DACPACs to Docker

In order to truly optimize the workflow and create a culture of DevOps, we need to be able to quickly build and deploy databases. To automate this process, we need to rely on improving our ability to utilize infrastructure as code. In this post, I’m going to show you how to use sqlpackage to automate the process of deploying a DACPAC to SQL Server 2017 on a Linux container.

What is a DACPAC?

If you’re not familiar with the terms, DACPACs are a way of encapsulating the definition of a SQL Server database for automated deployment. Essentially, it’s a ZIP file (or, more specifically, an Open Packaging Convention document) which contains the details of the schema and an optional set of pre-deployment and post-deployment scripts to be executed. The benefit of this model is that it uses state-based deployments to update the schema of the database. Think of this as desired state configuration for your database schema. Rather than manually creating migration scripts, you can instead focus on defining the end state and allowing DACFx (Database Application Framework) to determine how to best apply the migration.

An important related concept is the BACPAC. This is a DACPAC which contains the exported binary data dump from one or more tables. It is a mechanism for transferring both the database and schema from a given database to another system. BACPACs can be imported to a server in order to quickly recreate the database on a new server. On Azure, this has been the primary mechanism for backing up and restoring cloud-hosted SQL Server databases, replacing the traditional BAK files.

Why Docker?

Docker provides us with an environment that is completely encapsulated. It offers us an infrastructure-as-code approach to building, testing, and deploying environments which will execute consistently across systems. While Docker is not always the best approach for hosting databases (as Blaize explains here), it can provide a powerful way to create working test environments.

Creating the Docker Image

To create our image, we’re going to need to craft a small Dockerfile that we can use which configures our environment for us. For this example, we’re going to use the SQL Server 2017 image documented here.

Two environmental variables MUST be set – either in the Dockerfile or passed on the command line – in order to be able to run SQL Server in a container.

• ACCEPT_EULA : Must be set to Y to indicate that you are accepting the EULA. If this value is not set, SQL Server will fail to launch.
• SA_PASSWORD : This is the pasword for the SA account. This value must be a strong password. This means the password must be at least 8 characters long and contain three of the four character types: uppercase letters, lowercase letters, digits, or symbols.

With this in mind, let’s look at the resulting Dockerfile:

 1FROM mcr.microsoft.com/mssql/server:2017-latest
 2
 3# Install Unzip
 4RUN apt-get update \
 5    && apt-get install unzip -y
 6
 7# Install SQLPackage for Linux and make it executable
 8RUN wget -progress=bar:force -q -O sqlpackage.zip https://go.microsoft.com/fwlink/?linkid=873926 \
 9    && unzip -qq sqlpackage.zip -d /opt/sqlpackage \
10    && chmod +x /opt/sqlpackage/sqlpackage
11
12# Add the DACPAC to the image
13COPY Northwind.dacpac /tmp/db.dacpac
14
15# Configure external build arguments to allow configurability.
16ARG DBNAME=Database
17ARG PASSWORD
18
19# Configure the required environmental variables
20ENV ACCEPT_EULA=Y
21ENV SA_PASSWORD=$PASSWORD
22
23# Launch SQL Server, confirm startup is complete, deploy the DACPAC, then terminate SQL Server.
24# See https://stackoverflow.com/a/51589787/488695
25RUN ( /opt/mssql/bin/sqlservr & ) | grep -q "Service Broker manager has started" \
26    && /opt/sqlpackage/sqlpackage /a:Publish /tsn:. /tdn:${DBNAME} /tu:sa /tp:$SA_PASSWORD /sf:/tmp/db.dacpac \
27    && rm /tmp/db.dacpac \
28    && pkill sqlservr

In this example, we have a DACPAC called Northwind.dacpac which exists in the same folder as Dockerfile. We can build the image by executing this command in the same folder:

1docker build --build-arg PASSWORD=YourS3cureP@ass -t northwind:1.0 .

To execute our database environment, we simply need to call docker run with a few arguments

1docker run -p 1433:1433 --name sqldb -d northwind:1.0

You can easily create more complex environments using Kubernetes or Docker Compose. Environments of this kind are ideal for automatic test execution and cleanup. This approach ensures that the database definition in the DACPAC is working as intended. Of course, you can also use this environment for integration testing.

This is just an introduction to the approach. Feel free to explore the concepts further … and Happy DevOp’ing!