SQL Server on Ubuntu

DRAFT

Microsoft SQL Server 2019 on Ubuntu 20.04

First, we need to enable the Microsoft apt repository in the system:

# Download the public key of the apt repository
$ wget -qO- https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
# Add the mssql-server 2019 apt repository
$ sudo add-apt-repository "$(wget -qO- https://packages.microsoft.com/config/ubuntu/20.04/mssql-server-2019.list)"

With the needed apt repository in place, the next step is to install the MSSQL server 2019 binary package:

$ sudo apt-get install -y mssql-server

To finally finish the setup of your MSSQL server instance you need to run the following command:

$ sudo /opt/mssql/bin/mssql-conf setup

In this step you will be asked to define a password for the SA user and also choose your edition. When it is done, the status of the server can be checked via systemd:

$ sudo systemctl status mssql-server

It should be successfully active and running. In order to test the server, let’s install the command line tools to interact with it. The first step is to add another apt repository maintained by Microsoft:

$ curl https://packages.microsoft.com/config/ubuntu/20.04/prod.list | sudo tee /etc/apt/sources.list.d/msprod.list 

Update the apt cache and install the needed packages:

$ sudo apt-get update 
$ sudo apt-get install -y mssql-tools unixodbc-dev

The binaries used to interact with the MSSQL server 2019 are installed in /opt/mssql-tools/bin/ which is not in the $PATH. You can specify the full path when calling the binaries or add this directory to the $PATH variable. Let’s do the latter:

$ echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
$ source ~/.bashrc

Now it is time to connect to our MSQL server using the new tools we have:

$ sqlcmd -S localhost -U SA -P '<YourPassword>'

The YourPassword is the password defined for the SA user previously when setting up the server. Then you should get a sqlcmd command prompt. Since we are connected to the server we can for instance create a database:

# Create a database called TestDB
1> CREATE DATABASE TestDB
# Query the name of all databases in the server
2> SELECT Name from sys.Databases
# Execute the list of the commands above
3> GO

You will get a list of all the databases in your MSSQL server:

$ sqlcmd -S localhost -U SA -P 'C@nonic@l'
1> CREATE DATABASE TestDB
2> SELECT Name from sys.Databases
3> GO
Name                                                                                                                       	 
--------------------------------------------------------------------------------------------------------------------------------
master                                                                                                                     	 
tempdb                                                                                                                     	 
model                                                                                                                      	 
msdb                                                                                                                       	 
TestDB                                                                                                                     	 

(5 rows affected)

With that you are ready to keep creating databases and tables and inserting data as you wish. Have fun!