carloscastilla - Fotolia

Manage Learn to apply best practices and optimize your operations.

Automate SQL Server Docker container configuration via Dockerfiles

As many in IT have learned the hard way, building software containers for systems like SQL Server can be tough. That's where Dockerfiles come in to help automate the process.

One of the IT management possibilities enabled by software containers is using them to deploy back-end database systems, such as SQL Server. Using containers, you can deploy a new database instance in a matter of seconds, making your development and operations more agile and efficient.

Previously, I demonstrated how to install Windows Server 2016's container support and use a prebuilt Docker image from Microsoft to run SQL Server 2016 Express Edition in a container. Then, I showed how to build a customized SQL Server image using the Windows Server Core image in conjunction with a command-line installation of SQL Server 2016 Standard Edition. That allows you to create SQL Server images configured with the features you specify, but it includes a number of manual steps which make it time-consuming and potentially prone to errors for repeated operations.

Fortunately, a tool called Dockerfile automates the task of building containers. Read on to learn how you can use Dockerfiles to streamline the Docker container configuration process for SQL Server.

What are Dockerfiles?

Dockerfiles are fairly straightforward to use, although their specific commands do require some familiarity. At its essence, a Dockerfile is simply a text file without a file extension that contains the build commands to create Docker images. In turn, these images contain the software layers required to run an application or a service, such as SQL Server. Using the Docker build command with a Dockerfile will create an image based on the specifications in the Dockerfile.

You can think of a Dockerfile as being like a code compiler for a Docker image. Dockerfiles enable you to quickly and easily recreate a particular image. In addition, making changes to a Dockerfile enables you create derivative images that are based on the initial Dockerfile specifications.

Inside a Dockerfile for SQL Server

Figure 1 shows a sample Dockerfile for building a SQL Server 2016 Express image.

Build SQL Server Containers with Dockerfile
Figure 1: A Dockerfile for building SQL Server containers

In Dockerfiles, comment lines begin with the # character. They can be used to describe what commands will do. In this example, I started by documenting the Docker build and Docker run commands to use with the image; I discuss those commands more in the next section.

From there, the FROM command line identifies the base image that will be used to build the container; in this case, Microsoft's image containing Windows Server Core and a preinstalled instance of SQL Server 2016 Express Edition. If the image isn't present in a local repository, it will be downloaded from the Docker Hub, a repository site operated by Docker Inc., the company behind the open source Docker technology.

Once the image is instantiated, RUN statements can be used to initiate commands. If the image is a Windows image, as it is here, it must be some type of Windows command, such as a command shell or PowerShell command. If the image is a Linux image, the commands would typically be Bash commands.

In Figure 1, PowerShell is used to create a directory in the image named SQLData. It's important to realize that the commands in the RUN statement will run in the container image and not in the host system. These commands in the Dockerfile are executed in the container image and they enable you to build up the container image with the attributes that you want.

Next, two COPY commands are used to copy files from the Dockerfile's installation directory to the newly created SQLData directory within the image. The first command copies the sample AdventureWorks 2014 database's AdventureWorks2014_Data.mdf file, and the second line copies the AdventureWorks2014_Log.ldf file. Note that the COPY commands can include both the * and ? wildcard characters, where the ? is used to substitute a single character. For instance, if you wanted to copy multiple MDF and LDF files to the c:\ directory, you could use the following commands:

COPY *.mdf C:/

COPY *.ldf C:/

The example in the Dockerfile copies the data and log files that comprise the AdventureWorks2014 database. This allows the AdventureWorks 2014 database to be automatically attached when the container starts.

Finally, the EXPOSE command is used to open up TCP port 1433 on the host system; that's the default port used by SQL Server. Port 1433 is the default port used by SQL Server. Opening port 1433 enables the SQL Server instance in the container to be remotely accessed using SQL Server Management Studio, as well as other applications and management tools from network-attached systems.

Using a Dockerfile to build an image

While this example is relatively simple, actual Dockerfiles can be much more extensive, with a lengthy set of specifications for a customized Docker container configuration. You use Docker build commands to create new images using those specifications.

To do so, navigate to the directory containing the Dockerfile and the other installation files, like the AdventureWorks2014_Data.mdf and the AdventureWorks2014_Log.ldf files, and then execute a command like this one:

cd c:\sqlexpressbuild

docker build -t sqlexpress .

In this example, you can see that the -t (or tag) parameter is used to name the new image sqlexpress. The value of the . indicates that the current directory will be used for the build operation. After the new image has been created, you can use the Docker images command to list the available images, as shown in Figure 2.

Browsing Docker images in a repository
Figure 2: A listing of available Docker images in a local repository

At this point, you're ready to spin up multiple containers based on the new standardized image. You can start a container via the following Docker Run command:

docker run -d -e sa_password=9a55w0rd#1 -e ACCEPT_EULA=Y -e attach_dbs="[{'dbName':'AdventureWorks2014','dbFiles':['C:\\SQLData\\AdventureWorks2014_Data.mdf','C:\\SQLData\\AdventureWorks2014_Log.ldf']}]" sqlexpress

The command uses the -d parameter to indicate the container will run detached -- meaning it will run in the background and it won't have a UI -- no window will open up when the container starts. Instead, it will run as a background process. The -e sa_password parameter provides an initial password for the built-in System Administrator account. The -e ACCEPT_EULA=Y parameter is required to accept the software license agreement and to start the SQL Server Express container. The -e attach_dbs parameter is used to attach the AdventureWorks 2014 database whose files were copied to the container as part of the Dockerfile setup process.

Using Dockerfiles can help automate and standardize your Docker container configuration and deployment processes for SQL Server. For developers, using SQL Server containers is great because they enable you to be running new SQL Server instances in seconds, complete with databases, without needing to wait for any lengthy install or restore processes.

Next Steps

Operating SQL Server on Docker may not be so bad

Making sense of SQL Server 2016 licensing

Container management software for SQL Server

Dig Deeper on Microsoft SQL Server Consolidation and Virtualization