How to Install Microsoft SQL Server 2019 Developer Edition and SQL Command Line Tools on Ubuntu 20.04 LTS
In this article, I will show you how to install Microsoft SQL Server 2019 Developer Edition and Command Line Tools on Ubuntu 20.04 LTS. The most recent version of SQL Server is 2019. The developer edition we’re about to install is free. The MS SQL Server Developer edition is licensed for use as a development and test database in a non-production environment. It’s a fully featured free edition.
SQL Server is supported on Red Hat Enterprise Linux (RHEL), SUSE Linux Enterprise Server (SLES), and Windows. It is also supported as a Docker image, which can run on Docker Engine on Linux.
Let me use this opportunity to briefly talk about other editions of MS SQL Server 2019.
Types of SQL Server 2019 Editions
There are 4 different types of editions available in SQL Server 2019.
- The Enterprise edition has more features than any other edition. It is the top-end edition for production use and involves license costs.
2. The second edition is Standard — The Standard edition can also be used for production purposes and also involve license costs. It has fewer features than Enterprise.
3. The third edition is Express — The Express edition is free software and it’s free to download and install. It has a 10 GB size limit, only used for small applications.
4. The last Edition is the Developer Edition. The Developer is the same as Enterprise but for non-production use.
You explore the SQL Server 2019 download page here.
It is used for learning, building and testing purposes only. I am using SQL Server 2019 Developer Edition because it is free to download and install and has all the functionalities similar to the Enterprise Edition.
Installing SQL Server 2019 Developer Edition
To install SQL Server 2019, take the following steps:
Step 1 — Prepare/update your local system
Let’s make sure that our local system is up to date and has the necessary packages loaded before we begin installing SQL Server 2019, command-line tools, and related administration applications. Run the following commands by opening a terminal. First, your local package cache will be updated. The second one installs the curlpackage which will be required for future commands.
sudo apt-get update sudo apt
install curl libxss1 libgconf-2-4 libunwind8
The next thing is to import the GPG keys from the public repository with the command below:
curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
Note: Do not disregard the trailing dash (-) in the aforementioned instruction. It is necessary because it is crucial. Alternatively, wget could have been used to accomplish this as follows: wget -qO-https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
Step 2 — Download and Install the SQL Server 2019 packages
The actual software packages for SQL Server 2019 and command-line tools need to be downloaded at this point because the local system has been prepared. Use one of the following commands to add the package repository before installing SQL Server 2019:
1. $sudo add-apt-repository "$(wget -qO- https://packages.microsoft.com/config/ubuntu/20.04/mssql-server-2019.list)" OR
2. curl https://packages.microsoft.com/config/ubuntu/20.04/mssql-server-2019.list | sudo tee /etc/apt/sources.list.d/mssql-server-2019.list
Now, add the package repository for the command-line tools provided by Microsoft accordingly using the command below:
curl https://packages.microsoft.com/config/ubuntu/20.04/prod.list | sudo tee /etc/apt/sources.list.d/msprod.list
Another option is to use the data from each package repository directly. Below are some additional entries as well as the associated sources.
# SQL Server 2019 deb [arch=amd64,armhf,arm64] https://packages.microsoft.com/ubuntu/20.04/mssql-server-2019 focal main # SQL command-line tools as part of the "productivity collection" deb [arch=amd64,armhf,arm64] https://packages.microsoft.com/ubuntu/20.04/prod focal main # A few additional Microsoft repos for reference... # Edge deb [arch=amd64] http://packages.microsoft.com/repos/edge/ stable main # Azure CLI deb [arch=amd64] https://packages.microsoft.com/repos/azure-cli/ focal main # Visual Studio Code deb [arch=amd64,arm64,armhf] http://packages.microsoft.com/repos/code stable mainNote: You could choose to have a single microsoft.list file that contains all of the Microsoft package sources as opposed to having numerous ones.
It’s time to update the local package cache and populate it with information about the new packages that are now available thanks to the recently added repositories for apt.
sudo apt updateYou can perform a search for packages linked to SQL Server in the package cache after a successful package upgrade using the command below:
sudo apt search mssql
If you prefer lesser information, you can run the command as the one shown below:
sudo apt list mssql*
A list of the SQL Server 2019 packages that are currently available will be displayed in the command’s output as shown above in the screenshots. You might make a different decision, but I’m going to set up the real database engine, complete with full-text search, the most recent command-line tools, and ODBC driver compatibility for Unix/Linux computers.
Use the command below to install and set up the database engine and recent command-line tools and the ODBC driver:
sudo apt install -y mssql-server mssql-server-fts mssql-tools18 unixodbc-dev
While the installation is running, you will be prompted to accept the terms and conditions for both the MSSQL Server Installation and MSODBC Tools. Use the left-arrow key in your keyboard to highlight on the “yes” and press the Enter key to accept it.
Step 3 — Setup Recommended Configuration
Following installation, your system does not yet have SQL Server 2019 running. Run the mssql-conf setup command, choose your edition, and then follow the on-screen instructions to set the Super-Administrator (SA) account password.
sudo /opt/mssql/bin/mssql-conf setup accept-eula
From the screenshot above, we selected the SQL Server Edition as Developer represented with 2. You will be prompted to enter the SA password, enter and confirm the password to complete the setup.
Step 4 — Verify Packages
Once the setup is complete, make that the service is running as expected.
sudo systemctl status mssql-server --no-pager
One more thing to do is to add the SQL Server tools to the path by default using the command below:
echo 'export PATH="$PATH:/opt/mssql-tools18/bin"' >> ~/.bash_profile echo 'export PATH="$PATH:/opt/mssql-tools18/bin"' >> ~/.bashrc source ~/.bashrcStep 5 — Using the sqlcmd Command
Let’s attempt to learn more. The sqlcmd is one of SQL Server 2019's command-line utilities. You can connect to local and remote instances of SQL Server, including Azure SQL, using the SQL Server command line tool. To find out the product version and edition of your installation, run the following command.
sqlcmd -S localhost -C -U SA -Q "SELECT @@VERSION"When you press Enter, you will be prompted to enter the administrator’s password. Go ahead and enter the password and press enter to get a brief overview of the SQL Server details.
As you can see from the screenshot above, MS SQL Server 2019 version and the Developer edition have been installed on our system.
You can connect locally to the MSSQL Console by running in the terminal:
sqlcmd -S localhost -U sa -P 'YourPassword' -C
You are now connected locally using the server name “localhost”. SA in the command is the username, and the password is the one you specified while setting up the SA account.
Remember to add the -C (adds the TrustServerCertificate) flag to avoid encountering a connection error as shown below:
Note: To connect to the database server/instance and the database itself when working with SQL Server on a Windows system, the usual choice is SQL Server Management Studio (SSMS). Unfortunately, Linux users cannot access SQL Server Management Studio. And probably won’t be anytime soon. There are at least two alternatives to SQL Server Management Studio that are as functional and comfortable on Linux i.e. Visual Studio Code with SQL Server (mssql) extension and Azure Data Studio.
Download and install Azure Data Studio.
Or
Follow these steps to install VSCode on Ubuntu and then install the MSSQL VSCode extension. Install Visual Studio Code using the snap or apt by running any of the commands:
sudo snap install --classic code OR sudo apt install code
Now, update the system packages with:
$sudo apt update
$sudo apt upgradeStarting Visual Studio Code
In the Activities search bar type “Visual Studio Code” and click on the icon to launch the application. When the VSCode opens, search for MSSQL Extension and install it.
Alternatively; you can open Visual Studio Code directly from the terminal by running:
code Links for further learning:
