Prashanth Jayaram

How to configure SQL Server 2017 on Linux with mssql-conf and other available tools

December 13, 2017 by

Configuration of any system plays a vital role in its working efficiently. However, configuring a system needs not necessarily be a daunting task. There are several tools that help with the process.

SQL Server on Linux provides a wide range of options for configuration, management and administration.

  1. mssql-conf
  2. Transact-SQL
  3. SQL Server Management Studio
  4. PowerShell

This article outlines the use of mssql-conf in conjunction with other available tools, to manage SQL Server.

On Windows, we are familiar with SQL Server Configuration Manager, the configuration tool used to manage and administer SQL Server services, configure the network protocols, and to manage network connectivity between the SQL Server and the client computers.

On Linux, we have a configuration tool known as mssql-conf. The primary function of this tool remains the same as that of the SQL Server Configuration Manager, but makes it easy for Oracle and Linux admins to work with SQL Server 2017 on Linux. I’d like to visit some of the key concepts of the Oracle parameter file (PFILE and SPFILE) here, with which the server configuration can be set/unset by adding/modifying entries in the configuration file.

PFILE (parameter file) is a text file used to initialize the database and setting configuration parameters. This PFILE is read when the instance starts up, in order to get specific instance characteristics. The PFILE is text based, and can be edited in an editor like VI/VIM on UNIX/Linux or Notepad on Windows. Any changes made to the PFILE would take effect only when the database is restarted.

SPFILE, on the other hand, is a binary file. It is similar to sp_configure of SQL Server. T-SQL is used to adjust values or entries, or to commit them into the system without restarting the system.

I feel that using the Oracle configuration files as a reference in this context helps many DBAs form the analogy and understand the architecture of the SQL Server 2017 better. Also, since SQL Server 2017 has already been ported to Linux, many non-SQL-DBAs can enjoy the flexibility to play with SQL.

The most important point of mssql-conf is the validation rule, which adds to the flexibility, enabling the management of the configuration in a more dynamic way. The command is found at /var/opt/mssql/mssql-conf in Linux. We can also edit the file configuration file found at /var/opt/msssql/mssql.conf. Use any available text editor to edit or add the required setting(s) into the SQL configuration.

Having control over the configuration file will help in the standardization of the installation. All these features help avoid human error associated with manually updating a file.

Configuration Manager can be used to manage the following tasks

  1. Managing database services by configuring the startup parameter
  2. Changing the service accounts used by the SQL Server
  3. Managing client/server protocols

Any SQL Server configuration change registers a modification to an existing registry key value. This requires a service restart for the configuration change to take effect.

Mssql-conf file is a combination sp_configure and SSCM (SQL Server Configuration Manager).

Mssql-conf is a configuration file for the SQL Server that gets created during the process of installation of SQL Server on Linux.

You can use this utility to:

  1. Run the final installation setup process
  2. Set/unset the SQL Server configuration values
    • Server collation
    • SQL Agent settings
    • Database mail profile
    • Default data directory
    • Log directory
    • Default dump directory
    • Default backup directory
    • High Availability settings
    • SQL Server memory limit
    • TCP port
  3. Enable or disable trace flags
  4. Set the SA password
  5. Validate the configuration

Let’s begin

This section deals with the configuration overview of mssql.conf and shows the set of commands to be executed to set or unset various configuration parameters using mssql-conf command.

  1. Understand mssql.conf
  2. The mssql-conf command reference
  3. Set values in the mssql-conf configuration file
  4. Configuration validation

To display the default parameters of the mssql.conf file, use any available editor to open the file.

I’m using the VI editor to display the contents of the mssql.conf file


Now, execute mssql-conf to understand the available configuration options

The output gives the argument list that can be used with mssql-conf to set or unset the desired SQL Server configuration properties

Let’s go ahead and display all the available configuration settings using the list parameter. It will list all the configuration attributes for the SQL Server instance


Now, add some of the server configuration parameters using mssql-conf command. As we go through the process, after every entry, the SQL Server requires a restart to commit the changes. So let me hold off the restart part and go ahead make the changes to all the parameters I want to make changes to, and then perform a single restart the SQL Service to commit all the changes at once.

  1. Change the default data and log directory with the set command:

    The properties filelocation.defaultdatadir and filelocation.defaultlogdir settings can be changed to new database and log file locations using the following command. The default file location of data and log file is /var/opt/mssql/data

  2. Change the dump location to /sqlshack/dump with set command:

    To Change default dump directory location, use the fielocation.defaultdumpdir property with the set command

  3. Change backup directory location to /sqlhsack/backup/

    The property filelcoation.defaultbackupdir setting is updated to point to the new backup directory location with the set command


  4. Enable High Availability

    The property hard.hadrenabled should be set to 1. This will enable the Availability Group on SQL Server


  5. Disable High Availability

    The property hard.hadrenabled should be set to 0. This will disable the AG on the SQL Server

  6. Limit SQL Server memory usage

    The memory setting is memory.memorylimitb, which represents the total memory available for SQL Server. (It’s not SQL Server Max or Min memory setting.) By default, SQL Server uses 80% of the total RAM

  7. Change TCP Port

    The network.tcpport property is used to change the default port setting; we’re changing the port from 1433 to 1435.

  8. To Enable/disable trace flags

    To enable or disable multiple trace flags, use space in between the traceflags values with the on clause at the end of the command, like so:


  9. Restart the SQL Server service

View current settings

To view any configured settings, use any of the editor, or the cat command; the following shows the contents of the mssql.conf file


To validate the configuration file

Validation helps in validating all the properties of the configuration file. It also helps identify any type errors, thereby ensuring that the SQL Server configuration is correct and ready for prime time


Let’s make some changes to the configuration properties to see how the validation will act on the incorrect properties. Open the file and modify some contents of the configuration file. As shown in the following figure, the defaultlogdir is misspelled as defaultlogr, which is not correct. We’ve also added correct traceflags, traceflag1 and traceflag2. Run the validation command to validate the configuration.

The warning message shows issues in some of the configuration settings.



Output

The use of default port is not functioning, since we updated the default port configuration to 1435. So, in the second attempt, we specify the custom port number, along with the server name, the parameters separated by a comma. Now the command executes successfully

The output displays the recently set traceflag details

Wrapping up

We saw several tools that are available to configure MSSQL, and discussed mssql-conf in particular. I’ve covered a portion of Oracle parameter files and discussed about the similarities and the purpose of using the configuration files. Later, we had an in-depth discussion on mssql-conf tool, along with examples.

The goal of this article is to be a kick-start reference to SQL DBAs to try to set the configuration using new a tool set and configuration files. I think it’s time for SQL DBA’s to enhance their cross-technical skills as SQL Server is poised to cater to a bigger market by posting and setting new records for TPC-H benchmark.

The configuration file, as we saw, has many properties and options to tune SQL Server for optimal performance. Also, if you are a developer or an administrator of the Linux ecosystem, there is now a lot more reasons to play with SQL Server 2017. SQL Server on Linux is setting trends and benchmark to become a leading enterprise database solution for every organization and business.

Prashanth Jayaram
Latest posts by Prashanth Jayaram (see all)

Related posts:

  1. Change default database file and backup paths in SQL Server on Linux
  2. Azure Kubernetes Service (AKS) – Managing SQL Server database files
  3. SQL interview questions on database backups, restores and recovery – Part II
  4. SQL interview questions on database backups, restores and recovery – Part I
  5. Changing SQL Dump file locations in SQL Server on Linux
168 Views