Managing SQL Server on Linux with mssql-conf

2020-03-26 · mssql, linux, mssql-conf, ubuntu

The first time I installed SQL Server on Linux, I spent twenty minutes looking for Configuration Manager. There isn't one. No registry, no MMC snap-in. The TCP port, default file locations, startup trace flags, collation and the Agent switch are all handled by one command-line tool, mssql-conf, which writes /var/opt/mssql/mssql.conf for the engine to read at startup.

These are the settings I touch on every SQL Server 2017 or 2019 install on Ubuntu 16.04/18.04 and RHEL/CentOS 7. I assume mssql-server is already installed from the Microsoft repository, and that you have sudo. Most changes need a restart, so warn people if the instance is live.

Initial setup

Right after the package install the service is not running. The setup subcommand picks the edition, accepts the EULA and sets the sa password:

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

For an unattended install, the same is driven by environment variables:

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

MSSQL_PID accepts Evaluation, Developer, Express, Web, Standard, Enterprise or a product key. Check the result:

systemctl status mssql-server --no-pager

Port and default directories

Every setting is set <section>.<key> <value>. These come first for me:

sudo /opt/mssql/bin/mssql-conf set network.tcpport 1450
sudo /opt/mssql/bin/mssql-conf set filelocation.defaultdatadir /data/mssql/data
sudo /opt/mssql/bin/mssql-conf set filelocation.defaultlogdir /data/mssql/log
sudo /opt/mssql/bin/mssql-conf set filelocation.defaultbackupdir /backup/mssql
sudo /opt/mssql/bin/mssql-conf set filelocation.defaultdumpdir /var/opt/mssql/dumps

The directories must exist and be owned by mssql before the restart, or the engine can't create files there. I forget this one about every second time:

sudo mkdir -p /data/mssql/data /data/mssql/log /backup/mssql
sudo chown -R mssql:mssql /data/mssql /backup/mssql
sudo chmod 750 /data/mssql/data /data/mssql/log

Changing the default data directory does not move the existing system databases; it only affects where new databases go. Moving master and friends is a separate procedure using filelocation.masterdatafile and filelocation.masterlogfile after physically moving the files.

Memory limit and trace flags

By default SQL Server on Linux takes 80 percent of physical memory. On a small VM shared with other services, set a hard limit in MB:

sudo /opt/mssql/bin/mssql-conf set memory.memorylimitmb 6144

This is the equivalent of max server memory, and both work; the mssql-conf value acts as a ceiling for the whole process, including memory outside the buffer pool.

Startup trace flags use a dedicated subcommand instead of set:

sudo /opt/mssql/bin/mssql-conf traceflag 3226 on
sudo /opt/mssql/bin/mssql-conf traceflag 3226 off

Trace flag 3226 suppresses successful backup messages in the errorlog, which on a Linux box with frequent log backups keeps journalctl readable.

SQL Server Agent

On SQL Server 2017 the Agent ships in a separate package (mssql-server-agent) and has to be enabled; from 2017 CU4 onward it is part of the main package but still disabled by default. On 2019 it is included and enabled with the same key:

sudo /opt/mssql/bin/mssql-conf set sqlagent.enabled true
sudo systemctl restart mssql-server

Without this, msdb has jobs but nothing runs them, and SSMS shows the Agent node with a red mark.

Collation, sa password and other one-offs

The server collation can only be changed while there are no user databases, so do it right after setup:

sudo systemctl stop mssql-server
sudo /opt/mssql/bin/mssql-conf set-collation

It prompts for the collation name (SQL_Latin1_General_CP1_CI_AS is the default). Resetting a forgotten sa password also happens here, with the service stopped:

sudo systemctl stop mssql-server
sudo MSSQL_SA_PASSWORD='N3wStr0ngPass!' /opt/mssql/bin/mssql-conf set-sa-password
sudo systemctl start mssql-server

Other keys worth knowing: network.forceencryption 1 together with network.tlscert and network.tlskey for TLS, hadr.hadrenabled 1 for Availability Groups, coredump.coredumptype to control dump sizes, and telemetry.customerfeedback false.

Review and restart

list shows every supported key; validate checks the file for typos before you restart:

sudo /opt/mssql/bin/mssql-conf list
sudo /opt/mssql/bin/mssql-conf validate
sudo cat /var/opt/mssql/mssql.conf
sudo systemctl restart mssql-server

The file itself is plain INI and can be edited directly or dropped in by configuration management, as long as the ownership stays mssql:mssql:

[sqlagent]
enabled = true

[filelocation]
defaultdatadir = /data/mssql/data
defaultlogdir = /data/mssql/log
defaultbackupdir = /backup/mssql

[memory]
memorylimitmb = 6144

[network]
tcpport = 1450

[traceflag]
traceflag0 = 3226

Did it take?

sudo ss -tlnp | grep sqlservr
sqlcmd -S localhost,1450 -U sa -P "$SQLPASS" -Q "SELECT SERVERPROPERTY('Collation'), (SELECT value_in_use FROM sys.configurations WHERE name = 'max server memory (MB)'); EXEC sp_readerrorlog 0, 1, N'listening'"
sqlcmd -S localhost,1450 -U sa -P "$SQLPASS" -Q "CREATE DATABASE conftest; SELECT physical_name FROM conftest.sys.database_files; DROP DATABASE conftest"

The new database's files should appear under /data/mssql/data and /data/mssql/log, and the listener should be on port 1450.

Things that bit me

The service fails to start after changing a directory, and journalctl -u mssql-server shows "Error: 17204 ... Could not open file ... Permission denied" or "The specified directory does not exist". Fix ownership with chown -R mssql:mssql on the directory, or create it, then start again.
"Login failed for user 'sa'" right after set-sa-password. The service was running while the password was set. Stop it, set the password, start it.
"This setting can only be modified when SQL Server is stopped" from set-collation or set-sa-password. Exactly that; systemctl stop mssql-server first.
After enabling the Agent on 2017 pre-CU4, sqlagent.enabled is accepted but the Agent still does not start: install the mssql-server-agent package, or better, update to a current CU where it is built in.
Setting memory.memorylimitmb too low (below about 2000) leads to "There is insufficient system memory in resource pool 'internal'" errors during startup. Raise the value; 2 GB is the practical minimum for the engine itself.

One upside: a plain-text mssql.conf is far easier to keep in version control than a registry export.