[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
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
Change TCP/IP port
By default SQL Server vNext instance runs on 1433 (like previous other instances). Updating port is just a single command.
- Run the below command in Super user mode (SUDO).
sudo /opt/mssql/bin/mssql-conf set tcpport 6600
- Restart SQL Server vNext service to take new port no into effect.
sudo systemctl restart mssql-server
- Check status of SQL Service.
sudo systemctl status mssql-server
- 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
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
- Enable 2 trace flags (my favourite ones) with the help of below command.
sudo /opt/mssql/bin/mssql-conf traceflag 1204 1222 on
- Restart SQL services with the help of same command shared above and check status.
- Disable both trace flags with the help of below command.
sudo /opt/mssql/bin/mssql-conf traceflag 1204 1222 off
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.
*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
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
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
