SQL Server on Linux: what runs, what is missing

SQL Server on Linux is the same database engine as on Windows, not a port or a cut-down rewrite. The T-SQL is identical, the .bak files are interchangeable, SSMS and every driver connect to it the same way, and an application cannot tell which operating system is underneath. What differs is everything around the engine: it is configured with mssql-conf and environment variables instead of SQL Server Configuration Manager, its files live under /var/opt/mssql, paths are case-sensitive, and a list of Windows-centric features is not there at all. For an application database, none of the missing features usually matter. For a DBA bringing habits from Windows, several do.

This guide covers SQL Server 2022 on Linux, including the Express edition that small hosted servers run, and is careful about which differences come from Linux and which come from Express.

How Microsoft runs a Windows engine on Linux#

SQL Server was written against Windows for thirty years, and Microsoft did not rewrite it. Since SQL Server 2017, the engine runs on top of the SQL Platform Abstraction Layer, SQLPAL - a small library operating system that gives the engine the Windows-shaped services it expects (memory management, threading, I/O) and translates them to Linux calls underneath. The process you see in ps is sqlservr, running as the mssql user, and on a normal install it is managed by systemd as the mssql-server service.

The practical consequences:

• Performance is comparable. Microsoft publishes benchmark results for both platforms, and for ordinary workloads you will not find a meaningful difference attributable to the operating system.
• The engine version is the same. SQL Server 2022 on Linux gets the same cumulative updates, the same compatibility level 160 and the same query optimiser as on Windows.
• Memory is managed by the engine, not left to the kernel. SQL Server on Linux caps itself, by default at 80 percent of the memory it can see, so that it does not get killed by the out-of-memory killer. Inside a container, recent builds read the container's memory limit rather than the host's total.

That last point matters on hosted servers. A SQL Server that believes it has the host's 128 GB will cheerfully grow until the container is stopped at its real limit. If you run SQL Server in your own container, set the limit explicitly rather than trusting detection; on Linux the kernel's out-of-memory behaviour is unforgiving, as Linux swap and the OOM killer explains.

Editions on Linux, and what Express adds to the picture#

Every edition exists on Linux: Express, Developer, Standard, Enterprise, plus Evaluation. The edition is chosen at setup time, either interactively with mssql-conf setup or with the MSSQL_PID environment variable in a container (Express, Developer, Standard, Enterprise, or a product key).

Express is free and fully functional within fixed caps that apply on both operating systems:

These are edition limits, not Linux limits, but they shape how a Linux Express server behaves. The buffer pool cap in particular means that giving an Express instance 6 GB of memory does not give it a 6 GB cache: the data cache stops at about 1.4 GB, and the rest serves the plan cache, query workspace memory, connections and the operating system. More memory still helps, but not linearly. SQL Server Express hosting works through when those caps are enough.

Configuration: mssql-conf and environment variables#

On Windows you change instance-level settings in SQL Server Configuration Manager and the registry. On Linux the equivalent is mssql-conf, a script that edits /var/opt/mssql/mssql.conf:

$ sudo /opt/mssql/bin/mssql-conf set memory.memorylimitmb 3072$ sudo /opt/mssql/bin/mssql-conf set network.tcpport 1433$ sudo /opt/mssql/bin/mssql-conf set filelocation.defaultbackupdir /var/opt/mssql/backup$ sudo /opt/mssql/bin/mssql-conf traceflag 1222 on$ sudo systemctl restart mssql-server

Most changes take effect only after a restart. The file itself is plain INI and readable:

[memory]memorylimitmb = 3072[network]tcpport = 1433[filelocation]defaultbackupdir = /var/opt/mssql/backup[traceflag]traceflag0 = 1222

The settings you are likely to touch:

In a container, the same settings are usually given as environment variables at first start: ACCEPT_EULA=Y, MSSQL_SA_PASSWORD, MSSQL_PID, MSSQL_COLLATION, MSSQL_TCP_PORT, MSSQL_MEMORY_LIMIT_MB, MSSQL_DATA_DIR, MSSQL_LOG_DIR and MSSQL_BACKUP_DIR. The older SA_PASSWORD variable still works on some images and is deprecated.

max server memory set with sp_configure still exists on Linux and still limits the buffer pool and related caches. It sits inside memory.memorylimitmb, which limits the whole process. If you control both, set memorylimitmb to what the machine can spare and max server memory somewhat below it.

On a managed host you usually cannot run mssql-conf at all - the host owns the instance configuration and gives you sa. Everything you can change from T-SQL (sp_configure, ALTER DATABASE, ALTER SERVER CONFIGURATION) still works, and that covers nearly everything an application needs.

Files, paths and case sensitivity#

A default install keeps everything under /var/opt/mssql:

/var/opt/mssql/  data/       system and user database files (.mdf, .ldf), default backups  log/        errorlog, errorlog.1 ... and extended event files  secrets/    the machine key  mssql.conf  the configuration file

The binaries are under /opt/mssql/, and the command-line tools under /opt/mssql-tools18/bin/ for the current version 18 packages (/opt/mssql-tools/bin/ for the older ones). The error log is a text file you can read with any tool, which is often quicker than opening it through SSMS:

$ sudo tail -n 50 /var/opt/mssql/log/errorlog

Two things about paths catch people moving from Windows:

1. The file system is case-sensitive. /var/opt/mssql/Data/app.mdf and /var/opt/mssql/data/app.mdf are different files. A BACKUP or RESTORE ... WITH MOVE with the wrong capitalisation fails with operating system error 2.
2. The paths belong to the server. RESTORE DATABASE ... FROM DISK = 'C:\backups\app.bak' run from SSMS on your laptop looks for that file on the Linux machine. Copy the file to the server first.

Case sensitivity of the file system is a separate matter from case sensitivity of your data. Whether 'Smith' = 'smith' is decided by the collation, exactly as on Windows. The default server collation on Linux is SQL_Latin1_General_CP1_CI_AS, which is case-insensitive, so table names, column names and comparisons behave as Windows users expect. It is chosen at setup with MSSQL_COLLATION or mssql-conf set-collation, and SQL Server collations and Unicode covers the details.

What is missing on Linux#

Microsoft keeps an official list of features not supported on Linux, and it changes between versions and cumulative updates, so check it for your exact build. As of SQL Server 2022, the main gaps are:

Things that were missing early and now exist include SQL Server Agent itself (on paid editions), full-text search (a separate mssql-server-fts package), Database Mail, distributed transactions through MSDTC, Active Directory authentication with extra setup, and Always On availability groups using Pacemaker rather than Windows Server Failover Clustering.

One security difference matters for imports. On Linux, the bulkadmin fixed server role is not supported, so BULK INSERT and OPENROWSET(BULK ...) need sysadmin. A login with only database rights cannot load files from the server's disk; use bcp or an application-side bulk copy over the network instead, which sqlcmd and bcp covers.

Windows authentication is the other habit to drop. A hosted Linux instance uses SQL authentication: a login name and password, sa among them. Connection strings with Integrated Security=true or Trusted_Connection=yes will not work against it.

Running SQL Server on Linux yourself#

On a machine you control, the supported platforms are Red Hat Enterprise Linux, SUSE Linux Enterprise Server and Ubuntu (check Microsoft's current list for exact versions, as it moves with each release), plus the official container image. The container is by far the quickest way to get a local instance for development:

$ docker run -d --name sql2022 \    -e ACCEPT_EULA=Y \    -e MSSQL_SA_PASSWORD='Choose-a-long-one-9' \    -e MSSQL_PID=Express \    -p 1433:1433 \    -v sqldata:/var/opt/mssql \    mcr.microsoft.com/mssql/server:2022-latest

The password must meet the default policy - at least eight characters from three of the four classes of upper case, lower case, digits and symbols - or the container starts and immediately exits with a password error in its log. Mount a volume at /var/opt/mssql, or the databases vanish with the container. Since SQL Server 2019 the image runs as a non-root mssql user, so a bind-mounted directory needs to be writable by that user ID.

On the CPU side, SQL Server for Linux needs x86-64. There is no native ARM build, which is worth knowing before buying an ARM development laptop or an ARM server.

On RE:NODE, the SQL Server line runs SQL Server 2022 Express on Linux in its own container. The sa password is generated, a database is created for you and set as sa's default, and SSMS connects with the server name written as host,port with a comma. You work through T-SQL and the tools you already have; the instance-level configuration is handled for you.

Updates and versions on Linux#

On Windows, cumulative updates arrive as installers, often through Windows Update. On Linux, SQL Server is an ordinary package from Microsoft's repository, and an update is a package upgrade followed by a service restart:

$ sudo apt-get update$ sudo apt-get install mssql-server     # Ubuntu$ sudo yum update mssql-server          # Red Hat

The repository you register decides the major version and the update channel. Microsoft publishes a cumulative-update repository and a GDR repository (security fixes only) for each major version, and you choose one when adding it. Rolling back means installing a specific earlier package version, which the package manager will do if you name it - something the Windows installer has never made as easy.

In containers, the image tag is the version. mcr.microsoft.com/mssql/server:2022-latest moves with every cumulative update, which is convenient for development and a poor idea for anything you care about, because a re-pulled container can quietly change build. Pin a specific cumulative-update tag in production and upgrade on purpose. The database files on the volume are upgraded in place the first time a newer build opens them, and like a restored backup, they cannot then be opened by an older build. Take a backup before every upgrade, which is the same rule as on Windows and as important.

To see what you are actually running, from any client:

SELECT @@VERSION;SELECT SERVERPROPERTY('ProductVersion')     AS version,       SERVERPROPERTY('ProductUpdateLevel') AS cu,       SERVERPROPERTY('Edition')            AS edition;

@@VERSION on Linux names the distribution as well as the build, which is the quickest way to confirm that a hosted instance is Linux and which edition it is.

What changes for your application#

Usually nothing. Drivers speak the same TDS protocol to the same engine, so a connection string that works against Windows works against Linux once it points at the right host and port and uses SQL authentication. The details worth checking:

• Encryption defaults. Recent drivers - Microsoft.Data.SqlClient 4.0 and later, ODBC Driver 18, the version 18 command-line tools - encrypt by default and validate the certificate. A self-signed certificate then fails validation; either trust it explicitly (TrustServerCertificate=True) or install a proper one. SQL Server connection strings has the exact keywords per driver.
• The port. Hosted instances often listen on a non-default port, and SQL Server's syntax for it is a comma: db.example.net,14330, not a colon.
• File paths in your code. Anything that builds BACKUP, BULK INSERT or CREATE DATABASE statements with C:\ paths needs Linux paths.
• Anything using `xp_cmdshell`, OLE automation or unsafe CLR. It has to move out of the database into the application or a script.

Moving an existing database across is a normal backup and restore, with WITH MOVE pointing every file at a Linux path; migrating a database to SQL Server hosting walks through it.

Troubleshooting a Linux instance#

The service will not start after a configuration change. Read /var/opt/mssql/log/errorlog and journalctl -u mssql-server. The usual causes are a memory limit lower than the engine's minimum, a TLS certificate file the mssql user cannot read, or a data directory with the wrong owner.

Operating system error 5 or 13 on BACKUP or RESTORE. Permissions. The mssql user must be able to read the backup file and write to the target directory.

Cannot connect remotely. Check that the port is listening (ss -ltnp | grep 1433), that the firewall allows it, and that the client uses a comma for the port. "Named Pipes Provider" errors from a client mean it never reached the server at all.

Login failed for user 'sa'. On Linux there is no Windows authentication fallback. Reset the password with mssql-conf set-sa-password while the service is stopped, on a machine you control; on a hosted server, use the password the host generated.

FAQ#

Is SQL Server on Linux slower than on Windows?

Not in any way you will notice for an application database. It is the same engine with the same optimiser, and Microsoft supports both equally. Storage speed, memory and query design make a far bigger difference than the operating system.

Can I use SSMS with SQL Server on Linux?

Yes. SSMS runs on Windows and connects over the network to an instance on any platform. A handful of SSMS features that rely on Windows on the server, such as some Agent job step types, will not apply, but browsing, querying, backups and execution plans all work.

Do I need a licence for SQL Server Express on Linux?

No. Express is free for production use on Linux as on Windows, within its limits of 10 GB per database, four cores and about 1.4 GB of buffer pool memory. Developer edition is free too, but only for development and testing.

Does SQL Server on Linux support Windows authentication?

Only with extra Active Directory configuration on the Linux machine. Hosted instances use SQL authentication with a login and password, which every driver supports. Remove Trusted_Connection and Integrated Security from your connection strings.

Are table names case-sensitive on Linux?

Not by default. Object names follow the database collation, which defaults to a case-insensitive one, so Orders and orders are the same table. File paths, however, are case-sensitive because they belong to the Linux file system.