Back to articles list
- 6 minutes read

Running Microsoft SQL Server on Linux

If you were a fan of Linux and SQL Server, you basically had to have two operating systems at all times. But now, Microsoft has made a Linux version of their relational database management system. How does it work, and how well does it perform?

Microsoft products used to be Windows-centric, but in recent years Microsoft CEO Satya Nadella has started moving the company towards cloud-based and open-source projects and enabling their software to run on other platforms. Because of this, running SQL Server, Microsoft’s RDBMS, on a Linux system is now a possibility.

On March 7 2016, Microsoft announced that SQL Server for Linux would be released sometime in mid-2017. They later ran a private preview, and they released a public Community Technology Preview in November 2016.

Has hell frozen over, as some say? Microsoft hasn’t been known to work much with Linux. The most important question, joking aside, is how well SQL Server for Linux performs. But first, there’s another question that needs answering: How exactly did SQL Server make it onto Linux?

How Does SQL Server Run on Linux?

Microsoft’s developers had two options: rewrite SQL Server to run natively on Linux or try to port it with a Platform Abstraction Layer (PAL). Rewriting a huge system like SQL Server would take a long time – and quite a lot of time without much visible progress – so they decided to try using the PAL option. Microsoft Research has a project called Drawbridge which allows for application containerization. The developers used this to create an OS abstraction layer (SQLPAL) for the SQL Server Operating System.

The SQL Server Operating System is a thin abstraction layer that handles IO operation scheduling, thread synchronization and deadlock detection, memory management and exception handling.

So, what we’re getting is the actual Windows SQL Server in a container running a small NT kernel that runs in the Linux environment. For the most part, this setup gives us the same SQL Server experience and features on Linux as we get on Windows.

Because of low-level WinAPI call substitution, SQL Server is able to utilize libraries that are bound to Windows. This includes MSXML, COM components, Windows Event Log integration, and others. You can read more about how it works on the SQL Server blog.

Installing SQL Server on Linux

First, we must note that SQL Server requires 3.25GB of RAM and XFS or EXT4 for the underlying file system. Supposing your system meets these requirements, this is how you’d install the program.

First, download the SQL Server preview. The SQL Server "vNext" Community Technology Preview is now publicly available for evaluation. You can download it for RHEL, Ubuntu, and SUSE from Microsoft’s repository. For MacOS and any other Linux distributions, there is a Docker image available. You will find download and installation details for each platform here.

I chose the Docker image because it’s the easiest to install and it doesn’t need much configuration. To run my container, I used the following code:

$ docker run \
  -e 'ACCEPT_EULA=Y' \
  -e 'SA_PASSWORD=Password123!' \
  -p 1433:1433 \
  -v ~/mssqldb:/var/opt/mssql \
  -d \
  --name sqlserver \
  microsoft/mssql-server-linux

First, I set two environment variables with the -e option. One is for accepting the End User License Agreement for SQL Server and the other is for setting the SA user’s password (SA user is equivalent to root user). This password must be strong, with a minimum of 8 characters, utilizing both upper- and lower-case letters, at least one number, and at least one non-alphanumeric character.

Next, I mapped local port 1433 to the container’s port 1433 with the -p option. With the -v option, I created a volume mapping my local folder ~/mssqldb to the container’s /var/opt/mssql. This is where the database is stored.

The -d option runs the container in a detached mode, which means it’s going to run in the background. The --name option is quite obvious – it’s a local identifier for this container. The last parameter is the container image’s name, which will be pulled from the Docker Hub.

Once you run this command it will automatically download the image, create the container, set up the environment, and run the SQL Server. Voila! You can start using SQL Server on Linux.

Accessing the SQL Server on Linux

Once we have our SQL Server instance running (at this point, it doesn’t matter how we installed it), we need to connect to it. We’ll start with a sqlcmd application that is part of Microsoft’s SQL Server tool package, which you can download here. See the link for installation instructions.

In order to connect to the SQL Server, run:

sqlcmd -S localhost -U SA -P ''

Note: If you want to connect to an instance on a different machine, use the IP or domain of that server instead of localhost.

You’re then presented with an input prompt:

1>

You can run SQL statements from this prompt. After each statement, you must issue a GO statement; it will run whatever you typed in earlier.

Another way to connect to the SQL Server is by using Visual Studio Code with an extension called ‘mssql’. The extension provides methods for connecting to the database, selecting logical databases, executing SQL statements, and presenting the results. You can find installation and usage instructions here.

Visual Studio Code – screen

An alternative is to use the SQL Server Management Studio on Windows to connect with your SQL Server running on Linux. The Management Studio has a lot of options for configuring the database as well as tools that help with the administration. But for now, Management Studio is only for Windows.

How Well Does SQL Server for Linux Work?

It’s not yet completely clear how well SQL Server for Linux will perform once it’s released, but it looks promising. Fabrizio Accatino has conducted a performance check running SQL Server CTP 1.0 (November 2016) on Windows and Linux Virtual Machines on Azure. He performed a few queries on the AdventureWorks2014 database and the results were astonishing. It seems that in most cases the Linux version of SQL Server is actually faster than the Windows one. You can read more about the test and the results on Accatino’s blog.

Given how easy it is to install and use, SQL Server for Linux may replace many instances of SQL Server for Windows. At the very least, this would be because there’d be no need for an additional Windows Server license. The only features the Linux version lacks are those concentrated around Power BI and Excel, as these are still bound to the Windows platform.

I strongly encourage you to try SQL Server on Linux. Play around with it and get to know it. You may be using it a lot in the future.

Marian Dziubiak, Junior Technical Writer at Vertabelo Get in touch with Marian Dziubiak:

go to top