How to Setup Linux Version of MS SQL Server on Docker Container

mssql server linux dockerThis article explains how to setup and run Microsoft SQL server on containerised Linux Operating System. I will use the image available on Docker Hub to run a Docker container. An earlier version of SQL Server is meant to run on Windows OS. SQL Server 2017 CTP 2.1 run Linux based OS as well. Microsoft also published docker images which are having preinstalled mysql-server for Linux and Windows containers. We will talk about installing mysql-server on linux based container OS.

There are few minimum requirements for using microsoft/mssql-server-linux docker image which are listed below:

  • Install Docker Engine 1.8 or higher
  • Minimum of 4 GB of disk space
  • Minimum of 4 GB of RAM
  • You need to set a strong mssql admin password which should be at least 8 characters including uppercase, lowercase letters, base-10 digits and/or non-alphanumeric symbols.

How to create mssql-server-linux Docker container

To create a docker container which is having mssql-server we will have to run below command which will download microsoft/mssql-server-linux (if not already present on the machine) and create a container based on it.

Before that, I will show you some docker commands.

To check if any docker container is running:

BANL141cc14d1:~ ngupta9$ docker ps

CONTAINER ID        IMAGE               COMMAND             CREATED             STATUS              PORTS               NAMES

BANL141cc14d1:~ ngupta9$

It means that there is no container running at the moment.

To check all the containers on the machine (not necessarily in running state):

BANL141cc14d1:~ ngupta9$ docker ps -a

CONTAINER ID        IMAGE                                   COMMAND             CREATED             STATUS                    PORTS                                          NAMES

bec19ccd48af        coolnicks/qbdt-dashboard:ubuntu-14.04   "/bin/bash"         12 weeks ago        Exited (0) 12 weeks ago   0.0.0.0:8880->80/tcp, 0.0.0.0:8881->8000/tcp   QBDT-Dashboard

6d0c74506404        ubuntu:14.04                            "/bin/bash"         12 weeks ago        Exited (0) 12 weeks ago                                                  qbdt-dashboard

7b667349c0c3        python:2.7-wheezy                       "python2"           12 weeks ago        Exited (0) 12 weeks ago                                                  python-2.7

22dc8a99c427        coolnicks/ansible:ubuntu1404-nginx      "/bin/bash"         3 months ago        Exited (0) 3 months ago                                                  my_ubuntu_nginx_container

BANL141cc14d1:~ ngupta9$

This shows that there are 4 containers present on the system and in exited state. You can also see the docker image on which they are based. More than 1 container can be based on a single docker image.

Similarly, you can check the docker images present on your machine.

BANL141cc14d1:~ ngupta9$ docker images

REPOSITORY                    TAG                 IMAGE ID            CREATED             SIZE

coolnicks/qbdt-dashboard      ubuntu-14.04        89faaa2ecceb        12 weeks ago        936.1 MB

python                        2.7-wheezy          97f122a59c01        3 months ago        530.6 MB

ubuntu                        14.04               7c09e61e9035        3 months ago        188 MB

coolnicks/ansible             ubuntu1404-nginx    b4dcce787cd4        3 months ago        329 MB

jenkins                       latest              e11ceb27c34e        5 months ago        714.6 MB

ansible/ubuntu14.04-ansible   stable              1526eaefd882        20 months ago       287.4 MB

BANL141cc14d1:~ ngupta9$

These are the images present on the machine. microsoft/mssql-server-linux image is not already present. We will run the command to create a container based on microsoft/mssql-server-linux. Docker engine will check if this image is present on the machine or not. If not then it will download the image on the machine and create a container based on this image. From next time onwards it will not download the image if you want to create a mssql-server-linux container. It will use the already downloaded image.

BANL141cc14d1:~ ngupta9$ docker run -e 'ACCEPT_EULA=Y' -e '[email protected]' -p 1433:1433 -d microsoft/mssql-server-linux

Unable to find image 'microsoft/mssql-server-linux:latest' locally

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

67fbf3e59574: Pull complete

4c0c60131530: Pull complete

Digest: sha256:604d27fe5d3d9b4434fb1657e9bf4f2c2bf55ea9bd29dc0cb3660d84bc6f56a8

Status: Downloaded newer image for microsoft/mssql-server-linux:latest

0cebc8c5f887c237007bb099e2e93012ac8a4746b275bf078eb881b027ba2d3c

BANL141cc14d1:~ ngupta9$

BANL141cc14d1:~ ngupta9$ docker images

REPOSITORY                     TAG                 IMAGE ID            CREATED             SIZE

microsoft/mssql-server-linux   latest              5985832855cf        4 weeks ago         1.382 GB

coolnicks/qbdt-dashboard       ubuntu-14.04        89faaa2ecceb        12 weeks ago        936.1 MB

python                         2.7-wheezy          97f122a59c01        3 months ago        530.6 MB

ubuntu                         14.04               7c09e61e9035        3 months ago        188 MB

coolnicks/ansible              ubuntu1404-nginx    b4dcce787cd4        3 months ago        329 MB

jenkins                        latest              e11ceb27c34e        5 months ago        714.6 MB

ansible/ubuntu14.04-ansible    stable              1526eaefd882        20 months ago       287.4 MB

BANL141cc14d1:~ ngupta9$

BANL141cc14d1:~ ngupta9$ docker ps -a

CONTAINER ID        IMAGE                                   COMMAND                  CREATED             STATUS                     PORTS                                          NAMES

0cebc8c5f887        microsoft/mssql-server-linux            "/bin/sh -c /opt/mssq"   5 minutes ago       Exited (1) 5 minutes ago                                                  tender_saha

bec19ccd48af        coolnicks/qbdt-dashboard:ubuntu-14.04   "/bin/bash"              12 weeks ago        Exited (0) 12 weeks ago    0.0.0.0:8880->80/tcp, 0.0.0.0:8881->8000/tcp   QBDT-Dashboard

6d0c74506404        ubuntu:14.04                            "/bin/bash"              12 weeks ago        Exited (0) 12 weeks ago                                                   qbdt-dashboard

7b667349c0c3        python:2.7-wheezy                       "python2"                12 weeks ago        Exited (0) 12 weeks ago                                                   python-2.7

22dc8a99c427        coolnicks/ansible:ubuntu1404-nginx      "/bin/bash"              3 months ago        Exited (0) 3 months ago                                                   my_ubuntu_nginx_container

BANL141cc14d1:~ ngupta9$

You can see that it has downloaded the image and created a container with container ID 0cebc8c5f887c237007bb099e2e93012ac8a4746b275bf078eb881b027ba2d3c or 0cebc8c5f887.

Sometimes you may face an issue where you will get error “This program requires a machine with at least 3250 megabytes of memory.”

BANL141cc14d1:~ ngupta9$ docker run -e 'ACCEPT_EULA=Y' -e '[email protected]' -p 1433:1433 microsoft/mssql-server-linux

sqlservr: This program requires a machine with at least 3250 megabytes of memory.

So, you will have to allocate ~4GB RAM to docker container in the command line. In some cases even if you are allocating memory to docker container but it will fail with the same error. In that case you will have to check the memory allocated to Docker and make it 4GB. Generally, it is seen in Mac (You can change the allocated RAM in preference section under docker).

BANL141cc14d1:~ ngupta9$ docker run --memory 4096m -e 'ACCEPT_EULA=Y' -e '[email protected]' -p 1433:1433 -d microsoft/mssql-server-linux

300fb0551542ca1fe9ed0c021980ed3ad61c788ca6dae66147e20598e45e6c07



BANL141cc14d1:~ ngupta9$ docker ps

CONTAINER ID        IMAGE                          COMMAND                  CREATED             STATUS              PORTS                    NAMES

300fb0551542        microsoft/mssql-server-linux   "/bin/sh -c /opt/mssq"   4 seconds ago       Up 3 seconds        0.0.0.0:1433->1433/tcp   pensive_roentgen

BANL141cc14d1:~ ngupta9$

Now you can connect with the MsSql server running on docker container and perform database operations using below commands:

BANL141cc14d1:~ ngupta9$ docker exec -it 300fb0551542 /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P @MyMSSQLDocker112

1> SELECT Name from sys.Databases;

2> GO

Name

--------------------------------------------------------------------------------------------------------------------------------

master

tempdb

model

msdb

 

(4 rows affected)

1> CREATE DATABASE testdb;

2> GO

1>

1>

1> USE testdb;

2> GO

Changed database context to 'testdb'.

1>

1> CREATE TABLE inventory (id INT, name NVARCHAR(50), quantity INT);

2> GO

1> INSERT INTO inventory VALUES (1, 'banana', 150);

2> INSERT INTO inventory VALUES (2, '', 154);

3> GO

 

(1 rows affected)

 

(1 rows affected)

1> SELECT * FROM inventory WHERE quantity > 152;

2> GO

id          name                                               quantity

----------- -------------------------------------------------- -----------

2                                                      154

 

(1 rows affected)

1>

1> SELECT * FROM inventory WHERE quantity > 150;

2> GO

id          name                                               quantity

----------- -------------------------------------------------- -----------

2                                                      154

 

(1 rows affected)

1>

1> SELECT * FROM inventory WHERE quantity > 149;

2> GO

id          name                                               quantity

----------- -------------------------------------------------- -----------

1 banana                                                     150

2                                                      154

 

(2 rows affected)

1> QUIT

BANL141cc14d1:~ ngupta9$

If you want to go inside the container in interactive mode and run command then you can do like this:

BANL141cc14d1:~ ngupta9$ docker ps

CONTAINER ID        IMAGE                          COMMAND                  CREATED             STATUS              PORTS                    NAMES

300fb0551542        microsoft/mssql-server-linux   "/bin/sh -c /opt/mssq"   15 minutes ago      Up 15 minutes       0.0.0.0:1433->1433/tcp   pensive_roentgen

BANL141cc14d1:~ ngupta9$ docker exec -it 300fb0551542 bash

[email protected]:/# pwd

/

[email protected]:/# cd

[email protected]:~# pwd

/root

[email protected]:~# which sqlcmd

[email protected]:~# /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P @MyMSSQLDocker112

1> SELECT Name from sys.Databases;

2> GO

Name

--------------------------------------------------------------------------------------------------------------------------------

master

tempdb

model

msdb

testdb

 

(5 rows affected)

1> QUIT

[email protected]:~#

Conclusion

Microsoft is aware that most of the companies are using Linux based systems as development to production servers and many are migrating from windows to Linux if possible. Hence Microsoft is also coming into the Linux area and providing its software which can run on Linux based systems and It is also now proving Docker images based on Linux containers. MsSql for Linux is the product which can be run on Linux based systems and on Linux based docker containers.

Nikhil Gupta 3:00 am

Have anything to say?

Your email address will not be published. Required fields are marked *

All comments are subject to moderation.

2 Comments