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.
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]
NOTE: This will not show default settings.
- 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
- Install SQL Server Agent
- Restart SQL Server
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
- 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.
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 hereConfigure 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.
