[mssql-conf] – A command line utility to reconfigure SQL Server vNext on Linux

Hello Friends!

In my previous post I listed down how to install & use SQL Server vNext on Linux. However, configuration is an important piece and thought to talk about in detail.

Traditionally we use SQL Server configuration manager to do a lot of things when it comes to configuration. Below list is of few important ones.

  • Password Change for service account
  • Port change
  • Traceflag
  • Start/stop SQL Services
  • Disable/Enable SQL Services
    ..
    ..
    So on…

As SQL Server vNext set a all together new horizon with Linux OS, I strongly feel there is need to talk about how same traditional tool is going to help us. Most interesting part as Linux is mostly Command Line Interface, how SQL Server Configuration Manager will play a key role here, but interestingly you won’t find it too hard, I don’t. Linux edition of SQL Server a configuration tool is named as mssql-conf.

Path of Utility – /opt/mssql/bin/mssql-conf

0 - mssql-conf util

Let’s quick move ahead and leverage this command line tool to configure my SQL Server vNext instance installed on Linux (Ubuntu 16.04 LTS).

List (task) of mssql-conf

Before moving ahead and start leveraging this command line configuration tool to reconfigure my SQL Server vNext instance, let me show you what this tool is capable off with the help of “LIST” switch.

9 - List

Change TCP/IP port

By default SQL Server vNext instance runs on 1433 (like previous other instances). Updating port is just a single command.

  1. Run the below command in Super user mode (SUDO).

    1 - Change port
  1. Restart SQL Server vNext service to take new port no into effect.

    2 - Restart sql service
  1. Check status of SQL Service.

    3 - Check SQL Service status
  1. Connect SQL Server vNext with new port via SQLCMD to ensure port change has come into effect.

    4 - Connect with port

Enable/Disable traceflag

I’ve come across many situations with a need of enabling/disabling a SQL Trace flag. Trying it with SQL Server vNext hosted on a Linux box. Only difference I do see for now all trace flag enabling requires SQL services restart to take them into effect. Little unlike, hopefully take robust shape as time moves on.

With the help of mssq-conf I’m able to enable/disable 1 or more traces.

  1. Enable 2 trace flags (my favourite ones) with the help of below command.

    5 - enable trace flag
  2. Restart SQL services with the help of same command shared above and check status. 3 - Check SQL Service status
  3. Disable both trace flags with the help of below command.

    6 - Disable trace flag

 

Changing SA password.

I’ve included that particular piece in my previous blog about SQL Server vNext installation on Linux. All you need to find key word mssql-conf. J

Changing Collation

mssql-conf tool helps us to change collation of SQL server instance hosted in Linux, seamlessly. Provided current supported collations are including while changing collation. List of supported collation is found with the help of function.

7 - Collation list

*Currently 3955 collations are supported with SQL Server vNext CTP1.4 as illustrated in above picture.

Defaults collation- SQL_Latin1_General_CP1_CI_AS

Let’s change default collation to Latin1_General_Bin

8 - Updated collation

End result pop-ups to restart SQL Services, use systemctl utility to address that as explained above in this blog.

Start/Stop SQL Services

mssql-conf does help to stop & start SQL Server vNext service but I strongly recommend to continue using Linux utility systemctl

Enabling & Disabling SQL Services

I’ve included that particular piece in my previous blog about SQL Server vNext installation on Linux. All you need to find key word enable.

Validate

Just a single command to validate your installation.

10 - Validation

Happy Learning!

Avanish Panchal
Regional Head – DataPlatformGeeks & DPS2017 Core Team Member
Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook

Follow Avanish Panchal on Twitter | Follow Avanish Panchal on FaceBook