How To Install Microsoft SQL on Ubuntu 24.04 LTS

Install Microsoft SQL on Ubuntu 24.04

Microsoft SQL Server on Ubuntu represents a significant shift in Microsoft’s approach to cross-platform database solutions. For database administrators and developers working in mixed environments, installing SQL Server on Ubuntu 24.04 LTS offers an opportunity to leverage the reliability and security of Linux while utilizing Microsoft’s powerful database technology. This comprehensive guide walks you through the complete installation process, addressing the unique challenges posed by Ubuntu’s latest LTS release.

Understanding SQL Server on Linux

Microsoft’s decision to bring SQL Server to Linux marked a pivotal moment in the company’s history. Initially a Windows-exclusive product, SQL Server made its Linux debut in 2016, revolutionizing database management options for Linux users. This strategic move allowed organizations to leverage their existing SQL Server knowledge while benefiting from Linux’s well-known stability and security advantages.

The Linux implementation of SQL Server maintains core functionality while adapting to Linux’s architecture. Unlike the Windows version, SQL Server on Linux runs as a standalone service that integrates with the Linux ecosystem through specialized components. This architecture provides performance benefits through Linux’s efficient resource management and reduced licensing costs compared to Windows Server deployments.

For Ubuntu users specifically, running SQL Server means gaining access to enterprise-grade database capabilities without sacrificing the Ubuntu environment they prefer. The database engine delivers consistent performance across platforms, ensuring that applications behave predictably regardless of the underlying operating system.

System Requirements and Prerequisites

Before beginning the installation process, verify that your system meets Microsoft’s minimum requirements for running SQL Server on Ubuntu 24.04:

Processor: x64-compatible processor with at least 2 cores and 2GHz or faster clock speed (more cores recommended for production environments)
Memory: Minimum 2GB RAM (4GB or more recommended for optimal performance)
Storage: At least 6GB of available disk space (10GB or more recommended for databases and growth)
File System: EXT4 or XFS file systems only (BTRFS and other file systems are not supported)
User Access: Administrator (sudo) privileges on your Ubuntu system

It’s important to note that these specifications represent the absolute minimum requirements. For production workloads, you should consider significantly more powerful hardware, particularly in terms of memory and processor cores to handle concurrent connections and complex queries.

Preparing Your Ubuntu 24.04 System

Before installing SQL Server, you need to prepare your Ubuntu system to ensure a smooth installation process. Start by updating your existing packages to their latest versions:

sudo apt update && sudo apt -y upgrade

If kernel updates were applied during this process, reboot your system to ensure all changes take effect:

[ -f /var/run/reboot-required ] && sudo reboot -f

Next, install the essential dependencies that SQL Server requires:

sudo apt install curl wget gnupg software-properties-common apt-transport-https

At this point, it’s important to note that Microsoft has not yet released official packages for SQL Server specifically targeting Ubuntu 24.04. However, there are workarounds to install SQL Server using packages from previous Ubuntu versions.

Adding Microsoft Repositories to Ubuntu 24.04

Since official repositories for SQL Server on Ubuntu 24.04 are not yet available, we’ll need to add Microsoft’s repositories for Ubuntu 22.04 (Jammy) and adapt them for our installation.

First, import Microsoft’s GPG keys to ensure package authenticity:

curl -fsSL https://packages.microsoft.com/keys/microsoft.asc | sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/microsoft.gpg

Alternatively, you can use this command which avoids certain warnings during the update process:

curl https://packages.microsoft.com/keys/microsoft.asc | sudo tee /etc/apt/trusted.gpg.d/microsoft.asc

Next, register Microsoft’s SQL Server repository for Ubuntu 22.04:

sudo add-apt-repository "$(wget -qO- https://packages.microsoft.com/config/ubuntu/22.04/mssql-server-2022.list)"

Update your package lists to include the newly added repositories:

sudo apt update

Installing SQL Server Core Components

With the repositories configured, you can now proceed to install the SQL Server package:

sudo apt-get install -y mssql-server

During installation, you might see package dependency warnings, which is expected since we’re installing packages designed for Ubuntu 22.04 on a newer system. The installation should complete despite these warnings, but we’ll address any dependency issues in subsequent steps.

Addressing Dependency Issues on Ubuntu 24.04

One of the main challenges when installing SQL Server on Ubuntu 24.04 is dealing with missing dependencies. After installing the SQL Server package, you might find it won’t start due to missing libraries. Common missing dependencies include:

liblber-2.4.so.2
libldap_r-2.4.so.2
libssl.so.1.1
libcrypto.so.1.1

To identify exactly which libraries are missing on your system, run:

ldd /opt/mssql/bin/sqlservr | grep "not found"

For the commonly missing libraries, you’ll need to install compatible packages. One approach is to install the OpenLDAP 2.4 and OpenSSL 1.1 libraries from a previous Ubuntu release:

# For OpenLDAP libraries
wget http://archive.ubuntu.com/ubuntu/pool/main/o/openldap/libldap-2.4-2_2.4.49+dfsg-2ubuntu1.9_amd64.deb
sudo dpkg -i libldap-2.4-2_2.4.49+dfsg-2ubuntu1.9_amd64.deb

# For OpenSSL 1.1 libraries
wget http://archive.ubuntu.com/ubuntu/pool/main/o/openssl/libssl1.1_1.1.1f-1ubuntu2.19_amd64.deb
sudo dpkg -i libssl1.1_1.1.1f-1ubuntu2.19_amd64.deb

Another solution that some users have found effective is to locate the missing libraries in the /snap directory (if you have snap packages installed) and copy them to the system library directory:

# Example for finding and copying libraries
sudo find /snap -name "liblber-2.4.so.2" -exec cp {} /usr/lib/x86_64-linux-gnu/ \;
sudo find /snap -name "libldap_r-2.4.so.2" -exec cp {} /usr/lib/x86_64-linux-gnu/ \;

These steps should resolve the dependency issues that prevent SQL Server from starting.

Initial Configuration Setup

After installing the SQL Server package and addressing any dependency issues, you need to configure the SQL Server instance:

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

This command launches the configuration utility, which will guide you through several setup steps:

Accept the license terms
Choose your SQL Server edition (Developer, Express, Standard, Enterprise, etc.)
Set a strong password for the SA (System Administrator) account

When setting the SA password, ensure it meets Microsoft’s complexity requirements: at least 8 characters long and containing characters from three of the following categories: uppercase letters, lowercase letters, base-10 digits, and symbols.

After configuration completes, verify that SQL Server is running properly:

systemctl status mssql-server --no-pager

You should see an “active (running)” status if everything is configured correctly. If the service isn’t running, check the system logs for errors:

sudo journalctl -u mssql-server --no-pager

Installing SQL Server Command-Line Tools

To effectively manage your SQL Server instance, you’ll need to install the command-line tools. These tools allow you to execute Transact-SQL statements, create and manage databases, and perform various administrative tasks.

First, add the repository for Microsoft’s tools:

curl https://packages.microsoft.com/keys/microsoft.asc | sudo tee /etc/apt/trusted.gpg.d/microsoft.asc
curl https://packages.microsoft.com/config/ubuntu/22.04/prod.list | sudo tee /etc/apt/sources.list.d/msprod.list

Now install the command-line tools:

sudo apt-get update
sudo apt-get install -y mssql-tools unixodbc-dev

To make these tools easily accessible, add them to your PATH environment variable:

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

This ensures that commands like sqlcmd are available without specifying the full path.

Post-Installation Validation

With SQL Server and its tools installed, it’s essential to validate that everything is functioning correctly:

Check the service status again:

systemctl status mssql-server --no-pager

Verify you can connect to the SQL Server instance locally:
```
sqlcmd -S localhost -U SA
```
You’ll be prompted for the SA password you set during configuration. If you connect successfully, you’ll see a 1> prompt.
Run a test query to verify SQL Server is responding:
```
1> SELECT @@VERSION
2> GO
```
This should display detailed version information about your SQL Server installation.

Setting Up Remote Connectivity

By default, SQL Server listens on port 1433. To allow remote connections to your SQL Server instance, you need to configure your firewall:

sudo ufw allow 1433/tcp
sudo ufw reload

You may also need to modify SQL Server’s network configuration to accept remote connections. This is managed through the mssql-conf utility:

sudo /opt/mssql/bin/mssql-conf set network.ipaddress 0.0.0.0
sudo systemctl restart mssql-server

Setting the IP address to 0.0.0.0 allows SQL Server to listen on all network interfaces.

Creating and Managing Databases

Now that SQL Server is running properly, you can create your first database. Connect to SQL Server using the sqlcmd utility:

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

Create a new database with the following commands:

1> CREATE DATABASE TestDB
2> GO

You can verify the database was created by listing all databases:

1> SELECT name FROM sys.databases
2> GO

This will display a list of all databases, including your newly created TestDB.

To create a table in your new database:

1> USE TestDB
2> GO
1> CREATE TABLE Inventory (id INT, name NVARCHAR(50), quantity INT)
2> GO
1> INSERT INTO Inventory VALUES (1, 'Laptop', 100)
2> GO
1> SELECT * FROM Inventory
2> GO

These commands create a simple inventory table, insert a record, and verify the data was stored correctly.

Installing GUI Management Tools

While command-line tools are powerful, many administrators prefer graphical interfaces for database management. Since SQL Server Management Studio (SSMS) is only available on Windows, you’ll need alternative tools for Ubuntu:

Azure Data Studio

Azure Data Studio is Microsoft’s cross-platform database management tool:

sudo snap install azuredatastudio --classic

After installation, launch Azure Data Studio and connect to your SQL Server instance using:

Server: localhost
Authentication: SQL Login
User name: SA
Password: YourPassword

DBeaver Community Edition

DBeaver is another popular open-source database management tool that supports SQL Server:

sudo snap install dbeaver-ce

Both tools provide graphical interfaces for database administration, query execution, and performance monitoring.

Security Best Practices

Securing your SQL Server installation on Ubuntu 24.04 is critical. Consider implementing these security best practices:

Avoid using the SA account for routine operations. Create dedicated users with appropriate permissions:

1> CREATE LOGIN RegularUser WITH PASSWORD = 'StrongPassword123!'
2> GO
1> USE TestDB
2> GO
1> CREATE USER RegularUser FOR LOGIN RegularUser
2> GO
1> GRANT SELECT, INSERT, UPDATE, DELETE ON Inventory TO RegularUser
2> GO

Enable SQL Server’s Transparent Data Encryption (TDE) for sensitive databases:

1> USE master
2> GO
1> CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongMasterKeyPassword!'
2> GO
1> CREATE CERTIFICATE TDECertificate WITH SUBJECT = 'TDE Certificate'
2> GO
1> USE TestDB
2> GO
1> CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE TDECertificate
2> GO
1> ALTER DATABASE TestDB SET ENCRYPTION ON
2> GO

Configure regular security updates for both Ubuntu and SQL Server:

sudo apt install unattended-upgrades
sudo dpkg-reconfigure unattended-upgrades

These measures help establish a solid security foundation for your SQL Server installation.

Performance Tuning Considerations

To optimize SQL Server performance on Ubuntu 24.04, consider these adjustments:

Allocate appropriate memory to SQL Server:

sudo /opt/mssql/bin/mssql-conf set memory.memorylimitmb 4096
sudo systemctl restart mssql-server

Configure the tempdb database for optimal performance:

1> USE master
2> GO
1> ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, SIZE = 512MB, FILEGROWTH = 128MB)
2> GO
1> ALTER DATABASE tempdb MODIFY FILE (NAME = templog, SIZE = 128MB, FILEGROWTH = 64MB)
2> GO

Enable the Query Store for performance tracking:

1> USE TestDB
2> GO
1> ALTER DATABASE TestDB SET QUERY_STORE = ON
2> GO

These optimizations help ensure SQL Server runs efficiently on your Ubuntu system.

Troubleshooting Common Issues

When working with SQL Server on Ubuntu 24.04, you might encounter these common issues:

Service Won’t Start

If SQL Server fails to start:

sudo journalctl -u mssql-server --no-pager | tail -50

Look for missing libraries or permission issues. Common errors include:

Missing shared libraries (resolved by installing the appropriate packages)
Permission problems with data directories
Configuration errors in the mssql.conf file

Connection Problems

If you can’t connect to SQL Server:

# Check if SQL Server is listening on the network
sudo netstat -tulpn | grep 1433

# Verify firewall settings
sudo ufw status

Common connection issues include:

Firewall blocking port 1433
SQL Server configured to listen only on specific IP addresses
Authentication failures due to password complexity requirements

Performance Issues

For slow query performance:

1> USE TestDB
2> GO
1> SELECT TOP 10 total_worker_time/execution_count AS avg_cpu_time,
2> execution_count, 
3> SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
4> ((CASE qs.statement_end_offset
5> WHEN -1 THEN DATALENGTH(st.text)
6> ELSE qs.statement_end_offset
7> END - qs.statement_start_offset)/2) + 1) AS statement_text
8> FROM sys.dm_exec_query_stats AS qs
9> CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
10> ORDER BY avg_cpu_time DESC
11> GO

This query helps identify the most resource-intensive queries for optimization.

Backup and Recovery Strategy

Implementing a robust backup strategy is essential for production SQL Server deployments:

Configure regular full backups:

1> BACKUP DATABASE TestDB TO DISK = '/var/opt/mssql/data/TestDB_Full.bak' WITH INIT
2> GO

Set up transaction log backups for point-in-time recovery:

1> BACKUP LOG TestDB TO DISK = '/var/opt/mssql/data/TestDB_Log.bak' WITH INIT
2> GO

Test restore procedures regularly:

1> RESTORE DATABASE TestDB_Test FROM DISK = '/var/opt/mssql/data/TestDB_Full.bak' WITH MOVE 'TestDB' TO '/var/opt/mssql/data/TestDB_Test.mdf', MOVE 'TestDB_log' TO '/var/opt/mssql/data/TestDB_Test_log.ldf', RECOVERY, REPLACE
2> GO

Consider automating these backups using cron jobs or SQL Server Agent jobs for consistent protection of your data.

Uninstallation Process (if needed)

If you need to remove SQL Server from your Ubuntu 24.04 system:

# Stop the SQL Server service
sudo systemctl stop mssql-server

# Remove the SQL Server package
sudo apt-get remove -y mssql-server

# Remove the SQL Server tools
sudo apt-get remove -y mssql-tools unixodbc-dev

# Remove configuration and data files (optional - be careful!)
sudo rm -rf /var/opt/mssql/

Be extremely cautious with the last command, as it permanently deletes all databases and configuration data.

Additional Resources and Next Steps

To deepen your knowledge of SQL Server on Ubuntu, explore these resources:

Microsoft’s official documentation for SQL Server on Linux
The SQL Server community forums for Linux-specific questions
Online courses on SQL Server administration in Linux environments
GitHub repositories with example configurations and automation scripts

Consider exploring more advanced topics like:

High availability configurations with Always On Availability Groups
Integration with other open-source tools in the Linux ecosystem
Performance benchmarking and optimization techniques
Containerization of SQL Server using Docker

Congratulations! You have successfully installed Microsoft SQL. Thanks for using this tutorial for installing the Microsoft SQL Server database on Ubuntu 24.04 LTS system. For additional help or useful information, we recommend you check the official Microsoft website.

VPS Manage Service Offer

If you don’t have time to do all of this stuff, or if this is not your area of expertise, we offer a service to do “VPS Manage Service Offer”, starting from $10 (Paypal payment). Please contact us to get the best deal!