We have been able to spin up an instance of SQL Server inside a container. We have seen that a rapid deployment is possible once the image and supporting files are available on the container host system. The general idea behind containers is that they can be quickly created and destroyed at will, allowing for rapid deployments and short-lived instances of applications (great for testing/development). Once we have these files available, creating and destroying an instance of SQL Server is almost instantaneous and we can begin treating our database service just like a simple web server—a stateless relational database processing service. However, just because the database engine now enjoys rapid deployment, we must consider the second part of the RDBMS equation: the data.

The data inside a database is very much not stateless. It is the very antithesis of stateless, in that the database is supposed to persist our data and data changes. We must therefore consider how this new technology can be used to allow deployment agility and continue to host our databases without data loss. To achieve this, we must instruct Docker to either attach an existing database or to restore a database from a network share. Either will need to be configured as an action after the container has been deployed. In an automated deployment pipeline, this sort of preparation step before testing/development can begin is normal in software development outside the SQL Server space and may be familiar to some readers. The idea behind this approach is that an automated deployment has a preparation phase, then the deployment/testing phase, and a final tear-down phase.

The simplest way to allow a database to be accessed and remain usable after the container has been destroyed is to map a folder from the container host into the container itself. We need a folder on our host machine that will store the data and log files, and this will be mapped into the container at creation time (this example will create a new container on a different port and map C:Databases on the host filesystem to C:Databases in the container):

docker run -d -p 12346:1433 -v C:Databases:C:Databases --env ACCEPT_EULA=Y --env sa_password=P4ssw0rd! --name MySQLContainer2 microsoft/mssql-server-windows-express

Upon creation of the container, we are then able to connect to the instance and create a database using the new persistent storage location:

USE [master];
GO

CREATE DATABASE [MyContainerDB]
ON PRIMARY
(NAME = N'MyContainerDB', FILENAME = N'C:DatabasesMyContainerDB.mdf')
LOG ON
(NAME = N'MyContainerDB_log', FILENAME = N'C:DatabasesMyContainerDB_log.ldf')
GO

USE [MyContainerDB];
GO

CREATE TABLE dbo.MyTestTable
(Id INT);
GO

INSERT INTO dbo.MyTestTable
(Id)
SELECT TOP 10 object_id FROM sys.objects
GO

SELECT * FROM dbo.MyTestTable;

Assuming that our test/development work is complete and we wish to keep the database for further processing but no longer need the container, we can destroy the container and be sure that the database files remain intact:

docker stop MySQLContainer2
docker rm MySQLContainer2
docker ps

The output of the preceding code is as follows:

We then see that the files remain on C: Databases and can be re-attached to another instance for further use. This is extremely important in those cases where the database is still needed after a container has been destroyed (for example, when an automated test has been run and the database is needed for further testing/development purposes).

We also have the option of building a custom image for our containers for cases when we need to do some more configuration before working with the container. We have something called a dockerfile. It stores a set of commands that the Docker system can execute in sequence to first take an image and then process customized commands, before completing the build process of the image. This will allow us to include more complicated setup/preparation steps (such as adding or creating databases, users, instance configurations, and so on) that are required in our build process.

The dockerfile is a text file with no extension and contains a specific set of commands. In the following example, we will include the steps to create an image, create a directory for a user database, and attach the previously created MyContainerDB to the SQL Server instance we are starting up. However, this attach can easily be replaced with any other conceivable command (whether specifically for SQL Server or for the underlying operating system).

The contents of the dockerfile are as follows:

• FROM microsoft/mssql-server-windows-express
• The FROM statement defines which base image should be used
• Run PowerShell Command (the new-item -path c: -name Databases -itemtype directory)

The RUN statement tells Docker that we wish to run a specific command, in this case, a Powershell command to create a directory:

COPY MyContainerDB.mdf C:Databases
COPY MyContainerDB_log.ldf C:Databases

The COPY command tells Docker to copy the two database files from the root of the Docker execution to the directory we just created inside the image:

ENV sa_password=P$ssw0rd!
ENV ACCEPT_EULA=Y
ENV attach_dbs="[{'dbName':'MyContainerDB','dbFiles':['C:DatabasesMyContainerDB.mdf','C:DatabasesMyContainerDB_log.ldf']}]"

The three ENV commands are environment-specific commands that the SQL Server image requires to set the sa password, accept the EULA, and attach the newly copied files as a database on the container instance.

We build the image using docker build and name it with -t [imagename]. The . tells Docker to use the dockerfile in the current directory for customization:

docker build -t testimage 

The output of the preceding code is as follows:

We are then able to create a container using the new image, then retrieve the IP address, connect to the instance, and see our attached database:

docker run -d -p 12346:1433 --name testcontainer testimage

The following screenshot shows the execution of the preceding code:

This provides us with maximum flexibility when deploying our images and containers. We are able to pull the official release version of SQL Server from Microsoft, alter the contents to meet our requirements, and deploy containers based on this modified base image.

From a developer's standpoint, using containers with SQL Servers allows us to be more flexible with our development process—we are no longer required to involve the DBA or SysOps team if we want to spin up a new instance. This allows us to test new versions and new features without the traditionally drawn-out process of requesting an instance; at the same time we incorporate business-specific configuration changes on top of standardized images supplied by Microsoft. A repeatable deployment process is possible with a minimal overhead.