Running Microsoft SQL Server on Linux in Docker

One of the organizations that recently consulted me for advice, no longer willing to be extortion by Microsoft's high licensing fees, and plans to replace all Windows Server with Linux (Ubuntu). No big deal for services written in PHP, Java, and other cross platform languages. But some of the systems use MicroSoft SQL Server as their Database. The permanent cure is to migrate to MySQL, MariaDB, or other OpenSource Databases. But it takes time to translate all schema and queries. The other solution is to use Microsoft SQL Server on Linux in Docker as a temporary workaround.

Env: Ubuntu 16.04

Run official docker image

Pull and run

$ docker run --name mssql-server-linux -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=yourStrong(!)Password' -p 1433:1433 -d microsoft/mssql-server-linux

Check

$ docker ps

CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
7c73a9a4d0ef microsoft/mssql-server-linux "/bin/sh -c /opt/m..." 25 minutes ago Up 25 minutes 0.0.0.0:1433->1433/tcp mssql-server-linux

Connect and run T-SQL

Once it's running, you can connect it with built in mssql tools

$ docker exec -it mssql-server-linux /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P 'yourStrong(!)Password'

>1

Try some command

1> SELECT Name from sys.Databases;
2> GO

Name                                                                                                                            
--------------------------------------------------------------------------------------------------------------------------------
master                                                                                                                          
tempdb                                                                                                                          
model                                                                                                                           
msdb                                                                                                                            

(4 rows affected)

1> CREATE DATABASE testdb;
2> GO
1> USE testdb;
2> GO

Changed database context to 'testdb'.

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, 'orange', 154);
3> GO

(1 rows affected)

(1 rows affected)

1> SELECT * FROM inventory WHERE quantity > 152;
2> GO

id          name                                               quantity   
----------- -------------------------------------------------- -----------
          2 orange                                                     154

(1 rows affected)

1> QUIT

Additional Info

Data dir

root@7c73a9a4d0ef:~# ls /var/opt/mssql/data/

master.mdf   model.mdf     msdbdata.mdf  tempdb.mdf   testdb.mdf
mastlog.ldf  modellog.ldf  msdblog.ldf   templog.ldf  testdb_log.ldf

In Dockerfile we can see there's no entrypoint.sh MSSQL server is directly executed

CMD /opt/mssql/bin/sqlservr.sh

If you want to build your app extending from this image and has your own entrypoint.sh, this is an example

Reference

• https://hub.docker.com/r/microsoft/mssql-server-linux/
• https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-connect-and-query-sqlcmd
• https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-troubleshooting-guide#connection
• https://github.com/Microsoft/mssql-docker/blob/dc52dd5391fe397455430d04ffeb4ecb547e80fe/linux/preview/Dockerfile#L15