Dealing with SQL Server on Linux on WSL2

This is a blog post I intended to write sometime ago … about using SQL Server on Linux on WSL2. For whom already installed it on Windows 10, version 2004, you are already aware it doesn’t come with the support of systemd. Indeed, although it does exist in the file system, systemd is not running. If you intend to use directly SQL Server on WSL2, you need to read this carefully, because installation and management relies precisely on systemd!

163 - 0 - banner

First, let’s say if you want to run SQL Server on WSL2 in a supported way, docker containers remain probably the better fit. Podman may be also an alternative but it seems to remain an experimental stuff so far. The interesting point is that some people prefer to use Docker directly on WSL2 whereas other ones prefer to use Docker Desktop for Windows with the integration of WSL2 based engine. The option seems to be available for a few months as shown below:

163 - 1 - Docker Desktop WSL2

But this is not the main topic of this blog post. Having interesting discussions with some of my colleagues, we wanted to know if we were able to install directly SQL Server on WSL2 and the good news is we finally managed to do it but at the cost of some (dirty) tricks required to make SQL Server starting / stopping correctly.

Before continuing, let’s precise we did it in a pure experimental and academic way and this is definitely not supported by Microsoft. But before starting my holidays in August, it was something very fun and a good reminder of some Linux concepts …

I did my test with the Ubuntu-18.04 distro in WSL v2.

163 - 2 - WSL2 Ubuntu18.04

First step consisted in installing SQL Server in an usual way as per the Microsoft BOL. The installation was done correctly but after running the setup, an error message raised quickly in the last step related to service initialization:

163 - 3 - install sql

In fact, if you think about it, this message is expected because systemd is not running on WSL2 and the configuration process attempts to initialize the mssql-server service based on systemd. But let’s dig further on the installation process … Referring to how a deb package is made, there exist preinst, postinst, prerm, postrm scripts for mssqlserver deb package located in /var/lib/dpkg/info/:

$ sudo ls -l /var/lib/dpkg/info/mssql-server*
-rwxr-xr-x 1 root root   108 Jun 16 20:01 /var/lib/dpkg/info/mssql-server.config
-rw-r--r-- 1 root root  8579 Jul 26 00:31 /var/lib/dpkg/info/mssql-server.list
-rw-r--r-- 1 root root 11543 Jun 16 20:01 /var/lib/dpkg/info/mssql-server.md5sums
-rwxr-xr-x 1 root root  1436 Jun 16 20:01 /var/lib/dpkg/info/mssql-server.postinst
-rwxr-xr-x 1 root root   289 Jun 16 20:01 /var/lib/dpkg/info/mssql-server.postrm
-rwxr-xr-x 1 root root  1353 Jun 16 20:01 /var/lib/dpkg/info/mssql-server.preinst
-rwxr-xr-x 1 root root   365 Jun 16 20:01 /var/lib/dpkg/info/mssql-server.prerm
-rw-r--r-- 1 root root    72 Jun 16 20:01 /var/lib/dpkg/info/mssql-server.shlibs
-rw-r--r-- 1 root root   305 Jun 16 20:00 /var/lib/dpkg/info/mssql-server.templates
-rw-r--r-- 1 root root    74 Jun 16 20:01 /var/lib/dpkg/info/mssql-server.triggers

My suspicion was those files content references of systemctl commands and yes, there were in preinst/prerm/postins/prerm files:

163 - 4 - install files and systemctl dependencies

Well, we found a reasonable explanation of the previous error message at least. At this stage, I would say you can use your new fresh installed SQL Server instance, but it requires to manually start / stop it because there are no systemctl commands to handle it. But thinking about it … we are still using the famous init daemon (PID1) on WSL2 and it works for regular services.

163 - 5 - top tree init

A good alternative could be to rely on init scripts and start-stop-daemon wrapped into LSB-compliant init scripts with:

– At least start, stop, restart, force-reload, and status
– Return Proper exit code
– Document run-time dependencies

LSB provides default set of functions which is in /lib/lsb/init-functions and we can make use of those functions in our Init scripts. The script file is located to /etc/init.d and named mssql-server.

$ sudo ls -l /etc/init.d/mssql*
-rwxr-xr-x 1 root root 1606 Jul 26 22:13 /etc/init.d/mssql-server

Here the content of my mssql-server script file:

#! /bin/sh -e
#
### BEGIN INIT INFO
# Provides:          sqlserver
# Required-Start:    $all
# Required-Stop:
# Default-Start:     2 3 4 5
# Default-Stop:      0 1 6
# Short-Description: Manages SQL Server instance on Linux
### END INIT INFO

DAEMON="/opt/mssql/bin/sqlservr"
daemon_OPT=""
DAEMONUSER="mssql"
daemon_NAME="sqlservr"

export PATH=/sbin:/bin:/usr/sbin:/usr/bin:/usr/local/sbin:/usr/local/bin:/var/opt/mssql

# Check sqlserver is present
if [ ! -x $DAEMON ]; then
        log_failure_msg "$DAEMON not present or not executable"
        exit 1
fi

# Load init functions
. /lib/lsb/init-functions


d_start () {
        log_daemon_msg "Starting system $daemon_NAME Daemon"
        start-stop-daemon --background --name $daemon_NAME --start --quiet --chuid $DAEMONUSER --exec $DAEMON --umask 007 -- $DAEMON_OPTS --oknodo
        log_end_msg $?
}

d_stop () {
        log_daemon_msg "Stopping system $daemon_NAME Daemon"
        start-stop-daemon --name $daemon_NAME --stop --retry 5 --quiet --name $daemon_NAME --oknodo
        log_end_msg $?
}

case "$1" in

        start|stop)
                d_${1}
                ;;

        restart|reload|force-reload)
                        d_stop
                        d_start
                ;;

        force-stop)
               d_stop
                ;;

        status)
                status_of_proc "$daemon_NAME" "$DAEMON" "system-wide $daemon_NAME" && exit 0 || exit $?
                ;;
        *)
                echo "Usage: /etc/init.d/$daemon_NAME {start|stop|force-stop|restart|reload|force-reload|status}"
                exit 1
                ;;
esac
exit 0

Thus, I was now able to manage with status / start / stop / restart operations:

$ sudo service mssql-server status
 /opt/mssql/bin/sqlservr is not running
$ sudo service mssql-server start
 Starting system sqlservr Daemon         [ OK ]

The SQL Server engine was started successfully, and I can double check with the top command …

163 - 6 - top sqlservr

… and with a quick connect to my SQL Server instance:

163 - 7 - mssql-cli

As starting my SQL Server instance, stopping it can be performed with the service command and stop:

$ sudo service mssql-server stop
 Stopping system sqlservr Daemon         [ OK ]

$ sudo service mssql-server status
/opt/mssql/bin/sqlservr is not running

Generally speaking, there is no concept of runlevel with WSL, so starting automatically the mssql-service with the WSL2 startup can be achieved in different ways with entries in .bashrc file or tricks from github projects (not tested from my side).

Finally, let’s talk about removing SQL Server from WSL2. As a reminder, during the installation / setup we faced an error message related to systemd. We also identified some dependencies to systemd in some dkpg files as shown below:

163 - 4 - install files and systemctl dependencies

Similar to the installation step, you will experience the same kind of issue with prerm / postrm files when removing SQL Server. In my case, I had to comment concerned lines in those files to uninstall successfully my SQL Server instance.

Hope this blog post helps!

Laisser un commentaire