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.
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 upgradeStep 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 -yStep 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/nullNext, 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.listMSSQL 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.listMSSQL 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.listStep 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 updateStep 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-devAfter 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 ~/.bashrcStep 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-serverUpon 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 setupDuring 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-serverStep 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
sqlcmdutility 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 upgradeRemoving MSSQL on Ubuntu Linux
Removing the Microsoft SQL Server is relatively easy. Stop the service with the following command:
sudo systemctl stop mssql-serverNext, remove the service and all associated packages with the following command:
sudo apt remove mssql-server mssql-tools unixodbc-devLastly, 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.gpgConclusion
We’ve embarked on a comprehensive journey, exploring various aspects of Microsoft SQL Server’s deployment and management on the Ubuntu Linux distribution. We’ve covered the initial installation of the server, discussed the configuration process, delved into creating MSSQL usernames and databases, and addressed the essential system management operations such as updating and removal. As a robust and reliable platform, Microsoft SQL Server on Ubuntu Linux provides a compelling solution for your database needs. It seamlessly integrates the enterprise-level functionality of MSSQL with the open-source flexibility of Ubuntu, delivering a powerful combination for various applications.