Build custom SQL Server 2017 Linux Docker image

Background

In the earlier series of posts on continuous deployment of multi-container apps, I had used two containers for ASP.Net Core MVC web site and ASP.Net Core Web API. This post is a sort of continuation. In the earlier version we were using in memory database to store the data. This approach is useful in a proof of concept or a quick demo application. In an enterprise scenario we would use some sort  of persistent data store to store the data. It is very easy to containerize the stateless services and web front ends. When we move towards stateful processing, things start to get interesting.

Containers by their very nature are supposed to be ephemeral. This means that containers can be stopped and destroyed and a new one built and put in place with minimum setup and configuration. If we destroy the container and replace it with a new one what happens to the data stored inside the container? Can we use containers to run database related processes? How can the stateless containers communicate with stateful container? We will try to answer these kind of questions as part of this and future posts.

In this post we will perform following activities

• Download Docker image of SQL Server 2017 running on Linux
• Create new database inside the container and initialize data
• Query data residing inside the container

Download Docker image of SQL Server 2017 running on Linux

I would like to use the latest image of SQL Server 2017. Microsoft provides multi-architecture images of SQL Server 2017. I will use the mssql-server-linux image. This is smaller in size compared to the windows image. The linux image compressed size is 450 MB compared to that of windows developer version which is about 6 GB. I could have used any other database related container like MySQL or Postgress. Personally I am very much familiar with Microsoft ecosystem, hence I chose an image of SQL Server 2017. This also gives me an opportunity to test the features of SQL Server running on Linux without having the need to provision a Linux VM.

Like all other images that we pull from container repositories, we will start by pulling the image named mssql-server-linux:2017-latest. The official documentation from Microsoft gives a good introduction about how to get started with this image. What I am more interested is to make use of this image in my multi-container application.

Create new database inside the container and initialize data

I will reuse the codebase that is used in other parts of my blog series. We will build a custom image based on the mssql-server-linux image which will have a database named TechTalksDB. We will start off by declaring a Dockerfile which will define the steps to build the custom image. I have created a new folder named Database to store all the files related to database operations under this folder.

We can see that the base image used is microsoft/mssql-server-linux:2017-latest. This image has a pre-requisite of setting 3 environment variables

1. ACCEPT-EULA
2. SA_PASSWORD
3. MSSQL_PID

I am setting these values to defaults as Y, January2018 and Developer respectively. Please note that this is not the best way of managing the password. Never use this approach in production system. This is purely for demo purpose. The reason I am providing default values is that it helps me from passing these values every time. This approach also gives me the flexibility that I can override it if required using environment variables at runtime.

We define a working directory as src. Next we copy 3 files into the working directory. These files are used to create the initial state of the database. We change the permissions on the files to mark the shell scripts as executable. Finally we run the entrypoint.sh script.

Entrypoint script starts the sqlservr process residing inside the /opt/mssql/bin directory. This is the default process run by the base image. Along with this process we run the setup-database shell script.

The setup-database script is a sort of intelligent part of the whole process. It takes into account the fact that the sqlservr process takes few seconds to start. It sleeps for 10 seconds and then runs the initialize-database.sql script. Please note that we use the sqlcmd command line tool to run the intialize-database sql script.

The sql script is pretty straightforward. It creates a database named TechTalksDB. Add 3 tables Categories, TechTalk and KeyValue. It also add few records into each of these tables.

Query data residing inside the container

We defined all the steps in the Dockerfile for building the image. Whoever creates a container based on this image will now have the TechTalksDB initialized with default values. The process of building and running the image is very very simple. Run the docker build command

docker build –t nileshgule/sqldb .

We have tagged the custom image as nileshgule/sqldb. If everything goes fine, we should see an output as shown below

Lets run this newly built image using the docker run command.

docker run -it -p 1433:1433 \
--name sql2017 \
nileshgule/sqldb

Thats all and we have a full fledge SQL Server 2017 running with a database initialized with values as can be seen from the screenshot below.

For simplicity I have shown only the last part of the output. But the whole process takes about less than a minute to fire up a brand new container with the data initialized. Isn’t that great? When was the last time you were able to get a fresh copy of database running on a SQL Server instance within a minute?

We can connect to the container and query the data from KeyValue table using the docker exec command as

docker exec -it sql2017 /opt/mssql-tools/bin/sqlcmd \
-S localhost \
-U SA \
-P "January2018"

This command starts the sqlcmd shell by connecting to the container named sql2017 which we created using the docker run command earlier. We can then issue the queries against any database within the container. In the below screenshot I am selecting all the records from the KeyValue table.

We can see that all the records inserted using initialize-database.sql script are available in the output.

Conclusion

Docker really makes it very simple to develop and test applications using containers. Here we saw how to initialize a database inside SQL Server 2017 instance running on Linux. There was a small change I had to do to the Docker demon settings in order to run the SQL Server 2017 docker image. This is due to the pre-requisite that at least 2 GB of RAM is required.

We saw how Docekrfile can be used to describe the process of building a custom image. The image had a new database created along with some static data. The best part was the speed at which all these changes were completed. As I said before, I can’t recollect when was the last time I was able to get up and running with brand new database on a completely fresh copy of SQL Server. Mind you the process of installation itself would take few hours. With docker it is matter of minutes and not hours. For the very same reason, I believe you should move to docker if you have not done so far.

The complete source code for the application used during this post is available on Github. I have also pushed the nileshgule/sqldb docker image to DockerHub. Feel free to download it and play around with it. In the future post we will see how to connect ASP.Net Core Web API to the SQL Server database running inside the Docker container. We will also see how to persist data across container restarts. All that and many more related topics will come very soon. Until next time code with passion and strive for excellence. 

Share: