Installing SQL Server 2019 on Ubuntu 18.04

2020-02-16 · mssql, linux, ubuntu

I rebuilt my Linux test box last week and figured I'd write down the whole sequence, since I keep re-deriving it. SQL Server 2019 is a normal apt package on Ubuntu 18.04, and a fresh server takes about ten minutes. Setup is unchanged from 2017, but a few mssql-conf options are worth setting from the start, and the tools repository changed a bit.

You need Ubuntu 18.04 LTS x64 with at least 2 GB RAM (setup refuses to run with less), 6 GB of free disk, XFS or ext4 for the data directory, sudo, and outbound HTTPS to packages.microsoft.com. Pick the sa password up front: 8 characters minimum, three of the four character classes.

Step 1: Add the repository key and the 2019 repository

wget -qO- https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
sudo add-apt-repository "$(wget -qO- https://packages.microsoft.com/config/ubuntu/18.04/mssql-server-2019.list)"
sudo apt-get update

The repository name matters: mssql-server-2017 and mssql-server-2019 are separate lists, and the mssql-server-preview list carries CTP builds. Make sure only one of them is in /etc/apt/sources.list.d/.

Step 2: Install the engine

sudo apt-get install -y mssql-server

The package installs to /opt/mssql, with data under /var/opt/mssql/data and the error log at /var/opt/mssql/log/errorlog.

Step 3: Run setup

sudo /opt/mssql/bin/mssql-conf setup

Pick the edition (Evaluation, Developer, Express, Web, Standard, Enterprise, or a product key), accept the licence, enter the sa password. For a scripted install:

sudo MSSQL_PID=Developer ACCEPT_EULA=Y MSSQL_SA_PASSWORD='Str0ng!Passw0rd' /opt/mssql/bin/mssql-conf -n setup

MSSQL_PID also accepts an actual product key for licensed editions.

Step 4: Open the firewall and check the service

sudo ufw allow 1433/tcp
systemctl status mssql-server --no-pager

You should see active (running). The log at /var/opt/mssql/log/errorlog contains the usual startup lines, including SQL Server is now ready for client connections.

Step 5: Install the command-line tools

sudo add-apt-repository "$(wget -qO- https://packages.microsoft.com/config/ubuntu/18.04/prod.list)"
sudo apt-get update
sudo ACCEPT_EULA=Y apt-get install -y mssql-tools unixodbc-dev
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' | sudo tee /etc/profile.d/mssql-tools.sh
source /etc/profile.d/mssql-tools.sh

Writing the PATH into /etc/profile.d makes sqlcmd and bcp available to every user, which is handy for cron jobs run by a service account.

Step 6: Baseline configuration

The settings I put on every 2019 Linux instance. I always cap memory, because otherwise SQL Server will happily take most of the box:

# Memory cap in MB (leave ~25% for the OS on a dedicated VM with 8 GB)
sudo /opt/mssql/bin/mssql-conf set memory.memorylimitmb 6144
# Backup, data and log locations
sudo mkdir -p /var/opt/mssql/backup /data/mssql /log/mssql
sudo chown -R mssql:mssql /var/opt/mssql/backup /data/mssql /log/mssql
sudo /opt/mssql/bin/mssql-conf set filelocation.defaultbackupdir /var/opt/mssql/backup
sudo /opt/mssql/bin/mssql-conf set filelocation.defaultdatadir /data/mssql
sudo /opt/mssql/bin/mssql-conf set filelocation.defaultlogdir /log/mssql
# Enable SQL Server Agent (built in since 2017 CU4, no separate package)
sudo /opt/mssql/bin/mssql-conf set sqlagent.enabled true
# Quiet successful-backup messages in the error log
sudo /opt/mssql/bin/mssql-conf traceflag 3226 on
sudo systemctl restart mssql-server

The result in /var/opt/mssql/mssql.conf:

[sqlagent]
enabled = true
[memory]
memorylimitmb = 6144
[filelocation]
defaultbackupdir = /var/opt/mssql/backup
defaultdatadir = /data/mssql
defaultlogdir = /log/mssql
[traceflag]
traceflag0 = 3226

tempdb files are created by setup based on the core count, up to eight, like on Windows. Move them with ALTER DATABASE tempdb MODIFY FILE if you have a dedicated disk.

Does it answer?

sqlcmd -S localhost -U sa -P 'Str0ng!Passw0rd' -Q "SELECT @@VERSION; SELECT SERVERPROPERTY('Edition'), SERVERPROPERTY('ProductLevel');"

Expect Microsoft SQL Server 2019 (RTM-CU2) or similar and on Linux (Ubuntu 18.04.4 LTS). Create a database and run a backup to confirm the default directories:

CREATE DATABASE Test1;
BACKUP DATABASE Test1 TO DISK = N'Test1.bak' WITH COMPRESSION;   -- lands in defaultbackupdir
SELECT physical_name FROM sys.master_files WHERE database_id = DB_ID('Test1');

From a workstation, connect with SSMS 18.x or Azure Data Studio using the IP or hostname. Both work with SQL authentication out of the box; for Windows authentication the machine must be joined to the domain, which is a separate topic.

Things that went wrong for me

E: Unable to locate package mssql-server. The repository line did not get added, often because software-properties-common (which provides add-apt-repository) is missing on minimal images. Install it and repeat Step 1.
Setup fails with The specified password does not meet SQL Server password policy requirements. Eight characters minimum, three of: uppercase, lowercase, digits, symbols. Also watch for shell quoting; put the password in single quotes.
Service starts, then stops, with This program requires a machine with at least 2000 megabytes of memory in journalctl -u mssql-server. The VM has too little RAM. Note that memory.memorylimitmb does not bypass this check.
sqlcmd -S localhost works but a remote SSMS gets A network-related or instance-specific error occurred. Port 1433 is closed at ufw, a cloud security group, or the client cannot resolve the hostname. nc -zv sqlhost 1433 from the client pins it down quickly.
Login failed for user 'sa' right after setup. The sa password was typed with a trailing character due to shell escaping. Reset with sudo /opt/mssql/bin/mssql-conf set-sa-password (stop the service first).

After that it's the same as any other instance. Schedule backups through the Agent, and update with apt-get update && apt-get install mssql-server when a cumulative update comes out. I read the release notes first.