Scott Hanselman

SQL Server on Linux or in Docker plus cross-platform SQL Operations Studio

July 27, '18 Comments [20] Posted in Linux | Open Source
Sponsored By

imageI recently met some folks that didn't know that SQL Server 2017 also runs on Linux but they really needed to know. They had a single Windows desktop and a single Windows Server that they were keeping around to run SQL Server. They had long-been a Linux shop and was now fully containerzed...except for this machine under Anna's desk. (I assume The Cloud is next...pro tip: Don't have important servers under your desk). You can even get a license first and decide on the platform later.

You can run SQL Server on a few Linux flavors...

or, even better, run it on Docker...

Of course you'll want to do the appropriate volume mapping to keep your database on durable storage. I'm digging being able to spin up a full SQL Server inside a container on my Windows machine with no install.

I've got Docker for Windows on my laptop and I'm using Shayne Boyer's "Docker Why" repo to make the point. Look at his sample DockerCompose that includes both a web frontend and a backend using SQL Server on Linux.

version: '3.0'
services:

mssql:
image: microsoft/mssql-server-linux:latest
container_name: db
ports:
- 1433:1433
volumes:
- /var/opt/mssql
# we copy our scripts onto the container
- ./sql:/usr/src/app
# bash will be executed from that path, our scripts folder
working_dir: /usr/src/app
# run the entrypoint.sh that will import the data AND sqlserver
command: sh -c ' chmod +x ./start.sh; ./start.sh & /opt/mssql/bin/sqlservr;'
environment:
ACCEPT_EULA: 'Y'
SA_PASSWORD: P@$$w0rdP@$$w0rd

Note his starting command where he's doing an initial population of the database with sample data, then running sqlservr itself. The SQL Server on Linux Docker container includes the "sqlcmd" command line so you can set up the database, maintain it, etc with the same command line you've used on Windows. You can also configure SQL Server from Environment Variables so it makes it easy to use within Docker/Kubernetes. It'll take just a few minutes to get going.

Example:

/opt/mssql-tools/bin/sqlcmd -S localhost -d Names -U SA -P $SA_PASSWORD -I -Q "ALTER TABLE Names ADD ID UniqueIdentifier DEFAULT newid() NOT NULL;"

I cloned his repo (and I have .NET Core 2.1) and did a "docker-compose up" and boom, running a front end under Alpine and backend with SQL Server on Linux.

101→ C:\Users\scott> docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
e5b4dae93f6d namesweb "dotnet namesweb.dll" 38 minutes ago Up 38 minutes 0.0.0.0:57270->80/tcp, 0.0.0.0:44348->443/tcp src_namesweb_1
5ddffb76f9f9 microsoft/mssql-server-linux:latest "sh -c ' chmod +x ./…" 41 minutes ago Up 39 minutes 0.0.0.0:1433->1433/tcp mssql

Command lines are nice, but SQL Server is known for SQL Server Management Studio, a nice GUI for Windows. Did they release SQL Server on Linux and then expect everyone use Windows to manage it? I say nay nay! Check out the cross-platform and open source SQL Operations Studio, "a data management tool that enables working with SQL Server, Azure SQL DB and SQL DW from Windows, macOS and Linux." You can download SQL Operations Studio free here.

SQL Ops Studio is really impressive. Here I am querying SQL Server on Linux running within my Docker container on my Windows laptop.

SQL Ops Studio - Cross platform SQL management

As I'm digging in and learning how far cross-platform SQL Server has come, I also checked out the mssql extension for Visual Studio Code that lets you develop and execute SQL against any SQL Server. The VS Code SQL Server Extension is also open source!

Go check it SQL Server in Docker at https://github.com/Microsoft/mssql-docker and try Shayne's sample at https://github.com/spboyer/docker-why


Sponsor: Scale your Python for big data & big science with Intel® Distribution for Python. Near-native code speed. Use with NumPy, SciPy & scikit-learn. Get it Today!

About Scott

Scott Hanselman is a former professor, former Chief Architect in finance, now speaker, consultant, father, diabetic, and Microsoft employee. He is a failed stand-up comic, a cornrower, and a book author.

facebook twitter subscribe
About   Newsletter
Sponsored By
Hosting By
Dedicated Windows Server Hosting by SherWeb
Friday, 27 July 2018 06:03:46 UTC
It would be nice to have SQL Operations Studio dockerized too.
Manu
Friday, 27 July 2018 06:26:17 UTC
I really like this feature "get a license first and decide on the platform later..."
Friday, 27 July 2018 07:57:25 UTC
@Manu, I'm not sure there's any benefit / it's even possible to run applications with a GUI inside Docker. I'd love to be proven wrong on this point though. There's a number of applications I've come across that different versions don't sit well next to each other, so if it is possible, that'd be a great help.
Gareth
Friday, 27 July 2018 08:42:16 UTC
@Gareth @Manu, for containerized GUI applications there is Turbo.net. Currently it only runs on Windows, but last time i checked they did plan to have it work on Mac also.

@Scott, thanks for this article. I was wondering about this exact thing a few days ago. If SQL server will can run in a container (i knew it ran on linux).
Will it run on a Raspberry Pi also then?
Quintonn
Friday, 27 July 2018 11:00:09 UTC
The sad thing about SQL Server for Linux is that there is no Express edition docker image, and other editions have too high resources requirements. I want to run SQL Server Express on a Synology NAS in Docker, but no luck...
Friday, 27 July 2018 11:28:39 UTC
Correction to my previous comment: there now IS an Express edition specified by the environment variable. However, you still have to have at least 2 Gigs of RAM :(
Friday, 27 July 2018 13:00:38 UTC
@Gareth You can run containerized GUI applications using a Windows X server, like VcXsrv (available for free at sourceforge). I'm currently using it to run my IDE and some other things inside a ubuntu container on docker for windows.
Øyvind Strømmen
Friday, 27 July 2018 13:48:40 UTC
Only one issue with using the Linux image on your Windows box. You still need a Linux VM running on your box, because of SQL Server's memory requirements and Linux Containers on Windows (LCOW) has an outstanding issue preventing assigning more memory to a Linux container. See https://github.com/Microsoft/opengcs/issues/145
Richard Gavel
Friday, 27 July 2018 15:29:45 UTC
I've been running the sqlserver docker image on my mac since preview. The install is really quick. I made a separate docker file volume image for the data files, so the sql server docker image can be updated with losing the data (on linux you can use a host folder instead). One bash script and updates are a breeze.

also visual studio code has added docker container support, so you can start, stop the sqlserver container without additional tools.



Bruce
Sunday, 29 July 2018 01:07:10 UTC
@Richard Gavel
It is definitely worth noting that the issue you mention only applies to LCOW mode; the default Docker installation most certainly allows you to run the mssql-server-linux image just fine. The issue is when you're trying to run with mixed Windows/Linux containers at the same time (which is a feature only available in the experimental/edge builds of Docker anyway).

You also technically need a Linux VM running on your box to even use Docker on Windows in the first place - because Windows can't do the equivalent of what LXC containers do (e.g. separating kernel processes by cgroups), a "MobyLinux" virtual machine is created in Hyper-V (or VirtualBox if you have it configured for that). That's what the memory slider in Docker's settings changes - the memory allocated to the MobyLinux VM.
William Wingler
Sunday, 29 July 2018 01:13:39 UTC
Quick note on my above comment - it is possible I misunderstood how LCOW works, in which case the second paragraph doesn't make as much sense. I simply assumed LCOW still used a VM because the MobyLinux VM still shows up in Hyper-V for me while in LCOW mode. Thinking about it, that could just be left there for the user to switch from Windows to Linux mode again.
William Wingler
Sunday, 29 July 2018 02:34:49 UTC
Awesome post.

thanks for sharing.
Sunday, 29 July 2018 03:29:11 UTC
Thanks for the reading,. I'm always struggling with Linux...
Johan
Monday, 30 July 2018 07:35:40 UTC
@Andriy - you can configure the amount of RAM and CPU SQL Server is allowed to use. So if 2 GB is too much you can limit it to 1GB or less. Although of course you won't be doing anything more than simple CRUD on that amount of RAM without it running like a dog.


@others - You can run different versions of SQL Server side by side on Windows, so when developing why would you bother wasting the CPU/RAM on Docker/LinuxVM when you could just run it natively. I have SQL 2008R2, 2014 and 2017 developer edition installed locally for example.
Peter
Monday, 30 July 2018 08:37:57 UTC
This is pretty impressive stuff. I already appreciate the work that has gone into the Windows Subsystem for Linux, which has made cross platform work much more straight forward. Now containerising SQL Server and running on Linux, is a giant leap forward.
Monday, 30 July 2018 10:48:38 UTC
@Peter You're probably talking about MSSQL_MEMORY_LIMIT_MB setting (env. variable). I tried this, but still can't start the server with the following error in the log:
"sqlservr: This program requires a machine with at least 2000 megabytes of memory.".
So it looks like there is a check for amount of installed physical memory against hardcoded minimum requirement. And the setting you're referring to looks to be for run-time.
Tuesday, 31 July 2018 07:46:29 UTC
@Andriy - hmmmm… well I've got a SQL 2008R2 which is configure to use 1024MB of RAM. This isn't an environment variable, well not that I know of. In SSMS, object explorer, right click the instance node --> properties --> memory and set the maximum amount of memory.
Peter
Tuesday, 31 July 2018 11:27:00 UTC
@Peter, you're referring to the same setting which can be set in multiple ways, including environment variable and SSMS GUI. Anyway, this setting specifies *maximum* amount of memory server can use, while I'm talking about *minimum* memory requirement listed in requirements of the SQL Server for Linux. For example, see "Requirements" section in container image description https://hub.docker.com/r/microsoft/mssql-server-linux/
Wednesday, 01 August 2018 00:11:20 UTC
What is the difference in memory consumption between the native EXE SQL Server Management Studio and the JavaScript base SQL Operations Studio?

We cannot afford losing and extra 500 MB ram needed to run a JavaScript wrapped in a binary control application.
Bob
Wednesday, 08 August 2018 07:07:19 UTC
Great post, loving SQL Operations Studio!
Mattias Josephsson
Comments are closed.

Disclaimer: The opinions expressed herein are my own personal opinions and do not represent my employer's view in any way.