How to install MsSQL Server 2019 on Ubuntu 20.04

Microsoft SQL Server is a relational database management system developed by Microsoft. It is aimed to be used to store and retrieve data by applications. It can be run either on the same computer or on another across a network.

In this guide we are going to install Microsoft Server 2019 in Ubuntu 20.04 or latest server. We then connect with sqlcmd to create your first database and run queries.

Also Check:

# Prerequisites

Updated ubuntu 20.04 server
Access to the commandline as root user

# Ensure that the server is up to date

Before proceeding, it is always a good practice to make sure that the server we are working on has updated packages. Use this command to ensure that the server is updated

1
2
sudo apt update
sudo apt upgrade -y

Ensure that some necessary packages are installed

1
sudo apt install -y wget curl

# Installing MsSQL Server 2019 in Ubuntu

MsSQL Server is not available in the default Ubuntu repos. To set up the repo and install ubuntu, follow these steps:

Import the public repository GPG keys:

1
wget -qO- https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -

1
sudo add-apt-repository "$(wget -qO- https://packages.microsoft.com/config/ubuntu/20.04/mssql-server-2019.list)"

Run the following commands to update the new repo and install SQL Server:

1
2
sudo apt-get update
sudo apt-get install -y mssql-server

# Configuring MsSQL Server

After the package installation finishes, run mssql-conf setup and follow the prompts to set the SA password and choose your edition.

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

Make sure to specify a strong password for the SA account (Minimum length 8 characters, including uppercase and lowercase letters, base 10 digits and/or non-alphanumeric symbols).

This is the output on my server

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
$ sudo /opt/mssql/bin/mssql-conf setup
sudo: unable to resolve host ubuntusrv.citizix.com: No address associated with hostname
Choose an edition of SQL Server:
  1) Evaluation (free, no production use rights, 180-day limit)
  2) Developer (free, no production use rights)
  3) Express (free)
  4) Web (PAID)
  5) Standard (PAID)
  6) Enterprise (PAID) - CPU Core utilization restricted to 20 physical/40 hyperthreaded
  7) Enterprise Core (PAID) - CPU Core utilization up to Operating System Maximum
  8) I bought a license through a retail sales channel and have a product key to enter.

Details about editions can be found at
https://go.microsoft.com/fwlink/?LinkId=2109348&clcid=0x409

Use of PAID editions of this software requires separate licensing through a
Microsoft Volume Licensing program.
By choosing a PAID edition, you are verifying that you have the appropriate
number of licenses in place to install and run this software.

Enter your edition(1-8): 2
The license terms for this product can be found in
/usr/share/doc/mssql-server or downloaded from:
https://go.microsoft.com/fwlink/?LinkId=2104294&clcid=0x409

The privacy statement can be viewed at:
https://go.microsoft.com/fwlink/?LinkId=853010&clcid=0x409

Do you accept the license terms? [Yes/No]:Yes

Enter the SQL Server system administrator password:
Confirm the SQL Server system administrator password:
Configuring SQL Server...


ForceFlush is enabled for this instance.
ForceFlush feature is enabled for log durability.
Created symlink /etc/systemd/system/multi-user.target.wants/mssql-server.service &#x2192; /lib/systemd/system/mssql-server.service.
Setup has completed successfully. SQL Server is now starting.

# Starting and enabling the service

Once the configuration is done, verify that the service is running:

1
sudo systemctl status mssql-server --no-pager

This is the output on my server

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
$ sudo systemctl status mssql-server --no-pager
sudo: unable to resolve host ubuntusrv.citizix.com: No address associated with hostname
 mssql-server.service - Microsoft SQL Server Database Engine
     Loaded: loaded (/lib/systemd/system/mssql-server.service; enabled; vendor preset: enabled)
     Active: active (running) since Wed 2021-11-24 09:45:21 UTC; 1min 29s ago
       Docs: https://docs.microsoft.com/en-us/sql/linux
   Main PID: 21584 (sqlservr)
      Tasks: 136
     Memory: 592.6M
     CGroup: /system.slice/mssql-server.service
             &#x251C;&#x2500;21584 /opt/mssql/bin/sqlservr
             &#x2514;&#x2500;21615 /opt/mssql/bin/sqlservr

Nov 24 09:45:25 ubuntusrv.citizix.com sqlservr[21615]: [78B blob data]
Nov 24 09:45:25 ubuntusrv.citizix.com sqlservr[21615]: [84B blob data]
Nov 24 09:45:25 ubuntusrv.citizix.com sqlservr[21615]: [145B blob data]
Nov 24 09:45:26 ubuntusrv.citizix.com sqlservr[21615]: [96B blob data]
Nov 24 09:45:26 ubuntusrv.citizix.com sqlservr[21615]: [66B blob data]
Nov 24 09:45:26 ubuntusrv.citizix.com sqlservr[21615]: [75B blob data]
Nov 24 09:45:26 ubuntusrv.citizix.com sqlservr[21615]: [96B blob data]
Nov 24 09:45:26 ubuntusrv.citizix.com sqlservr[21615]: [100B blob data]
Nov 24 09:45:26 ubuntusrv.citizix.com sqlservr[21615]: [71B blob data]
Nov 24 09:45:26 ubuntusrv.citizix.com sqlservr[21615]: [124B blob data]

If you plan to connect remotely, you might also need to open the SQL Server TCP port (default 1433) on your firewall.

At this point, SQL Server 2019 is running on your Ubuntu machine and is ready to use!

If you want to enable the service to start on boot use this command

1
sudo systemctl enable mssql-server

# Install the SQL Server command-line tools

To create a database, you need to connect with a tool that can run Transact-SQL statements on the SQL Server. The following steps install the SQL Server command-line tools: sqlcmd and bcp.

Use the following steps to install the mssql-tools on Ubuntu.

Import the public repository GPG keys.

1
curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -

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

Update the sources list and run the installation command with the unixODBC developer package.

1
2
sudo apt-get update
sudo apt-get install mssql-tools unixodbc-dev

Add /opt/mssql-tools/bin/ to your PATH environment variable in a bash shell.

To make sqlcmd/bcp accessible from the bash shell for login sessions, modify your PATH in the ~/.bash_profile file with the following command:

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

To make sqlcmd/bcp accessible from the bash shell for interactive/non-login sessions, modify the PATH in the ~/.bashrc file with the following command:

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

# Connecting locally

Upon successful installation, we can connect to the database server and perform some operations

Run sqlcmd with parameters for your SQL Server name (-S), the user name (-U), and the password (-P). In this tutorial, you are connecting locally, so the server name is localhost. The user name is SA and the password is the one you provided for the SA account during setup.

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

You can omit the password on the command line to be prompted to enter it.

If you later decide to connect remotely, specify the machine name or IP address for the -S parameter, and make sure port 1433 is open on your firewall.

If successful, you should get to a sqlcmd command prompt: 1>.

1
2
$ sqlcmd -S 127.0.0.1 -U SA -P 'Sup4Srcr7Pa$$';
1>

# Executing basic sql queries

In this section, we will explore using sqlcmd to create a new database, add data, and run a simple query.

Note: You must type GO on a new line to execute the commands.

To create a new database, use this:

1
2
1> CREATE DATABASE CitizixDB
2> GO

To get names of all the databases:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
1> SELECT Name from sys.Databases
2> GO
Name
--------------------------------------------------------------------------------------------------------------------------------
master
tempdb
model
msdb
CitizixDB

(5 rows affected)

Let us create a new table, Users, and insert new rows.

Switch context to the new CitizixDB database:

1
2
3
1> use CitizixDB
2> GO
Changed database context to 'CitizixDB'.

Create new table named Users:

1
2
1> CREATE TABLE Users (id INT, name NVARCHAR(50), age INT)
2> GO

Insert data into the new table:

1
2
3
4
5
6
1> INSERT INTO Users VALUES (1, 'etowett', 72); INSERT INTO Users VALUES (2, 'kchep', 36);
2> GO

(1 rows affected)

(1 rows affected)

Select data from our database table

1
2
3
4
5
6
7
1> SELECT * FROM Users WHERE age > 50;
2> GO
id          name                                               age
----------- -------------------------------------------------- -----------
          1 etowett                                                     72

(1 rows affected)

To end your sqlcmd session, type QUIT:

1
1> QUIT

# Conclusion

In this guide, we managed to install Ms SQL Server 2019 in an Ubuntu 20.04 server.