SQL Server on Linux or in Docker plus cross-platform SQL Operations Studio
I 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.
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.
About Newsletter
Manu
@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?
also visual studio code has added docker container support, so you can start, stop the sqlserver container without additional tools.
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.
@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.
"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.
We cannot afford losing and extra 500 MB ram needed to run a JavaScript wrapped in a binary control application.
Comments are closed.