sql-server-linux-configure-mssql-conf.md

title	Configure SQL Server settings on Linux | Microsoft Docs
description	This topic describes how to use the mssql-conf tool to configure SQL Server 2017 settings on Linux.
author	luisbosquez
ms.author	lbosq
manager	jhubbard
ms.date	05/17/2017
ms.topic	article
ms.prod	sql-linux
ms.technology	database-engine
ms.assetid	06798dff-65c7-43e0-9ab3-ffb23374b322
ms.custom	H1Hack27Feb2017

Configure SQL Server on Linux with the mssql-conf tool

mssql-conf is a configuration script that installs with SQL Server 2017 CTP 2.1 for Red Hat Enterprise Linux, SUSE Linux Enterprise Server, and Ubuntu. You can use this utility to set the following parameters:

• TCP port: Change the port where SQL Server will listen for connections.
• Default data directory: Change the directory where the new SQL Server database data files (.mdf) are created.
• Default log directory: Changes the directory where the new SQL Server database log (.ldf) files are created.
• Default dump directory: Change the directory where SQL Server will deposit the memory dumps and other troubleshooting files by default.
• Default backup directory: Change the directory where SQL Server will send the backup files by default.
• Mini and full dump preferences: Specify whether to generate both mini dumps and full dumps.
• Core dump type: Choose the type of dump memory dump file to collect.
• High Availability: Enable Availability Groups.
• Set traceflags: Set the traceflags that the service is going to use.
• Set collation: Set a new collation for SQL Server on Linux.

The following sections show examples of how to use mssql-conf for each of these scenarios.

Tip

These examples run mssql-conf by specify the full path: /opt/mssql/bin/mssql-conf. If you choose to navigate to that path instead, run mssql-conf in the context of the current directory: ./mssql-conf.

Note

Some of these settings can also be configure with environment variables. For more information, see Configure SQL Server settings with environment variables.

Change the 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:

1. Run the mssql-conf script as root with the "set" command for "network.tcpport":

   sudo /opt/mssql/bin/mssql-conf set network.tcpport <new_tcp_port>

2. Restart the SQL Server service as instructed by the configuration utility:

   sudo systemctl restart mssql-server

3. When connecting to SQL Server now, you will need to specify the port with a comma (,) after the hostname or IP address. For example, to connect with SQLCMD, you would use the following command:

   sqlcmd -S localhost,<new_tcp_port> -U test -P test

Change the default data or log directory location

This option will let you change the location where the new database and log files are created. By default, this location is /var/opt/mssql/data. To achieve this, follow these steps:

1. Create the directory where the new database's data and log files will reside. For example, we will use /tmp/data:

   sudo mkdir /tmp/data

2. Change the owner and group of the directory to the "mssql" user:

   sudo chown mssql /tmp/data
   sudo chgrp mssql /tmp/data

3. Use mssql-conf to change the default data directory with the "set" command:

   sudo /opt/mssql/bin/mssql-conf set filelocation.defaultdatadir /tmp/data

4. Restart the SQL Server service as instructed by the configuration utility:

   sudo systemctl restart mssql-server

5. Now all the database files for the new databases created will be stored in this new location. If you would like to change the location of the log (.ldf) files of the new databases, you can use the following "set" command:

   sudo /opt/mssql/bin/mssql-conf set filelocation.defaultlogdir /tmp/log

6. This command also assumes that a /tmp/log directory exists, and that it is under the user and group "mssql".

Change the default dump directory location

This option will let you change the default location where the memory and SQL dumps are generated whenever there is a crash. By default, these files are generated in /var/opt/mssql/log.

To set up this new location, use the following commands:

1. Create the directory where the dump files will reside. For example, we will use /tmp/dump:

   sudo mkdir /tmp/dump

2. Change the owner and group of the directory to the "mssql" user:

   sudo chown mssql /tmp/dump
   sudo chgrp mssql /tmp/dump

3. Use mssql-conf to change the default data directory with the "set" command:

   sudo /opt/mssql/bin/mssql-conf set filelocation.defaultdumpdir /tmp/dump

4. Restart the SQL Server service as instructed by the configuration utility:

   sudo systemctl restart mssql-server

Change the default backup directory location

This option will let you change the default location where the backup files are generated. By default, these files are generated in /var/opt/mssql/data.

To set up this new location, use the following commands:

1. Create the directory where the backup files will reside. For example, we will use /tmp/backup:

   sudo mkdir /tmp/backup

2. Change the owner and group of the directory to the "mssql" user:

   sudo chown mssql /tmp/backup
   sudo chgrp mssql /tmp/backup

3. Use mssql-conf to change the default backup directory with the "set" command:

   sudo /opt/mssql/bin/mssql-conf set filelocation.defaultbackupdir /tmp/backup

4. Restart the SQL Server service as instructed by the configuration utility:

   sudo systemctl restart mssql-server

Specify core dump settings

If an exception occurs in one of the SQL Server processes, SQL Server creates a memory dump.

There are two options for controlling the type of memory dumps that SQL Server collects: coredump.coredumptype and coredump.captureminiandfull. These relate to the two phases of core dump capture.

The first phase capture is controlled by the coredump.coredumptype setting, which determines the type of dump file generated during an exception. The second phase is enabled when the coredump.captureminiandfull setting. If coredump.captureminiandfull is set to true, the dump file specified by coredump.coredumptype is generated and a second mini dump is also generated. Setting coredump.captureminiandfull to false disables the second capture attempt.

1. Decide whether to capture both mini and full dumps with the coredump.captureminiandfull setting.

   sudo /opt/mssql/bin/mssql-conf set coredump.captureminiandfull <true or false>

   Default: true

2. Specify the type of dump file with the coredump.coredumptype setting.

   sudo /opt/mssql/bin/mssql-conf set coredump.coredumptype <dump_type>

   Default: miniplus

   The following table lists the possible coredump.coredumptype values.

   Type	Description
   mini	Mini is the smallest dump file type. It uses the Linux system information to determine threads and modules in the process. The dump contains only the host environment thread stacks and modules. It does not contain indirect memory references or globals.
   miniplus	MiniPlus is similar to mini, but it includes additional memory. It understands the internals of SQLPAL and the host environment, adding the following memory regions to the dump: - Various globals - All memory above 64TB - All named regions found in /proc/$pid/maps - Indirect memory from threads and stacks - Thread information - Associated Teb’s and Peb’s - Module Information - VMM and VAD tree
   filtered	Filtered uses a subtraction-based design where all memory in the process is included unless specifically excluded. The design understands the internals of SQLPAL and the host environment, excluding certain regions from the dump.
   full	Full is a complete process dump that includes all regions located in /proc/$pid/maps. This is not controlled by coredump.captureminiandfull setting.

High Availability

This option enables availability groups on your SQL Server instance. The following command enables availability groups by setting hadr.hadrenabled to 1. You must restart SQL Server for the setting to take effect.

sudo /opt/mssql/bin/mssql-conf set hadr.hadrenabled  1
sudo systemctl restart mssql-server

For information how this is used with availability groups, see the following two topics.

• Configure Always On Availability Group for SQL Server on Linux
• Configure read-scale availability group for SQL Server on Linux

Enable/Disable traceflags

This option will let you enable or disable traceflags for the startup of the SQL Server service. To enable/disable a traceflag use the following commands:

1. Enable a traceflag using the following command. For example, for Traceflag 1234:

   sudo /opt/mssql/bin/mssql-conf traceflag 1234 on

2. You can enable multiple traceflags by specifying them separately:

   sudo /opt/mssql/bin/mssql-conf traceflag 2345 3456 on

3. In a similar way, you can disable one or more enabled traceflags by specifying them and adding the "off" parameter:

   sudo /opt/mssql/bin/mssql-conf traceflag 1234 2345 3456 off

4. Restart the SQL Server service as instructed by the configuration utility for the changes to apply:

   sudo systemctl restart mssql-server

Change the SQL Server collation

This option will let you change the collation value to any of the supported collations:

1. Run the "set-collation" option and follow the prompts:

   sudo /opt/mssql/bin/mssql-conf set-collation

2. The mssql-conf utility will try to restore the databases using the specified collation and restart the service. If there are any errors, it will roll-back the collation to the previous value.

For a list of supported collations, run the sys.fn_helpcollations function: SELECT Name from sys.fn_helpcollations().

View current settings

To view any settings that you have explicitly configured with mssql-conf, run the following command:

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

Note that any settings not shown in this file are using their default values.

Next steps

To instead use environment variables to make some of these configuration changes, see Configure SQL Server settings with environment variables.

For other management tools and scenarios, see Manage SQL Server on Linux.