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. It also has the benefit of executing 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:
FROM mcr.microsoft.com/mssql/server:2017-latest
# 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=873926
&& unzip -qq sqlpackage.zip -d /opt/sqlpackage
&& chmod +x /opt/sqlpackage/sqlpackage
# Add the DACPAC to the image
COPY Northwind.dacpac /tmp/db.dacpac
# Configure external build arguments to allow configurability.
ARG DBNAME=Database
ARG PASSWORD
# Configure the required environmental variables
ENV ACCEPT_EULA=Y
ENV SA_PASSWORD=$PASSWORD
# Launch SQL Server, confirm startup is complete, deploy the DACPAC, 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:Publish /tsn:. /tdn:${DBNAME} /tu:sa /tp:$SA_PASSWORD /sf:/tmp/db.dacpac
&& rm /tmp/db.dacpac
&& 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:
docker 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
docker 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!