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.
# we copy our scripts onto the container
# bash will be executed from that path, our scripts folder
# run the entrypoint.sh that will import the data AND sqlserver
command: sh -c ' chmod +x ./start.sh; ./start.sh & /opt/mssql/bin/sqlservr;'
SA_PASSWORD: [email protected][email protected]$$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.
/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:Usersscott> 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!
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!