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:
- 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
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
|
|
Ensure that some necessary packages are installed
|
|
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:
|
|
Register the Microsoft SQL Server Ubuntu repository for SQL Server 2019:
|
|
Run the following commands to update the new repo and install SQL 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.
|
|
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
|
|
Starting and enabling the service
Once the configuration is done, verify that the service is running:
|
|
This is the output on my server
|
|
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
|
|
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.
|
|
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 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.
|
|
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>
.
|
|
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:
|
|
To get names of all the databases:
|
|
Let us create a new table, Users, and insert new rows.
Switch context to the new CitizixDB
database:
|
|
Create new table named Users
:
|
|
Insert data into the new table:
|
|
Select data from our database table
|
|
To end your sqlcmd
session, type QUIT
:
|
|
Conclusion
In this guide, we managed to install Ms SQL Server 2019 in an Ubuntu 20.04 server.