Configure and manage SQL Server on Linux

SQL Server Configuration Manageris a tool to manage the services associated with SQL Server running on Windows. To configure and manage SQL Server on Linux we use mssql-conf. mssql-conf is a configuration script that installs with SQL Server vNext (Linux). Latest release and documentation available here -> https://docs.microsoft.com/en-us/sql/linux/.

The tool is installed in the following directory path /opt/mssql/bin let’s explore what mssql-conf is all about.
Open a Terminal session and see what type of help and information we get…

Proving the -h or –help argument will return more information about how to use mssql-conf
cd /opt/mssql/bin
./mssql-conf -h

This command gives us various the options/arguments that we can use with the mssql-conf tool.

Now Let’s explore the supported configurable settings by issuing the following command
./mssql-conf list

The following are the supported settings that can be changed/modified using the ‘set’ option:

• network.tcpport – Change the TCP port
• filelocation.defaultbackupdir – Change the default backup directory location
• filelocation.defaultdumpdir – Change the default dump directory location
• filelocation.defaultlogdir – Change the default log directory location
• filelocation.defaultdatadir – Change the default data directory location
• hadr.hadrenabled – Enable Always On Availability Groups
• coredump.coredumptype – Choose the type of dump memory dump file to collect
• coredump.captureminiandfull – Specify whether to generate both mini dumps and full dumps

Choosing two settings to demonstrate

How to change the default TCP port
This option will let you change the TCP port where SQL Server will listen for connections. By default, this port is set to 1433.

To change the port, run the following commands:
sudo /opt/mssql/bin/mssql-conf set tcpport <<em>new_tcp_port</em>>

Restart the service
sudo systemctl restart mssql-server

And test…example
sqlcmd -S localhost,<<em>new_tcp_port</em>> -U test -P test

Change the default backup directory location
By default, the backup files are generated in /var/opt/mssql/data. I personally like to store my backups in a directory named ‘backup’ 😉 So we will need to create a new directory (folder) and then change the default backup directory.

Let’s do this…

We are going to create a new directory, we need to have root privileges so issue the following command and supply your password:
sudo su

Next we change directory -> cd /var/opt/mssql
List files and directories -> ls -l backup directory does not exist!
Create a new directory named ‘backup’ -> mkdir /var/opt/mssql/backup
Validate that new directory was created -> ls -l

Now that we have our new backup directory we can change the directory where SQL Server will send the backup files by default:

/opt/mssql/bin/mssql-conf set defaultbackupdir /var/opt/mssql/backup

You also need to restart the mssql-server service:

systemctl restart mssql-server

To see the content of the mssql.conf file, you use the cat command which stands for “catenate”. It reads data from files, and outputs their contents. It is the simplest way to display the contents of a file at the command line.

cat /var/opt/mssql/mssql.conf

Again for further details on how to configure SQL Server on Linux with the mssql-conf tool see the following link -> https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-configure-mssql-conf.

Enjoy!