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:
- 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 install and setup Mssql server on Centos 8
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
|
|
Once done, let us install common packages that will be essential during the installation.
|
|
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:
|
|
Manually download and register the SQL Server Ubuntu repository:
|
|
If you get this sudo: add-apt-repository: command not found
then install with:
|
|
Update the repositories and install SQL Server:
|
|
After the package installation finishes, run mssql-conf setup
using its full path, and follow the prompts to set the SA password.
|
|
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).
|
|
Once the configuration is done, verify that the service is running:
|
|
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!
Install the SQL Server command-line tools
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.
|
|
Register the Microsoft Ubuntu repository.
|
|
Update the sources list and run the installation command with the unixODBC developer package.
|
|
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:
|
|
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:
|
|
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
|
|
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
|
|
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:
|
|
Use this to query the name of all of the databases on your server
|
|
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:
|
|
Create new table named Inventory
:
|
|
Insert data into the new table:
|
|
Select data
Use this query to fetch data from the Inventory
table where the quantity is greater than 152:
|
|
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