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.
- How to install Mssql server on Rocky Linux 8/Centos 8
- How to install Ms SQL Server 2019 on Ubuntu 20.04
- Install and set up php to connect to MsSQL Server Centos 8
- How to run MsSQL Server 2019 with Docker and Docker-Compose
- Updated ubuntu server
Table of Contet
- Ensure that the server is up to date
- Installing MsSQL Server 2019
- Configuring mssql server
- Starting and enabling the service
- Install the SQL Server command-line tools
- Connecting locally
- Executing basic sql queries
1. 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
sudo apt update sudo apt upgrade -y
Ensure that some necessary packages are installed
sudo apt install -y wget curl
2. 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:
wget -qO- https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
Register the Microsoft SQL Server Ubuntu repository for SQL Server 2019:
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:
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.
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
$ 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 → /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:
sudo systemctl status mssql-server --no-pager
This is the output on my server
$ 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 ├─21584 /opt/mssql/bin/sqlservr └─21615 /opt/mssql/bin/sqlservr Nov 24 09:45:25 ubuntusrv.citizix.com sqlservr: [78B blob data] Nov 24 09:45:25 ubuntusrv.citizix.com sqlservr: [84B blob data] Nov 24 09:45:25 ubuntusrv.citizix.com sqlservr: [145B blob data] Nov 24 09:45:26 ubuntusrv.citizix.com sqlservr: [96B blob data] Nov 24 09:45:26 ubuntusrv.citizix.com sqlservr: [66B blob data] Nov 24 09:45:26 ubuntusrv.citizix.com sqlservr: [75B blob data] Nov 24 09:45:26 ubuntusrv.citizix.com sqlservr: [96B blob data] Nov 24 09:45:26 ubuntusrv.citizix.com sqlservr: [100B blob data] Nov 24 09:45:26 ubuntusrv.citizix.com sqlservr: [71B blob data] Nov 24 09:45:26 ubuntusrv.citizix.com sqlservr: [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
sudo systemctl enable mssql-server
Install the SQL Server command-line tools
Use the following steps to install the mssql-tools on Ubuntu.
Import the public repository GPG keys.
curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
Register the Microsoft Ubuntu repository.
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.
sudo apt-get update sudo apt-get install mssql-tools unixodbc-dev
/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:
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:
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc source ~/.bashrc
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.
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:
$ 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> CREATE DATABASE CitizixDB 2> GO
To get names of all the databases:
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
1> use CitizixDB 2> GO Changed database context to 'CitizixDB'.
Create new table named
1> CREATE TABLE Users (id INT, name NVARCHAR(50), age INT) 2> GO
Insert data into the new table:
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> SELECT * FROM Users WHERE age > 50; 2> GO id name age ----------- -------------------------------------------------- ----------- 1 etowett 72 (1 rows affected)
To end your sqlcmd session, type
In this guide, we managed to install Ms SQL Server 2019 in an Ubuntu 20.04 server.