How to configure SQL server in Linux

Problem

You installed SQL Server on Linux and need to customize the default installation, for instance change its default port, instance collation or even add trace flags. In this tip I will show you how easy it is.

Solution

With the arrival of SQL Server to Linux came new ways for us to do our DBA tasks. On Windows we are used to using SQL Server Configuration Manager as the de facto configuration tool for SQL Server, because of its simplicity compared with other ways like PowerShell scripting. Now on Linux we don't have that graphical interface to interact with and we must rely on the console, but surprisingly it's not too hard to use. Microsoft included on the Linux edition of SQL Server a configuration tool named mssql-confwhich you may find on the /opt/mssql/bin/ folder.

mssql-conf

This script configuration tool receives the arguments listed on the table below. All the arguments are case sensitive.

ArgumentDescription

set

Sets the value of a SQL Server setting. You can get the list of available settings with the list argument (mssql-conf list). 

unset

Used to remove a setting added with the set option.

traceflag

Use this argument to set trace flags on or off.

set-sa-password

This argument is used to change the sa password while the service is not running.

set-collation

Used to change the instance collation

validate

Validates the contents of SQL Server configuration file mssql.conf found in /var/opt/mssql/ folder.

list

Lists the available settings to set / unset.

setup

Runs the installation script.

start-service

Starts the SQL Server service, but I suggest that you use systemctl start mssql-server instead.

stop-service

Stops the SQL Server service, but I suggest that you use systemctl stop mssql-server instead.

enable-service

Enables SQL Server service automatic start on system start up.

disable-service

Disables SQL Server service automatic start on system start up.

set / unset Arguments

You can use this argument to set or unset one option at a time by using the following command.

mssql-conf set [option] [option value]
mssql-conf unset [option] 

For example, if you want to configure SQL Server to use port 444 you need to execute the following statement on an elevated prompt.

mssql-conf  set network.tcpport 444

Also, if you want to let SQL Server to use the default port instead of 444 you have to run the following command.

mssql-conf  unset network.tcpport

The following screen capture illustrates the previous statements.

Setting tcpport to 444 and then back to default.

traceflag Argument

You can use this argument to set on or off one or more trace flags at a time with the following commands.

mssql-conf traceflag traceflag01 traceflag02 ... traceflagn on

mssql-conf traceflag traceflag01 traceflag02 ... traceflagn off

Take a look at the following screen captures that shows how to set on / off trace flags 1204 (returns the resources and types of locks participating in a deadlock and also the current command affected) and 1222 (returns the resources and types of locks that are participating in a deadlock and also the current command affected, in an XML format that does not comply with any XSD schema).

Setting Trace Flags 1204 and 1222 ON.

Setting Trace Flags 1204 and 1222 OFF.

list Argument

As previously stated, you can get the list of available settings to use with set and unset arguments by running the following command:

mssql-conf list

The following image is a screen capture that shows the execution of this command.

Execution of mssql-conf with list argument.

set-sa-password

If you run mssql-conf with this argument while the instance is running it will not work.

set-collation

This argument is used to change the instance collation to one of the available collations listed on file /opt/mssql/lib/mssql-conf/collations.txt. The SQL Server service must be offline in order for this command to work.

Additional help on mssql-conf (The Linux Way)

There is a command on Linux named man (from the English word Manual) that receives as an argument another command, and displays what is known as the Manual Pages. Yes, Microsoft knew about that and added a reference into the Manual pages about mssql-conf that you can query with the following command.

mssql-conf man page.





  • SQL, Linux
  • 1 Корисниците го најдоа ова како корисно
Дали Ви помогна овој одговор?

Понудени резултати

How to change maximum upload size in php.ini

  There are a few common errors that occur in Wordpress and other PHP-based programs that use...

How to change the primary IP address of a cPanel server

Steps in WHM: Log into WHM and go to Basic cPanel & WHM Setup Change the Primary IP here...

How to install WordPress with Docker on Ubuntu 16.04

Before we start, it is necessary to install Docker and Docker Compose. On Ubuntu 16.04, this can...

How to Upgrade Kernel to Latest Version in Ubuntu

It is important to keep your systems up-to-date, here we'll show you how to upgrade your kenerl...

How To Install Latest Nodejs and NPM Version in Linux Systems

In this guide, we'll take a look at how you can install the latest version of Nodejs and NPM in...