PostgreSQL for the SQL Server Pro

Understanding SQL PAL architecture and spinning up a local SQL Server container playground

Tim Chapman

12 min read

·

Aug 28, 2026

--

1

Listen

Share

Press enter or click to view image in full size

About 10 years ago I was traveling to Redmond to attend an internal Microsoft training event called TechReady. These were huge events, with thousands of engineers attending to be up-skilled by the various Product Groups for their technology focus. Amazing events. I remember there was a pre-event full-day (or maybe 2 days, I can't remember) training session with the title something along the lines of ‘Project Helsinki’. It sounded neat, though I had really no idea what it was. But it was a reason to be out in Redmond early during July, so I was all for it. Little did I know I was signing up to learn how to run SQL Server on Linux! At that time, it seemed like an insane concept.

For more than two decades, SQL Server was deeply and unapologetically tied to the Windows NT kernel. It relied on Windows APIs, the Windows security model, and Windows clustering. The idea of running SQL Server on Red Hat, Ubuntu, or inside a Linux container seemed practically impossible without rewriting millions of lines of code. (Honestly — around that time I had no clue what a container was.)

Yet in late 2017, Microsoft shipped SQL Server 2017 (developed under that fun project name “Helsinki”), running natively on Linux or in Docker containers. Today, running SQL Server on Linux containers is one of the most effective ways to stand up isolated test instances, validate query performance, and run continuous integration pipelines without spinning up heavy virtual machines.

Since this series is focusing on translating concepts between SQL Server and PostgreSQL, we need a solid playground for both engines. We previously looked at spinning up PostgreSQL in a container. SQL Server in a Linux container is just as fast to stand up, but how it works under the hood is a totally different story.

How can SQL Server run on Linux?

To understand how SQL Server runs on Linux we first must dig into the engineering challenge Microsoft faced to make it so. SQL Server is a MASSIVE code-base, containing tens of millions of lines of C and C++ (and, in some cases, Assembler). You’d be shocked how much code gets executed to do even the simplest things.

So, rewriting the storage engine, query optimizer, and execution engine to use native POSIX system calls would have taken a decade and introduced enough difficult challenges that porting it likely wouldn’t have been worth it. Maintaining two completely separate code-bases — one for Windows and one for Linux — would have been an engineering nightmare.

Note: POSIX stands for Portable Operating System Interface and is a set of rules for system calls, APIs and OS behaviors — most commonly used with the Linux and Unix operating systems.

Instead of a rewrite, Microsoft used a layered architecture built on two key components: the SQL Operating System (SOS) and the Platform Abstraction Layer (SQL PAL), inspired by Microsoft Research’s Project Drawbridge. (For additional architectural background, see Microsoft’s SQL Server on Linux: How? Introduction).

1. SQL Operating System (SOS)

Back in SQL Server 2005, Microsoft introduced SQLOS (later known as SOS). SQL Server stopped relying directly on the Windows kernel for day-to-day thread scheduling, memory node management, and asynchronous I/O completion. Instead, SOS took over cooperative non-preemptive scheduling, memory allocation tracking, and resource management within user mode.

Note: Operating systems use preemptive scheduling, meaning the OS kernel forcibly interrupts threads to ensure no single application monopolizes the CPU. In contrast, the SQL Operating System (SOS) uses cooperative (non-preemptive) scheduling within its user-mode engine. Worker threads voluntarily yield the CPU once they hit their quantum or if they need to wait for a resource(like waiting for a lock, latch, or disk I/O) so the engine can manage query concurrency with minimal OS context-switching overhead.

Because the SQL Server relational engine already talked to SOS rather than making raw Win32 calls for scheduling and memory, the top layer of the engine was already mostly decoupled from the underlying operating system.

2. The Platform Abstraction Layer (SQL PAL)

Instead of rewriting millions of lines of code to speak Linux, Microsoft created a layer between SQL Server and Linux, named SQL PAL (Platform Abstraction Layer). Think of SQL PAL as a lightweight, in-process translation layer. It sits directly inside the database process to provide the exact Windows APIs the engine expects, while seamlessly translating those requests into native Linux system calls underneath.

As far as the core SQL Server engine is concerned, nothing has changed. When the engine allocates memory, reads a data file from disk, or coordinates worker threads, it makes the exact same calls it has always made. SQL PAL intercepts those requests directly inside the process address space and works with a thin Linux host extension to translate them into native Linux operations:

• Memory Management: Memory requests and page reservations are mapped directly to native Linux memory allocation.
• Disk I/O: Asynchronous database reads, log writes, and page flushes are routed straight to the native Linux filesystem.
• Threading & Locking: SQL Server worker threads and internal locks map straight to real Linux threads and native OS locks.

Because SQL PAL runs as native compiled code directly inside the SQL Server process, you aren’t running an emulator like Wine or a virtual machine. The core engine executes as native machine code, while SQL PAL simply handles the operating system calls underneath.

The Result: Single-Process Execution

Because of this abstraction layer, the entire database engine runs on Linux as a single, multi-threaded process.

If you are familiar with PostgreSQL, this might seem kinda weird. PostgreSQL uses a multi-process architecture — spawning a separate child PostgreSQL process for every client connection. Because of that, database engineers often assume Linux databases only use processes and that threads are a Windows-only concept.

In reality, Linux has first-class kernel multi-threading. When SQL Server spins up worker threads, it uses the Linux clone() system call to create real OS threads that share the exact same process memory space.

(We will dig much deeper into the world of threads vs. processes architecture in a future post, but for our purposes here it is helpful to know that SQL Server’s multi-threaded engine design translates directly to native Linux threads).

What Works, What Doesn’t, and some Caveats

To understand what works and what doesn’t on Linux, it helps to understand what Win32 actually is. Win32 is the core application programming interface (API) that Windows software has used for decades to interact with the operating system — handling low-level duties like memory allocation, file access, security tokens, and thread scheduling.

Because SQL PAL provides the essential Win32 surface area directly inside the Linux process, the core relational engine runs identically on Linux. The query optimizer, storage engine, transaction logging, indexing, and T-SQL execution all behave exactly as they do on Windows.

However, over its long history on Windows, SQL Server also built dependencies on external Windows operating system features outside the core engine — like Windows clustering services, Active Directory domain joins, MAPI mail subsystems, and proprietary filesystem drivers. Those external Windows components don’t exist on Linux, which leads to a few important caveats.

What Works Identically

• Core Database Engine: Full T-SQL support, Clustered and Non-Clustered Indexes, Query Optimizer, and Execution Engine.
• Advanced Storage and Engine Features: In-Memory OLTP (Hekaton), Clustered and Non-Clustered Columnstore Indexes, Table Partitioning, Data Compression, and Query Store.
• Security: Transparent Data Encryption (TDE), Always Encrypted, Row-Level Security, and Dynamic Data Masking.
• High Availability: Always On Availability Groups. On standalone Linux VMs, AG failover is orchestrated by Pacemaker and Corosync instead of Windows Server Failover Clustering (WSFC). In container environments, you can run clusterless (read-scale) Availability Groups.
• Tooling & Observability: Dynamic Management Views (DMVs), Dynamic Management Functions (DMFs), Extended Events (XEvents), and SQL Server Agent for background jobs.
• Backup and Restore: Database backup files (.bak) are fully binary-compatible. You can take a native backup of a database running on SQL Server for Windows and restore it directly onto SQL Server on Linux, and vice versa.

What Is Not Supported or Has Caveats

• FileStream and FileTable: These rely on Windows-specific filesystem drivers that don’t exist on Linux, making both features completely unsupported.
• Active Directory in Containers: While supported on Linux VMs using Kerberos, setting up domain authentication in local containers adds a lot of extra hassle. For a local container sandbox, standard SQL logins (like sa) are the easiest way to go.
• Extended Stored Procedures & xp_cmdshell: Legacy Windows C++ DLLs (sp_addextendedproc) won’t load on Linux. If you enable xp_cmdshell, it executes a Linux /bin/sh shell inside the container instead of Windows cmd.exe.
• Database Mail: Database Mail works on Linux, but configuration must be scripted via T-SQL stored procedures (msdb.dbo.sp_send_dbmail and sysmail_add_account_sp) rather than the SSMS graphical wizard.
• SSAS and SSRS: Analysis Services and Reporting Services remain Windows-only and aren’t included in Linux packages or container images.
• File Path Case Sensitivity: While SQL Server table and column names are typically case-insensitive, the Linux filesystem is strictly case-sensitive. If you run a RESTORE or ATTACH command with mismatched casing in the path (MyDb.mdf vs mydb.mdf), Linux will return a file-not-found error.

Spinning up SQL Server in a Container

Running SQL Server in a container is the fastest way to get an isolated sandbox up and running on your local machine. We will use Podman on Linux, but the commands work almost identically with Docker.

If you just need a quick, throwaway instance for running test queries:

# Using Podman:
podman run --name sql-playground \
-e "ACCEPT_EULA=Y" \
-e "MSSQL_SA_PASSWORD=Password12345" \
-e "MSSQL_PID=Developer" \
-p 1433:1433 \
-d mcr.microsoft.com/mssql/server:2025-latest

Here is a breakdown of what these flags do:

• — name sql-playground: Assigns a friendly name so you don’t have to manage long container IDs.
• -e “ACCEPT_EULA=Y”: Confirms your acceptance of the End User License Agreement. The container will refuse to start without this.
• -e “MSSQL_SA_PASSWORD=Password12345”: Sets the system administrator (sa) password. SQL Server enforces password complexity (minimum 8 characters, uppercase, lowercase, numbers, and symbols).
• -e “MSSQL_PID=Developer”: Specifies the edition. The default is Developer (which provides full Enterprise Edition feature parity for development and testing). You can also specify Express, Standard, Enterprise, or a commercial product key.
• -p 1433:1433: Maps port 1433 inside the container to port 1433 on your host.
• -d: Runs the container detached in the background.
• mcr.microsoft.com/mssql/server:2025-latest: Pulls the official SQL Server 2025 container image from the Microsoft Container Registry (MCR).

(Note: SQL Server has a hard minimum requirement of 2GB (2048 MB) of RAM to start up. If your container engine or virtual machine is constrained below 2GB, the container will exit immediately on launch).

2. Adding Persistent Storage and the Non-Root User Gotcha

As I mentioned in my previous post about setting up PostgreSQL in a container, container storage is ephemeral. If you delete the container, your databases disappear. To persist your data across container updates, you need to mount a host directory to `/var/opt/mssql`.

Note on Linux Paths: If you’re wondering why SQL Server lives in /var/opt/mssql, Linux follows a standard directory structure. Third-party application binaries live in /opt — think of /opt/mssql as your C:\Program Files folder for the engine executables. Variable runtime data like database files, transaction logs, and error logs live in /var/opt — think of /var/opt/mssql as your MSSQL\DATA and MSSQL\Log directories.

Starting in SQL Server 2019 CU3, Microsoft updated container images to run as an unprivileged, non-root user (`mssql`, UID 10001). When you mount a local host folder, that directory is owned by your host user account. If the container process cannot write to it, SQL Server will fail to create `master.mdf` and `tempdb.mdf`, and the container will exit immediately.

In Linux, changing directory ownership normally requires running the `chown` (change owner) command with `sudo`. With rootless Podman, you don’t have to manually manage host permissions — you handle this automatically by adding `:Z,U` to the volume flag:

Here is how to set up persistent storage properly:

# 1. Create a local directory on your host for SQL Server files
mkdir -p ~/sqldata

# 2. Spin up the container with the volume mount and memory limit
podman run --name sql-playground \
-e "ACCEPT_EULA=Y" \
-e "MSSQL_SA_PASSWORD=Password12345" \
-e "MSSQL_PID=Developer" \
-e "MSSQL_MEMORY_LIMIT_MB=4096" \
-v ~/sqldata:/var/opt/mssql:Z,U \
-p 1433:1433 \
-d mcr.microsoft.com/mssql/server:2025-latest

Details of note in this command:

1. -v ~/sqldata:/var/opt/mssql:Z,U: Mounts the host folder to the container’s data directory. The :U flag tells Podman to automatically run a recursive change owner (chown) on the host directory to match the unprivileged mssql service account (UID 10001) inside the container namespace, while :Z configures private SELinux labeling on systems like RHEL, Fedora, and CentOS.

(Note for Docker users: The :U flag is a Podman-specific extension. If you are using Docker instead of Podman, manually set permissions on your host directory first by running sudo chown -R 10001:0 ~/sqldata, then mount it using standard :z syntax: -v ~/sqldata:/var/opt/mssql:z).

2. -e “MSSQL_MEMORY_LIMIT_MB=4096”: Tells SQL Server’s internal memory manager to cap memory consumption at 4GB. Inside containers, host memory detection can occasionally misread total host RAM, so setting an explicit limit is a best practice.

Lets double-check to make sure our container is running:

# Have a look at all containers running on the machine
podman ps -a

Press enter or click to view image in full size

Connecting via Command Line

Our container has sqlcmd already available, so we can call it via podman exec to connect. From there I just run a simple SELECT @@VERSION to get the version of the SQL Server instance.

podman exec -it sql-playground /opt/mssql-tools18/bin/sqlcmd \
-S localhost -U sa -P "Password12345" -C

(Note: In modern versions of sqlcmd and ODBC Driver 18, encryption is enabled by default. The -C flag tells sqlcmd to trust the server certificate for local testing).

Press enter or click to view image in full size

Connecting via SQL Server Management Studio (SSMS)

Time to give our trusty tool, SQL Server Management Studio, a try. Here we are running SQL Server on Linux, but want to connect via SSMS on Windows. To make this easy, I’ll just connect via the IP of my Linux machine.

In the SSMS Connect to Server dialog, set:

• Server type: Database Engine
• Server name: either the name of your Linux machine or its IP address. In my case it is 172.24.8.250 (yours will almost certainly be different).
• Authentication: SQL Server Authentication
• Login: sa
• Password: Password12345 (or the password configured in MSSQL_SA_PASSWORD).
• Certificate Trust: In SSMS 19 and SSMS 20, encryption is required by default. Because the container generates a self-signed certificate during startup, click the Options button and check the “Trust server certificate” box (or set Encryption to Optional). If you skip this, SSMS will refuse to connect because the certificate is not signed by a trusted root authority.

Once connected, Object Explorer works identically to a Windows installation. You can browse databases, open Activity Monitor, check SQL Server Agent jobs, manage logins, view server properties, and run queries.

Lets run some Queries

Once connected, let’s run a few diagnostic queries to see how the engine views its Linux host.

First, lets check out the version of SQL Server. Here we can see this is running on Ubuntu Linux. This should return the same as the call from podman exec.

SELECT @@VERSION
GO

Press enter or click to view image in full size

Next, lets query the DMV sys.dm_os_host_info to view the host details. Similar information to what was returned from @@VERSION, but with the addition of the host CPU architecture.

SELECT
  host_platform,
  host_distribution,
  host_architecture
FROM sys.dm_os_host_info;
GO

Lastly, lets query sys.dm_os_sys_info for operating system info. One neat thing this query returns is the fact that this version of SQL is specifically running on a Linux Container. Very cool!

SELECT
  cpu_count,
  hyperthread_ratio,
  physical_memory_kb / 1024 AS physical_memory_mb,
  committed_kb / 1024 AS committed_mb,
  container_type_desc
FROM sys.dm_os_sys_info;
GO

Wrap Up

SQL Server in a Linux container runs the exact same relational engine, query optimizer, and buffer pool as Windows. By decoupling the engine with the SQL Operating System (SOS) and routing OS calls through SQL PAL, Microsoft brought the full database engine to Linux without maintaining two separate code-bases.

Here is what we covered:

• The Architecture: SQL Server talks to SQL PAL within its user-mode address space, while a thin Linux host extension maps memory, disk I/O, and threading directly to native Linux operations.
• The Process Model: Unlike PostgreSQL’s multi-process architecture, SQL Server runs on Linux as a single process (sqlservr) powered by native Linux kernel threads created via clone().
• Container Setup & Permissions: Spinning up an instance with Podman or Docker is fast, but persistent volumes require setting ownership to UID 10001 (mssql) because modern container images run non-root by default.
• Engine Parity: The core engine, indexing, T-SQL syntax, and diagnostic views are identical. Querying sys.dm_os_host_info confirms the Linux host, while sys.dm_os_sys_info explicitly identifies the container runtime.