Pythian Blog: Technical Track

SQL Server on Linux – Configuration (Ubuntu)

by Daniel Holt on Jan 22, 2018 12:00:00 AM

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.

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
[code language="bash"] sudo apt-get update [/code]
  1. Install SQL Server Agent
[code language="bash"] sudo apt-get install mssql-server-agent [/code]
  1. Restart SQL Server
[code language="bash"] sudo systemctl restart mssql-server [/code] 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
[code language="bash"] 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 [/code] Screen capture below of existing folder structure and files Existing Folder and File Structure
  1. 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.
[code language="bash"] #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 [/code] 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 [code language="bash"] #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 [/code]

Enable Availability Groups

To enable availability groups we will again use the mssql-conf script [code language="bash"] #Enable HA sudo /opt/mssql/bin/mssql-conf set hadr.hadrenabled 1 #Restart SQL Server sudo systemctl restart mssql-server [/code] 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. [code language="bash"] #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 [/code]

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 [code language="bash"] #Change Default TCP Port sudo /opt/mssql/bin/mssql-conf set network.tcpport 3333 #Restart SQL Server sudo systemctl restart mssql-server [/code] NOTE: Now that we have changed the default TCP port you will need to specify the port when connecting. an example is below [code language="bash"] sqlcmd -S localhost,3333 -U sa [/code]

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. [code language="bash"] #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 [/code]

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 [code language="bash"] #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 [/code]

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 [code language="bash"] sudo cat /var/opt/mssql/mssql.conf [/code] 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.

No Comments Yet

Let us know what you think

See all

See all

Subscribe by email
Follow us
Love Your Data
Pythian Logo

© 2024 All rights reserved.