Install Microsoft SQL Server on Ubuntu 14.02.24

How to Install Microsoft SQL Server on Ubuntu 20.04
Last Updated on Thursday, September 7, 2023 by Joshua James

Microsoft SQL Server is a leading relational database management system (RDBMS) known for its robust capabilities in data management, business intelligence, and security. With the 2017 edition, Microsoft extended SQL Server support to Linux, including Ubuntu 20.04 LTS, also known as ‘Focal Fossa.’

Key Advantages of Microsoft SQL Server

Performance and Scalability: Designed to manage large databases and complex transactions efficiently.
    Security Features: Offers transparent data encryption and advanced threat protection.
    Business Intelligence Tools: Includes Reporting Services (SSRS) and Integration Services (SSIS).
    High Availability: Features like AlwaysOn Availability Groups ensure data is always accessible.
    Microsoft Integration: Seamlessly works with other Microsoft products like Azure and Power BI.

Available Versions

Enterprise: Feature-rich for large organizations.
    Standard: For medium-sized businesses.
    Web: Affordable, designed for web hosting.
    Developer: Free for development and testing.
    Express: Free, limited, ideal for small-scale applications.

The focus of this guide is to walk you through the installation of Microsoft SQL Server on Ubuntu 20.04 LTS. Whether you’re a database administrator or a developer, this guide aims to simplify the installation process for you.

This method is only available on Ubuntu 20.04 LTS Focal Fossa; the newer LTS release of Ubuntu 22.04 Jammy Jellyfish is not supported now.

Table of Contents

Section 1: Install Microsoft SQL Server on Ubuntu 20.04
        Step 1: Update the Ubuntu System Before Microsoft SQL Server Installation
        Step 2: Install Initial Packages for Microsoft SQL Server on Ubuntu 20.04
        Step 3: Import Microsoft SQL Server APT Repository on Ubuntu 20.04
        Step 4: Updating APT Package Index
        Step 5: Install MSSQL Server and Tools on Ubuntu 20.04
        Step 6: Validating the Installation of Microsoft SQL Server on Ubuntu 20.04
    Section 2: Configure Microsoft SQL Server on Ubuntu 20.04
        Step 1: Running the Configuration Script
        Step 2: Applying Configuration Changes
        Step 3: Verifying the Configuration
    Section 3: Create a Database and User Account in MSSQL Server on Ubuntu 20.04
        Step 1: Launch the SQL Command Line Interface
        Step 2: Create a New Database with MSSQL on Ubuntu
        Step 3: Create a New User with MSSQL on Ubuntu
        Step 4: Grant Permissions to the User with MSSQL on Ubuntu
    Section 4: Manage MSSQL on Ubuntu 20.04
        Updating MSSQL on Ubuntu 20.04
    Conclusion

Section 1: Install Microsoft SQL Server on Ubuntu 20.04
Step 1: Update the Ubuntu System Before Microsoft SQL Server Installation

To ensure the compatibility and smooth installation of Microsoft SQL Server, it’s a good practice to update and upgrade your Ubuntu system. This process helps you get the latest bug fixes, security patches, and software updates for your system. The apt update and apt upgrade commands are used to accomplish this task:

sudo apt update
sudo apt upgrade

Step 2: Install Initial Packages for Microsoft SQL Server on Ubuntu 20.04

Before diving into the installation of Microsoft SQL Server, some essential packages need to be installed. These packages are crucial for importing the APT repository of SQL Server, facilitating network operations, managing certificates, etc. Execute the following command to install these packages:

sudo apt install dirmngr ca-certificates software-properties-common apt-transport-https curl lsb-release -y

Step 3: Import Microsoft SQL Server APT Repository on Ubuntu 20.04

The SQL Server APT repository contains the necessary packages for the SQL Server installation. This repository needs to be imported before installing SQL Server. This process involves two steps: First, importing the GPG key, which is required to ensure the integrity and security of the repository; second, adding the repository to your system.

For the GPG key import, run the following command:

curl -s https://packages.microsoft.com/keys/microsoft.asc | gpg --dearmor | sudo tee /usr/share/keyrings/microsoft-sql.gpg > /dev/null

Next, add the repository for Microsoft SQL Server; only import 2022 or 2019! Then, the MSSQL tools repository:

MSSQL 2022:

echo "deb [arch=amd64,armhf,arm64 signed-by=/usr/share/keyrings/microsoft-sql.gpg] https://packages.microsoft.com/ubuntu/20.04/mssql-server-2022 focal main" | sudo tee -a /etc/apt/sources.list.d/mssql.list

MSSQL 2019:

echo "deb [arch=amd64,armhf,arm64 signed-by=/usr/share/keyrings/microsoft-sql.gpg] https://packages.microsoft.com/ubuntu/20.04/mssql-server-2019 focal main" | sudo -a tee /etc/apt/sources.list.d/mssql.list

MSSQL Tools Repository:

echo "deb [arch=amd64,armhf,arm64 signed-by=/usr/share/keyrings/microsoft-sql.gpg] https://packages.microsoft.com/ubuntu/20.04/prod/ focal main" | sudo tee -a /etc/apt/sources.list.d/mssql.list

Step 4: Updating APT Package Index

With the repository incorporated, updating your APT package index is essential. This operation helps the system become aware of the new packages available for installation from the recently added SQL Server repository:

sudo apt update

Step 5: Install MSSQL Server and Tools on Ubuntu 20.04

You are now ready to install Microsoft SQL Server and its associated tools. These tools include sqlcmd and bcp, command-line tools for SQL Server that can run queries and perform other operations:

sudo apt install mssql-server mssql-tools unixodbc-dev

After the installation, the MSSQL Tools folder must be added to your system path. This step allows access to these tools from any location in the terminal, regardless of your current directory. To add the Tools folder to your system path, execute the following commands:

echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
source ~/.bashrc

Step 6: Validating the Installation of Microsoft SQL Server on Ubuntu 20.04

Verifying the installation is critical to ensure your SQL Server is properly installed and ready to operate. You can do this by checking the status of the SQL Server service using the systemctl command. This command-line tool examines and controls the state of the “systemd” system and service manager.

Use the following command to check the status of the SQL Server service:

systemctl status mssql-server

Upon successful execution of this command, you should see output indicating that the mssql-server service is active and running.
Section 2: Configure Microsoft SQL Server on Ubuntu 20.04
Step 1: Running the Configuration Script

Upon successful installation of Microsoft SQL Server, an integral step involves running a configuration script included with the installation. This script, located at /opt/mssql/bin/mssql-conf setup, accomplishes several critical tasks:

It presents the Microsoft SQL Server license terms for your agreement.
    It allows you to establish the password for the ‘SA’ (System Administrator) account. This account possesses the highest level of permissions in the SQL Server instance.
    It offers the option to enable SQL Server to start on system boot automatically.

Invoke this script by executing the following command in your terminal:

sudo /opt/mssql/bin/mssql-conf setup

During this process, you will be interactively guided through these tasks. Be sure to carefully note down the SA password you establish, as it will be necessary for logging into the SQL Server in the future.
Step 2: Applying Configuration Changes

The changes made by the configuration script are profound and affect how SQL Server will operate on your system. To make these changes take effect, it is necessary to restart the SQL Server service. This action will ensure that SQL Server is running with the latest configuration. Use the following command to restart the service:

sudo systemctl restart mssql-server

Step 3: Verifying the Configuration

With the SQL Server service restarted, we can now verify that the configuration process was successful. This is accomplished by attempting to connect to the SQL Server using the sqlcmd utility. This command-line tool allows us to run SQL scripts and commands, making it an excellent resource for testing the database connection.

To connect to the server, you’ll use the sqlcmd command followed by the -S option to specify the server (in this case, localhost), the -U option to provide the username (SA), and the -P option to enter the password you established for the SA account earlier. Be sure to replace ‘password’ in the command below with the actual password you set:

sqlcmd -S localhost -U SA -P 'password'

If the connection is successful, you will see a 1> prompt, indicating that you are connected to SQL Server and ready to execute SQL commands.

The sqlcmd utility is a powerful tool that can directly interact with your SQL Server. As such, it should be used judiciously and only by users who have a clear understanding of SQL Server operations.

Section 3: Create a Database and User Account in MSSQL Server on Ubuntu 20.04

Once your MSSQL Server instance is up and running, one of the first tasks you’ll likely need to perform is creating a database and a user account. This step is crucial for managing your data and controlling access to it.
Step 1: Launch the SQL Command Line Interface

The sqlcmd utility, as mentioned before, is a powerful command-line interface for SQL Server. It enables users to run SQL scripts and commands directly.

To connect to the SQL Server with the System Administrator (SA) account, use the command below, replacing ‘password’ with the password you set up for the SA account:

sqlcmd -S localhost -U SA -P 'password'

After running the command, you’ll see a 1> prompt, indicating that you are connected to the SQL Server and ready to execute SQL commands.
Step 2: Create a New Database with MSSQL on Ubuntu

Creating a new database in SQL Server is straightforward. To create a database named myDB, for instance, you would run the following command at the SQL command prompt:

CREATE DATABASE myDB;

Be sure to replace myDB with the name you want for your database. It’s important to include the semi-colon (;) at the end of the command, which signifies the end of the SQL statement.

After entering the command, press Enter on your keyboard to execute it. You should see a Command(s) completed successfully message if everything went as expected.
Step 3: Create a New User with MSSQL on Ubuntu

Now, let’s create a new user account with permissions to this database. To do this, we’ll first switch our context to the new database using the USE command:

USE myDB;

Next, we’ll create a new user named myUser with a password of myPassword:

CREATE LOGIN myUser WITH PASSWORD = 'myPassword';
CREATE USER myUser FOR LOGIN myUser;

Again, replace myUser and myPassword with the username and password you want to use.
Step 4: Grant Permissions to the User with MSSQL on Ubuntu

With the user created, the final step is to grant them the appropriate permissions. For this example, let’s grant our new user the db_datareader and db_datawriter roles, which will allow them to read and write data in our database:

ALTER ROLE db_datareader ADD MEMBER myUser;
ALTER ROLE db_datawriter ADD MEMBER myUser;

Once these steps are completed, the new user can connect to the myDB database and perform read/write operations.

Remember, managing access to your data is a crucial aspect of database administration. Always use the principle of least privilege when granting permissions, i.e., only give users the permissions they need and nothing more.
Section 4: Manage MSSQL on Ubuntu 20.04
Updating MSSQL on Ubuntu 20.04

Given you installed the Microsoft SQL Server using the APT package manager, run the standard update and upgrade command to check and proceed with updates. Remember in production to ensure you test updates before going live:

sudo apt update
sudo apt upgrade

Removing MSSQL on Ubuntu Linux

Removing the Microsoft SQL Server is relatively easy. Stop the service with the following command:

sudo systemctl stop mssql-server

Next, remove the service and all associated packages with the following command:

sudo apt remove mssql-server mssql-tools unixodbc-dev

Lastly, remove the Microsoft repository and GPG key with the following command if you plan on not re-installing the SQL server again:

sudo rm /etc/apt/sources.list.d/mssql.list
sudo rm /usr/share/keyrings/microsoft-sql.gpg

https://www.linuxcapable.com/how-to-install-microsoft-sql-server-on-ubuntu-linux/

Anonymous

14.02.24

https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-replication-configure?view=sql-server-ver16

Anonymous

```command
sudo apt update
sudo apt upgrade
sudo apt install neofetch emacs-nox dirmngr ca-certificates software-properties-common apt-transport-https curl lsb-release iputils-ping -y
curl -s https://packages.microsoft.com/keys/microsoft.asc | gpg --dearmor | sudo tee /usr/share/keyrings/microsoft-sql.gpg > /dev/null
echo "deb [arch=amd64,armhf,arm64 signed-by=/usr/share/keyrings/microsoft-sql.gpg] https://packages.microsoft.com/ubuntu/22.04/mssql-server-2022 jammy main" | sudo tee -a /etc/apt/sources.list.d/mssql.list
echo "deb [arch=amd64,armhf,arm64 signed-by=/usr/share/keyrings/microsoft-sql.gpg] https://packages.microsoft.com/ubuntu/22.04/prod/ jammy main" | sudo tee -a /etc/apt/sources.list.d/mssql.list
sudo apt-get update
sudo apt-get upgrade
sudo apt install mssql-server mssql-tools unixodbc-dev
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
source ~/.bashrc
sudo /opt/mssql/bin/mssql-conf setup
sudo /opt/mssql/bin/mssql-conf set sqlagent.enabled true
systemctl restart mssql-server.service
sudo mkdir /var/opt/mssql/data/ReplData/
sudo chown mssql /var/opt/mssql/data/ReplData/
sudo chgrp mssql /var/opt/mssql/data/ReplData/
```

# And I run this in the mssql studio on this server
```tsql
USE msdb;
GO
exec sys.sp_MSrepl_createdatatypemappings;
GO

14.02.24

Anonymous

Install Microsoft SQL Server on Ubuntu 14.02.24

Information Epoch 1714314180