In this guide, we are going to install Ms SQL Server 2019 on Ubuntu 22.04. As a bonus we are going to see how to usesqlcmdto connect to the server, create a database and run queries.
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.
Also check these:
Requirements
To follow along, please ensure that you have:
- Up to date Ubuntu 22.04 Server withat least 2 GBof memory
- Internet Access from the server
- Root access to the server
Ensure the server is up to date
Let us make sure that our server has updated packages with this command
1
2
| sudo apt update
sudo apt -y upgrade
|
Once done, let us install common packages that will be essential during the installation.
1
| sudo apt install curl wget -y
|
Install SQL Server
To configure SQL Server on Ubuntu, follow these steps:
Download the public key, convert from ASCII to GPG format, and write it to the required location:
1
| curl -fsSL https://packages.microsoft.com/keys/microsoft.asc | sudo gpg --dearmor -o /usr/share/keyrings/microsoft-prod.gpg
|
Manually download and register the SQL Server Ubuntu repository:
1
| curl -fsSL https://packages.microsoft.com/config/ubuntu/22.04/mssql-server-preview.list | sudo tee /etc/apt/sources.list.d/mssql-server-preview.list
|
If you get this sudo: add-apt-repository: command not found then install with:
1
| sudo apt install software-properties-common
|
Update the repositories and install SQL Server:
1
2
| sudo apt-get update
sudo apt-get install -y mssql-server
|
After the package installation finishes, run mssql-conf setup using its full path, and follow the prompts to set the SA password.
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).
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
| $ sudo /opt/mssql/bin/mssql-conf setup
This is a preview version (free, no production use rights, 180-day limit starting Thu Aug 24 10:25:19 GMT 2023), continue? [Yes/No]:Yes
The license terms for this product can be found in
/usr/share/doc/mssql-server or downloaded from: https://aka.ms/useterms
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...
The licensing PID was successfully processed. The new edition is [Enterprise Evaluation Edition].
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 → /lib/systemd/system/mssql-server.service.
Setup has completed successfully. SQL Server is now starting.
|
Once the configuration is done, verify that the service is running:
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
● mssql-server.service - Microsoft SQL Server Database Engine
Loaded: loaded (/lib/systemd/system/mssql-server.service; enabled; vendor preset: enabled)
Active: active (running) since Sat 2023-09-30 14:01:33 UTC; 29s ago
Docs: https://docs.microsoft.com/en-us/sql/linux
Main PID: 3487 (sqlservr)
Tasks: 154
Memory: 626.0M
CPU: 6.000s
CGroup: /system.slice/mssql-server.service
├─3487 /opt/mssql/bin/sqlservr
└─3515 /opt/mssql/bin/sqlservr
Sep 30 14:01:38 fiddle-etestub0 sqlservr[3515]: [64B blob data]
Sep 30 14:01:38 fiddle-etestub0 sqlservr[3515]: [158B blob data]
Sep 30 14:01:38 fiddle-etestub0 sqlservr[3515]: [155B blob data]
Sep 30 14:01:38 fiddle-etestub0 sqlservr[3515]: [193B blob data]
Sep 30 14:01:38 fiddle-etestub0 sqlservr[3515]: [66B blob data]
Sep 30 14:01:38 fiddle-etestub0 sqlservr[3515]: [75B blob data]
Sep 30 14:01:38 fiddle-etestub0 sqlservr[3515]: [96B blob data]
Sep 30 14:01:38 fiddle-etestub0 sqlservr[3515]: [100B blob data]
Sep 30 14:01:38 fiddle-etestub0 sqlservr[3515]: [71B blob data]
Sep 30 14:01:38 fiddle-etestub0 sqlservr[3515]: [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!
To connect to the server, you need to have the client tools that will allow you to do so. In this section we will install SQL Server command-line tools: sqlcmd and bcp.
Import the public repository GPG keys.
1
| curl https://packages.microsoft.com/keys/microsoft.asc | sudo tee /etc/apt/trusted.gpg.d/microsoft.asc
|
Register the Microsoft Ubuntu repository.
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 -y
|
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 to the server locally
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.BashCopy
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>. Let us confirm that everything works by checking the version
1
2
3
4
5
6
7
8
9
10
11
| $ sqlcmd -S localhost -U SA -P 'gM3PL3wh2YNjBm7TDTcw'
1> SELECT @@VERSION
2> GO
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Microsoft SQL Server 2019 (RTM-CU13) (KB5005679) - 15.0.4178.1 (X64)
Sep 23 2021 16:47:49
Copyright (C) 2019 Microsoft Corporation
Enterprise Evaluation Edition (64-bit) on Linux (Ubuntu 20.04.3 LTS) <X64>
(1 rows affected)
|
Create and query data
In this section, let us use sqlcmd to create a new database, add data, and run a simple query.
Create a new database
Use this Transact-SQL command in sqlcmd command prompt to create a test database called CitizixDB:
1
2
| 1> CREATE DATABASE CitizixDB
2> GO
|
Use this to query the name of all of the databases on your server
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)
|
Note: **You must type GO on a new line to execute the previous commands
Insert data
Let’s create a new table, Inventory, and insert two new rows.
In the sqlcmd command prompt, switch context to the new CitizixDB database:
1
2
3
| 1> USE CitizixDB
2> Go
Changed database context to 'CitizixDB'.
|
Create new table named Inventory:
1
2
| 1> CREATE TABLE Inventory (id INT, name NVARCHAR(50), quantity INT)
2> GO
|
Insert data into the new table:
1
2
3
4
| 1> INSERT INTO Inventory VALUES (1, 'banana', 150); INSERT INTO Inventory VALUES (2, 'orange', 154);
2> GO
(1 rows affected)
(1 rows affected)
|
Select data
Use this query to fetch data from the Inventory table where the quantity is greater than 152:
1
2
3
4
5
6
7
| 1> SELECT * FROM Inventory WHERE quantity > 152;
2> GO
id name quantity
----------- -------------------------------------------------- -----------
2 orange 154
(1 rows affected)
|
Exit the sqlcmd command prompt
To end your sqlcmd session, type QUIT:
Conclusion
In this guide we managed to install Ms SQL Server 2019 in Ubuntu 22.04