Running SQL Server vNext on a Linux machine

The aim of this article is to present how you can quickly set up a simple development environment based on a Linux machine and the SQL Server vNext also officially known as SQL Server 2017. For this purpose I would like to prepare a customized Docker image and after this is completed I will try to establish connection with SQL Server inside the container as well as from the outside. I can acknowledge this process to be a success when a new database is created on the server from a previously prepared script file, what seems to be a real-world scenario.

Motivation

First of all, I’d like to explain why it’s worth to set up SQL Server vNext on Linux machine with the Docker. Introduction of the cross-platform .NET Core was big (and great) news for software developers community. Especially, for those who does bespoke software development using Microsoft stack. Since then the interest in .NET Core framework is consistently growing and I may predict that it will become one of the leading software development technology in a near future. The SQL Server vNext is a part of the idea of making the Microsoft products cross-platform, thus also more available and flexible.

The reason of using Docker seems to be quite similar and strictly related to the motivation behind the setting up the SQL Server vNext, it simply gives an opportunity to fast creation of modular and scalable applications in a cross-platform way. The subtle difference between Docker containers and the virtual machines results in its great performance. It is also highly supported by the Microsoft itself who provides us with official Docker images with crucial services (like SQL Server vNext).

Requirements

I have taken an extra effort to keep the article simple, clear and illustrated with examples, however I also assume the reader is a software developer which has a basing knowledge of working with databases, Linux command line and the Docker. There are also some software requirements.

Software requirements

• Ubuntu 16.04 (or newer) as an OS for application environment with at least 3250 MB of memory
• Docker installed (instruction)
• mssql-tools package installed (instruction)

All files used in this article are shared with you on Github repository. Just click to this link.

Setting up the SQL Server vNext

I’ve already discussed some aims, so let’s go the interesting part and let’s set up the SQL Server vNext.

Docker image customization

I’ll start from pulling official Microsoft Docker image (microsoft/mssql-server-linux) which I will customize later in our Dockerfile:

It’s probably a good time to prepare your coffee, because the image size is quite large (500 MB) and it can take some time to download. Let’s look at the content of the image. It’s not so long, but I’ve added some hopefully helpful comments.

Dockerfile:

• from microsoft/mssql-server-linux – points the base image
• ENV ACCEPT_EULA=”Y” – sets environment variable which decides whether the EULA is accepted (End-user license agreement)
• ENV SA_PASSWORD=”Al3#ambr.0″ – sets password for System Administrator account
• ENV PATH=”/opt/mssql-tools/bin/:${PATH}” – adds path to the executables from mssql-tools package installed on Docker image in order to use sqlcmd command in a convenient way
• EXPOSE 1433 – informs that I want to expose externally the port on which the SQL Server listens by default

When the Dockerfile is ready I should build the image from it. Let’s call the image vts_mssql:

Please note that I called this command in the directory which contains the Dockerfile (the build command searches for a Dockerfile at the root of the build context by default).

Running Docker container

It’s getting exciting because our Docker image is prepared and I’m ready to run it as a container. It boils down to an one simple command:

As you can see, I give a command to run a container and just after that I simply check whether it did not silently crash, what is always worth to do when the container is run in detached mode. But take a closer look to a docker run command.

• –name vts_mssql – specifies the name of the container what makes the management much easier
• -p 1433:1433 – defines port mapping between host and the container (host:container); I decided to not change the default listening port
• -d – enabled detached mode (container running in background)
• vts_mssql – image name

Great success! The deployment of the SQL Server vNext is done and it is ready for connections. But one small thing before I play with it. As I already said, the hosting OS has a minimum memory requirement and this is why:

This is the requirement of the SQL Server itself and the above error message was produced when the memory assigned to the Linux virtual machine was limited. Please also note, that I did not run the container in detached mode on purpose in order to see the exact error message immediately.

Testing SQL Server

Once I have running container I can try to establish connection with it. First of all, let’s make a connection inside the container in order to investigate how does it look internally.

Internal connection

I will try to get access to the bash within the running container. To do it, I need to find the container identifier. I’ll do it with the following command:

I have passed a combined -a -q -f flags to a docker ps command which stands for:

• -a – display all containers
• -q – quiet mode (displays container id and quits)
• -f – filter
• “name=vts_mssql” – filtration argument

When the identifier of the container is determined, I can pass it to the docker exec command (or event combine it with the previous one, what you can see below):

• -it – (combined -i -t) enables interactive mode and allocates a pseudo-TTY
• 3983f102f771 – container id
• /bin/bash – command to execute within a container
• exit – closes TTY

Now I can try to connect the server and execute simple queries in order to see if it works properly. I will use the sqlcmd command as presented below:

When sqlcmd establishes connection it opens an interactive console where you can execute queries separated by GO command. For example, I have listed all available databases and used one of them. It looks that our instance works properly and is accessible from inside the container. But what about the the external connection, which is much more interesting in most cases?

External connection

It’s time to make a connection to a server running in container from the host operating system. I hope that you’ve already installed mssql-tools (instruction) package. Just go to the directory where sqlcmd is available (if sqlcmd is added to PATH variable you can skip this step) and make use of it. The output presented below shows also how the situation changes when the docker container is stopped.

Now I have a proof the server is really ready to work with client applications. Let us note, that I didn’t need to specify port number during connection to the server by sqlcmd. This may look different when the port is changed from the default one in mapping while running container. It’s worth to be remembered.

Create a new database

Since our server is ready to work I are going to create a new database from a script file which contains a few simple tables and relations between them. The file is available on our Github repository (link).

I’ll modify a bit sqlcmd parameters so it takes a script file as an input. For that purpose the flag -i is prepared. Let’s watch it in action:

Now I have a database structure restored from a script file available on our SQL Saver vNext instance.

What about data persistence?

The last but not least question is about the data persistence in the container after the restart. Fortunately, it’s persisted without additional steps, unless the container is removed and re-created. I have a clear illustration below:

As one can see, the freshly created database is still existing on the server despite of it was restarted.

Summary

I hope to give a clear example on how easily and quickly developers can set the environment consisting of SQL Server vNext on a Linux machine by usage of Docker and therefore create application in a more flexible way. I believe it is always worth to give the trending technologies a try for the sake of better performance and user experience. However, the combination of tools presented in this article makes our work as a software developers easier too.