Build custom SQL Server 2017 Linux Docker image

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

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

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

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

Image for post
Image for post

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.

Image for post
Image for post

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.

Image for post
Image for post

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

Conclusion

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.

Originally published at www.handsonarchitect.com.

Passionate about software development lives with a motto of Code with Passion and Strive for Excellence. Actively blogs at www.HandsOnArchitect.com.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store