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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
vts@vts:~$ docker pull microsoft/mssql-server-linux Using default tag: latest latest: Pulling from microsoft/mssql-server-linux aed15891ba52: Pull complete 773ae8583d14: Pull complete d1d48771f782: Pull complete cd3d6cd6c0cf: Pull complete 8ff6f8a9120c: Pull complete 1fd7e8b10447: Pull complete bd485157db89: Pull complete 273a1970ce9c: Pull complete fe20b2602177: Pull complete 086b69c4d68d: Pull complete Digest: sha256:38346f8beba690c99f7ff35b11118df3c0731a77784a25e3165eec5f592a1498 Status: Downloaded newer image for microsoft/mssql-server-linux:latest |
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:
1 2 3 4 5 6 7 |
from microsoft/mssql-server-linux ENV ACCEPT_EULA="Y" ENV SA_PASSWORD="Al3#ambr.0" ENV PATH="/opt/mssql-tools/bin/:${PATH}" EXPOSE 1433 |
- 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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
vts@vts:~/Desktop/mssql$ docker build -t vts_mssql . Sending build context to Docker daemon 126.5kB Step 1/5 : FROM microsoft/mssql-server-linux ---> cd385d707eee Step 2/5 : ENV ACCEPT_EULA "Y" ---> Using cache ---> 952e9beaa956 Step 3/5 : ENV SA_PASSWORD "Al3#ambr.0" ---> Using cache ---> 5fd44d37611a Step 4/5 : ENV PATH "/opt/mssql-tools/bin/:${PATH}" ---> Using cache ---> d4b3344f6cf6 Step 5/5 : EXPOSE 1433 ---> Using cache ---> 69efa2e7b682 Successfully built 69efa2e7b682 Successfully tagged vts_mssql:latest |
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:
1 2 3 4 5 |
vts@vts:~$ docker run --name vts_mssql -p 1433:1433 -d vts_mssql 3983f102f77119016939e629696e192934ae51e7ae5c0106ede3cdbb6134b564 vts@vts:~$ docker ps -a CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES 3983f102f771 vts_mssql "/bin/sh -c /opt/m..." 9 seconds ago Up 8 seconds 0.0.0.0:1433->1433/tcp vts_mssql |
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:
1 2 |
vts@vts:~/Desktop/mssql$ docker run --name vts_mssql -p 1433:1433 vts_mssql sqlservr: This program requires a machine with at least 3250 megabytes of memory. |
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:
1 2 |
vts@vts:~$ docker ps -aqf "name=vts_mssql" 3983f102f771 |
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):
1 2 3 4 5 6 7 |
vts@vts:~$ docker exec -it 3983f102f771 /bin/bash root@3983f102f771:/# exit exit vts@vts:~$ docker exec -it `docker ps -aqf "name=vts_mssql"` /bin/bash root@3983f102f771:/# exit exit vts@vts:~$ |
- -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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
vts@vts:~$ docker exec -it `docker ps -aqf "name=vts_mssql"` /bin/bash root@3983f102f771:/# sqlcmd -S localhost -U SA -P 'Al3#ambr.0' 1> SELECT Name FROM sys.Databases 2> GO Name -------------------------------------------------------------------------------------------------------------------------------- master tempdb model msdb (4 rows affected) 1> USE master 2> GO Changed database context to 'master'. 1> exit root@3983f102f771:/# exit exit vts@vts:~$ |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
vts@vts:~$ cd /opt/mssql-tools/bin/ vts@vts:/opt/mssql-tools/bin$ ./sqlcmd -S localhost -U SA -P 'Al3#ambr.0' 1> select name from sys.databasebs 2> go name -------------------------------------------------------------------------------------------------------------------------------- master tempdb model msdb (4 rows affected) 1> exit vts@vts:/opt/mssql-tools/bin$ docker stop vts_mssql vts_mssql vts@vts:/opt/mssql-tools/bin$ ./sqlcmd -S localhost -U SA -P 'Al3#ambr.0' Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login timeout expired. Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : TCP Provider: Error code 0x2749. Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.. |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
vts@vts:~$ cd /opt/mssql-tools/bin/ vts@vts:/opt/mssql-tools/bin$ ./sqlcmd -S localhost -U SA -P 'Al3#ambr.0' -i /home/vts/Desktop/mssql/VtsMSSQLDb.sql Changed database context to 'master'. Changed database context to 'VtsMSSQLDb'. Changed database context to 'VtsMSSQLDb'. Changed database context to 'master'. vts@vts:/opt/mssql-tools/bin$ ./sqlcmd -S localhost -U SA -P 'Al3#ambr.0' 1> SELECT Name FROM sys.Databases 2> GO Name -------------------------------------------------------------------------------------------------------------------------------- master tempdb model msdb VtsMSSQLDb (5 rows affected) 1> exit vts@vts:/opt/mssql-tools/bin$ |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
vts@vts:/opt/mssql-tools/bin$ docker stop vts_mssql vts_mssql vts@vts:/opt/mssql-tools/bin$ docker start vts_mssql vts_mssql vts@vts:/opt/mssql-tools/bin$ ./sqlcmd -S localhost -U SA -P 'Al3#ambr.0' 1> SELECT Name FROM sys.Databases 2> GO Name -------------------------------------------------------------------------------------------------------------------------------- master tempdb model msdb VtsMSSQLDb (5 rows affected) 1> exit vts@vts:/opt/mssql-tools/bin$ |
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.
Author: Mateusz Chmielewski
Senior software developer at VTS Software.
One Reply to “Running SQL Server vNext on a Linux machine”