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.
- Update Local Repositories
sudo apt-get update
- Install SQL Server Agent
sudo apt-get install mssql-server-agent
- 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.
- 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
- 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.
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.
Interested in working with Daniel? Schedule a tech call.