Problem
Microsoft has released SQL Server to run on Linux. As a DBA, you must be familiar with SQL Server Configuration Manager for Windows. In this tip we will explore the SQL Server Configuration Manager for Linux and how to make use of it.
Solution
SQL Server Configuration Manager for Windows is a graphical tool to manage the services associated with SQL Server, to configure the network protocols used by SQL Server, and to manage the network connectivity configuration from SQL Server client computers. We can also use the SQL Server Configuration Manager to start, pause, resume, or stop the services, to view service properties, or to change service properties.
In comparison, SQL Server Configuration Manager for Linux is not a graphical interface tool. For SQL Server on Linux, mssql-conf is the corresponding tool for SQL Server on Linux (Red Hat Enterprise Linux, SUSE Linux Enterprise server and Ubuntu).
To get more information about the mssql-conf tool, run the following command:
#Syntax to check information about mssql-conf tool. sudo /opt/mssql/bin/mssql-conf -h
This command gives us various options that we can use with the mssql-conf tool.
We will review each option to get more details and begin learning about the tool.
mssql-conf list
We can configure multiple parameters using the mssql-conf tool. This option provides a list of supported configuration settings.
# Syntax to list options for the mssql-conf tool. /opt/mssql/bin/mssql-conf list
We can see there that it supports below operations.
- tcpport: Change the port where SQL Server will listen for connections.
- defaultdatadir (Default data directory): Change the directory where the new SQL Server database data files (.mdf) are created.
- defaultlogdir (Default log directory): Changes the directory where the new SQL Server database log (.ldf) files are created.
- defaultdumpdir (Default dump directory): Change the directory where SQL Server will deposit memory dumps and other troubleshooting files by default.
- defaultbackupdir (Default backup directory): Change the directory where SQL Server will send the backup files by default.
mssql-conf set
This sets a new value of a SQL Server settings i.e. TCP port, default data directory, default log directory, default backup directory, etc. For example, if we want to configure SQL Server to use port 5500 we need to execute the following statement:
mssql-conf set tcpport 5500
mssql-conf unset
We can reset the original values of SQL Server setting using the unset parameter. For example, to reset the SQL Server port to default values 1433, use the below code:
mssql-conf unset tcpport
mssql-conf traceflags
We can set the traceflags that the SQL Server service will use globally. For example, if we want to run the traceflag 1204 globally, run the below code:
sudo /opt/mssql/bin/mssql-conf traceflag 1204 on
mssql-conf set-sa-password
We can reset the sa password using this parameter. To reset the sa password, run the below code and restart the SQL Server services.
sudo /opt/mssql/bin/mssql-conf set-sa-password 'newpassword'
mssql-conf set-collation
We can set the collation for SQL Server on Linux. Suppose we want to set the server collation to Latin1_General_CS_AS, so we need to run the below code and restart the services.
sudo /opt/mssql/bin/mssql-conf Latin1_General_CS_AS
mssql-conf validate
This validates the configuration file and removes settings that are not acceptable. Below is the code to do so:
sudo /opt/mssql/bin/mssql-conf validate
mssql-conf accept-eula
This command accepts the license terms for SQL Server on Linux.
The mssql-conf tool creates a configuration file to store the user specified configuration changes. These configurations are stored in the mssql.conf config file located at /var/opt/mssql. During SQL Server startup the customized values and parameters are read from this config file and then applied to SQL Server.
We will explore more about mssql.conf file and using different options for mssql-conf in future tips.
Next Steps
- Explore SQL Server Configuration manager in Linux as this is required to perform day to day operations.
- Wait for my next tips to see how to do configuration changes using mssql-conf.
- Read more about SQL Server on Linux Tips.
