Configure SQL Server vNext – New utility new methods.

[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.

/opt/mssql/bin/mssql-conf list

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).
    sudo /opt/mssql/bin/mssql-conf set tcpport 6600

    1 - Change port

  1. Restart SQL Server vNext service to take new port no into effect.
    sudo systemctl restart mssql-server

    2 - Restart sql service

  1. Check status of SQL Service.
    sudo systemctl status mssql-server

    3 - Check SQL Service status

  1. Connect SQL Server vNext with new port via SQLCMD to ensure port change has come into effect.
    sqlcmd -S localhost,6600 -U Linux -P Linux

    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.

   
mssql-conf traceflag <<traceflag01>> <<traceflag02>> ... <<traceflagn>> on

mssql-conf traceflag <<traceflag01>> <<traceflag02>> ... <<traceflagn>> off
  1. Enable 2 trace flags (my favourite ones) with the help of below command.
    sudo /opt/mssql/bin/mssql-conf traceflag 1204 1222 on

    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.
    sudo /opt/mssql/bin/mssql-conf traceflag 1204 1222 off

    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

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

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.

sudo/opt/mssql/bin/mssql-conf validate

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

   

About Avanish Panchal

Avanish carries around 15 years of experience in IT industry. He is post graduate in Computer Science, followed by Masters in Business Administration. He has worked on multiple technologies like SQL Server, .net & Sybase with world largest bank(s)/IT consulting firm(s) like JPMorganChase, CapGemini, Datamatics etc. Currently holds position of Database Architect in BioPharma Global Leader. His area of focus are SQL Server DB Engine, Security, Storage, Virtualization & Infrastructure Consolidation. Passionate for delivering Database IT Infrastructure to satisfy and exceed the needs of the enterprise. You may find him active on various forums like SQLBangalore, SQLServerGeeks & many more. His SQL Server passion helped him to be the Core Team member of Asia's First SQL Server Conference in 2015 @ Bangalore (#SSGAS2015).

View all posts by Avanish Panchal →

Leave a Reply

Your email address will not be published.