mssql server linux docker
This 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 'SA_PASSWORD=@MyMSSQLDocker112' -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 'SA_PASSWORD=@MyMSSQLDocker112' -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 'SA_PASSWORD=@MyMSSQLDocker112' -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
root@300fb0551542:/# pwd
/
root@300fb0551542:/# cd
root@300fb0551542:~# pwd
/root
root@300fb0551542:~# which sqlcmd
root@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
testdb
(5 rows affected)
1> QUIT
root@300fb0551542:~#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.
