This post follows on from SQL Server & Containers – Part 1 and will go through how to build custom container images.

Since Part 1 came out Microsoft has released SQL Server vNext which is available in the Docker repository. I used the SQL 2016 Express image in Part 1 but that has now been deprecated so for this part we’ll use one of the new images.

To see what SQL Server images are available for you to download and run as containers, you can run:-

docker search microsoft/mssql-server

So let’s crack on and build a container.

One word before we start however, this post assumes that you’ve installed the docker engine on a Windows Server 2016 installation as detailed in Part 1. If you haven’t installed the Docker engine, go back to Part 1 and follow the instructions, we’ll meet you here

As before to create a container, we first need to pull an image from the respository. Let’s go for the vNext image.

Open an admin powershell prompt and run:-

docker pull microsoft/mssql-server-windows

And now we can run a container from the image: –

docker run -d -p 15888:1433 -e sa_password=Testing11 -e ACCEPT_EULA=Y microsoft/mssql-server-windows

We now have a running SQL Server vNext container. Note that the syntax to run the container has changed slightly from the code we ran in Part 1, the difference being
-e sa_password=Testing -e ACCEPT_EULA=Y instead of –env sa_password=Testing

Details of commands needed to run containers are documented in the Docker Hub which we’ll explore further in Part 3.

What we are going to do now is create a database within that container, then stop the container and create a new image from it.

So connect to the container (server IP address and the port we specified in the run command) and run the following SQL scripts:-

CREATE DATABASE [TESTDB];
GO
USE [TESTDB];
GO
CREATE TABLE dbo.DummyData
(PKID INT IDENTITY(1,1) PRIMARY KEY,
 ColA VARCHAR(10),
 ColB VARCHAR(10),
 ColC DATETIME);
GO
INSERT INTO dbo.DummyData
(ColA,ColB,ColC)
VALUES
(REPLICATE('A',10),REPLICATE('B',10),GETUTCDATE());
GO 10

This container now has a custom database in it with some random data. What we’re going to do now is stop that container and create a new image from it, so first run:-

docker stop b71

N.B. – b71 is the first three digits of my container ID. You’ll need to substitute for your container’s ID

Now that the container is stopped we can create a new image: –

docker commit b71 testimage

This will create a new image from our container called testimage which we can view by running:-

docker images

Great stuff, we’ve created a custom docker image! What’s really cool now is that we can create containers from that image, exactly the same as we did with the generic original image from the repository:-

docker run -d -p 15666:1433 -e sa_password=Testing22 -e ACCEPT_EULA=Y testimage

Once the command has executed you can connect remotely via SSMS using the server name and the port we specified above. The database that we created in the original image will be there, along with the data that we entered!

This is where containers start to come into their own in my opinion. You can build your own custom images and quickly spin up multiple instances that already have all the databases that you require!

Hmmm, you say. That’s great and all but are those custom images only available on the server that I’ve created them on?

Well, yes and no, but that’s something that’ll be covered in Part Three.