SQL Server on Linux – Configuration (Ubuntu)

Following on from my previous post where we installed SQL Server with the default configuration this article goes into more detail regarding the configuration options available on Linux to customize your SQL Server environment.

• Installing SQL Server Agent
• Changing Default Directories
• Enabling Availability Groups for High Availability
• Configure SQL Server Memory Settings
• Configuring the TCP port for SQL Server
• Enabling and Disabling Traceflags
• Removing/Reverting a Setting back to default
• Viewing Non Default Settings

SQL Server Agent Install

The first configuration option we are going to look at is installing the SQL Server Agent. Although you could use something like CRON to automate jobs on Linux most SQL Server DBA will be more comfortable with SQL Server Agent.

1. Update Local Repositories

 sudo apt-get update 

2. Install SQL Server Agent

 sudo apt-get install mssql-server-agent 

3. Restart SQL Server

 sudo systemctl restart mssql-server 

Next Steps: For more information on creating jobs a great tutorial can be found by clicking here

SQL Server Default Data and Log File Directories

Now we will change the default directory for user database and log files. In this scenario, i am moving the default data directory from /var/opt/mssql/data to /opt/mssql/data and the default log directory from  /var/opt/mssql/data to var/opt/mssql/logs.

NOTE: Before Making these changes in a production environment it is recommended to take backups of all databases.

1. Create the folders required to store newly created databases and assign correct permissions

sudo mkdir /opt/mssql/data
sudo mkdir /opt/mssql/logs
#Change owner and group to mssql user
sudo chown mssql /opt/mssql/data
sudo chgrp mssql /opt/mssql/data
sudo chown mssql /opt/mssql/logs
sudo chgrp mssql /opt/mssql/logs

Screen capture below of existing folder structure and files

2. We will now use the mssql-conf script to change the default directories. The mssql-conf script is stored by default in the /opt/mssql/bin directory and is the script to set most of the sql server configuration options.

#execute below command to change the default database data file location.
sudo /opt/mssql/bin/mssql-conf set filelocation.defaultdatadir /opt/mssql/data
#Restart SQL
sudo systemctl restart mssql-server
#Change default log file location
sudo /opt/mssql/bin/mssql-conf set filelocation.defaultlogdir /opt/mssql/logs
#Restart SQL
sudo systemctl restart mssql-server

As you can see when connecting to SQL using SQLCMD and executing a CREATE DATABASE command the files are now stored in the new location

Moving Existing files will be covered in another article

In Summary, you can follow the above procedure for changing the default Dump Directory and Backup Directory too, You just need to make sure the permissions on the folders are set correctly and run the relevant command from below in place of the mssql-conf command above

#Change Default Dump Directory
sudo /opt/mssql/bin/mssql-conf filelocation.defaultdumpdir /opt/mssql/Dump
#Change Default Backup Directory
sudo /opt/mssql/bin/mssql-conf filelocation.defaultbackupdir /opt/mssql/Backup

Enable Availability Groups

To enable availability groups we will again use the mssql-conf script

#Enable HA
sudo /opt/mssql/bin/mssql-conf set hadr.hadrenabled 1
#Restart SQL Server
sudo systemctl restart mssql-server

A great tutorial for configuring availability groups can be found here

Configure SQL Server Memory Settings

You guessed it in order to change the SQL Server memory settings we are going to use mssql-conf script again.

NOTE: This change requires a restart for the changes to take effect as documented here. On windows, this is not the case but for Linux, we need a service restart.

#Change Max server memory to 4GB (4096 MB)
sudo /opt/mssql/bin/mssql-conf set memory.memorylimitmb 4096
#Restart SQL Server
sudo systemctl restart mssql-server

Configuring the TCP port for SQL Server

We are now going to change the default TCP port SQL Server is listening on. Again mssql-conf is used

#Change Default TCP Port
sudo /opt/mssql/bin/mssql-conf set network.tcpport 3333
#Restart SQL Server
sudo systemctl restart mssql-server

NOTE: Now that we have changed the default TCP port you will need to specify the port when connecting. an example is below

sqlcmd -S localhost,3333 -U sa

Enabling and Disabling Traceflags

Enabling Trace flags is easy on Linux it is a one command operation to enable or disable one or many traceflags.

#Enable Trace Flag
sudo /opt/mssql/bin/mssql-conf traceflag 2345 3456 on
#Disable Trace Flags
sudo /opt/mssql/bin/mssql-conf traceflag 2345 3456 off
#Restart SQL Server
sudo systemctl restart mssql-server

Removing/Reverting a Setting back to default

Reverting a setting back to the default setting is also very easy on Linux and can be accomplished by issuing the unset option when calling the mssql-config script
Below is an example of reverting the tcp port settings back to the default

#Change TCP port settings back to default value using unset option
sudo /opt/mssql/bin/mssql-conf unset network.tcpport
#Restart SQL Server
sudo systemctl restart mssql-server

Viewing Non Default Settings

Ok, We have now made a bunch of configuration changes and I would like to see all the non-default changes that I have made.
This is possible with the below command

sudo cat /var/opt/mssql/mssql.conf

NOTE: This will not show default settings.

Summary

I hope this helps in starting to configure your SQL Server estate on Linux. Next post will be SQL Server on Linux – Adminstration (Ubuntu) where we will look at how to administer SQL Server effectively. This will include TempDB configuration, Patching and general commands that will help any SQL server DBA to administer SQL server on Linux.