DB-유

Installing SQL Server 2017 on Ubuntu 16.04

· mssql, linux, ubuntu

I was skeptical about SQL Server on Linux until I put the 2017 release candidate on a spare test box. It's not a demo anymore. Microsoft publishes proper apt repositories for Ubuntu 16.04, so installing is a few commands, and day-to-day administration is almost identical to Windows: same T-SQL, same SSMS, same backup files.

This is the sequence I use on a fresh Ubuntu 16.04 x64 server. You need 2 GB of RAM (setup refuses to run with less), about 6 GB of disk, sudo, and outbound HTTPS to packages.microsoft.com. The current build is Release Candidate 2, so the repository name below is the preview one and may change at general availability.

Add the Microsoft 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/16.04/mssql-server.list)"
sudo apt-get update

If add-apt-repository is missing, install software-properties-common first.

Install the engine

sudo apt-get install -y mssql-server

The package is around 170 MB and installs to /opt/mssql. Data files go under /var/opt/mssql/data and the error log under /var/opt/mssql/log.

Run setup

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

You are asked for the edition (Evaluation, Developer, Express, or a paid edition with a key), to accept the licence terms, and for the sa password. The password must be at least 8 characters with characters from three of the four classes (upper, lower, digit, symbol). Setup then starts the service.

Developer and Express are free; Developer has all Enterprise features but is licensed for non-production use only.

For an unattended install, the same thing can be done with environment variables:

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

Service and firewall

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

The service is a normal systemd unit, so sudo systemctl restart mssql-server and journalctl -u mssql-server work as you would expect.

Command-line tools

sqlcmd and bcp come from a separate repository together with the ODBC driver:

sudo add-apt-repository "$(wget -qO- https://packages.microsoft.com/config/ubuntu/16.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"' >> ~/.bashrc
source ~/.bashrc

mssql-conf tweaks

mssql-conf replaces the parts of SQL Server Configuration Manager that make sense on Linux. I cap memory on every install, because on a small VM the default will happily eat the whole machine. The rest:

# Cap memory: value is in MB
sudo /opt/mssql/bin/mssql-conf set memory.memorylimitmb 3072
# Default backup directory
sudo /opt/mssql/bin/mssql-conf set filelocation.defaultbackupdir /var/opt/mssql/backup
sudo mkdir -p /var/opt/mssql/backup && sudo chown mssql:mssql /var/opt/mssql/backup
# Enable trace flag 3226 to quiet successful backup messages
sudo /opt/mssql/bin/mssql-conf traceflag 3226 on
sudo systemctl restart mssql-server

The settings end up in /var/opt/mssql/mssql.conf, which is a plain INI file:

[sqlagent]
[memory]
memorylimitmb = 3072
[filelocation]
defaultbackupdir = /var/opt/mssql/backup
[traceflag]
traceflag0 = 3226

Is it alive?

Connect locally and confirm the version:

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

You should see Microsoft SQL Server 2017 (RC2) and on Linux (Ubuntu 16.04.3 LTS) in the version string. Then connect from a Windows machine with SSMS 17.x using the server's IP address and the sa login. SSMS 17 is required; 2016's SSMS 16 cannot connect to the Linux build properly.

Create a test database and back it up to make sure the file locations work:

CREATE DATABASE Test1;
BACKUP DATABASE Test1 TO DISK = N'/var/opt/mssql/backup/Test1.bak' WITH COMPRESSION;

Things that bit me

  • The mssql-server service fails to start: Program requires a machine with at least 2000 megabytes of memory. Give the VM more RAM. There is no supported way around this check.
  • sqlcmd: command not found after installing mssql-tools. The PATH line was not sourced; open a new shell or run source ~/.bashrc.
  • Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login timeout expired. from a remote machine. Usually ufw or a cloud security group blocking 1433. Test with nc -zv <server> 1433 from the client.
  • sudo apt-get install mssql-server reports unmet dependencies on a minimal image. Run sudo apt-get install -f and make sure libc++1, libsss-nss-idmap0 and gdb can be resolved from the main Ubuntu repositories.
  • Files owned by root under /var/opt/mssql after copying a backup in with sudo cp. Fix with sudo chown mssql:mssql /var/opt/mssql/backup/*.bak, otherwise RESTORE fails with operating system error 13 (Permission denied).

The Linux-specific part is really just mssql-conf and file permissions. Everything else is the SQL Server you already know.