SQL Server on Linux: Getting Started with SQL Server 2022 on Ubuntu 22.04

Microsoft’s extension of SQL Server to the Linux platform marked a significant shift in its strategy, recognizing Linux as a major player in enterprise environments. This move expanded the reach of SQL Server to a larger audience and provided more deployment options for organizations. Today we will explore installing and configuring SQL Server on an Ubuntu 22.04 Linux host.

Why SQL Server on Linux?

The demand for high-performance, secure, and reliable database systems is prevalent in many enterprise settings, and Linux is a popular choice due to its stability and performance. By offering SQL Server on Linux, Microsoft provided an opportunity for businesses to leverage SQL Server’s capabilities on their preferred operating system.

Features Not Available on Linux

While SQL Server on Linux offers a wide range of features, there are certain capabilities such as Reporting Services and Analysis Services that are still exclusive to the Windows environment. It’s important to be aware of these limitations when considering SQL Server for Linux. For a detailed list of features not available on Linux, please refer to Microsoft’s official documentation.

Installing SQL Server on Ubuntu 22.04

Ubuntu is a widely-used Linux distribution known for its ease of use and popularity among developers. SQL Server on Linux is also supported on Redhat and SUSE Linux distributions. More information about the differences between installation procedures for the three distributions can be found in the SQL Server for Linux official documentation. We are using Ubuntu 22.04 here due to its ease of use, free licensing, and wide availability.

Here’s how you can install SQL Server on Ubuntu 22.04:

1. Add Microsoft’s Repository:

sudo su 
curl -o /etc/apt/sources.list.d/mssql-server.list https://packages.microsoft.com/config/ubuntu/20.04/mssql-server-2022.list
exit

2. Update the package list:

sudo apt update

3. Install SQL Server:

sudo apt install -y mssql-server

4. Configure SQL Server:

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

5. Start SQL Server:

sudo systemctl start mssql-server

Installing SQL Server Tools on Ubuntu 22.04

To effectively manage and interact with SQL Server on Ubuntu 22.04, you’ll need to install the SQL Server tools. These tools include command-line utilities and graphical interfaces that make database administration and development tasks easier.

1. Install the SQL Server Command-Line Tools (sqlcmd and bcp):

To install the SQL Server command-line tools, open your terminal and run the following commands:

sudo su
curl -o /etc/apt/sources.list.d/msprod.list https://packages.microsoft.com/config/ubuntu/20.04/prod.list
exit

Now, update the package list and install the tools and the UNIX ODBC driver:

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

2. Add the SQL Server Tools to Your PATH:

To use the SQL Server command-line tools easily, add them to your system’s PATH environment variable:

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

You can now use sqlcmd and bcp from your terminal to interact with SQL Server.

Basic SQL Server Configuration on Linux

Once installed, there are several configuration settings you can adjust to optimize SQL Server for your environment. SQL Server will need to be restarted for any configuration changes to take effect. We will take a look at a few examples below:

• Enabling the SQL Server Agent:

sudo /opt/mssql/bin/mssql-conf set sqlagent.enabled true

• Configuring SQL Server Max Memory to 16GB:

sudo /opt/mssql/bin/mssql-conf set memory.memorylimitmb 16000

• Adjusting Worker Threads:

sudo /opt/mssql/bin/mssql-conf set hadr.num_worker_threads 300

• Restart for configuration changes to take effect:

sudo systemctl restart mssql-server

• Stop the SQL Server Service and Agent:

sudo systemctl stop mssql-server

Connecting to SQL Server using sqlcmd

Once you have SQL Server and the SQL Server tools installed on your Ubuntu 22.04 system, you can use the command-line tool sqlcmd to connect to SQL Server and execute SQL queries. Here’s how you can connect:

1. Open your terminal:

Launch your terminal on Ubuntu.

2. Connect to SQL Server:

Use the following command to connect to SQL Server. Replace <username> and <password> with your SQL Server credentials:

sqlcmd -S localhost -U <username> -P <password>

3. Execute SQL Queries:

Once connected, you can execute SQL queries directly from the command line. For example:

SELECT * FROM your_table;

4. Exit sqlcmd:

To exit sqlcmd, simply type:

QUIT

Using sqlcmd is a convenient way to interact with your SQL Server database from the command line on Linux.

Running SQL Server on Linux using Docker and Azure Data Studio

What is Docker?

Docker is a platform that allows developers to create, deploy, and run applications in containers. This ensures consistency across various stages of the development lifecycle.

Why Use Docker for SQL Server on Linux?

For developers and database administrators working on Windows machines but needing to deploy on Linux servers, Docker provides an efficient solution. By running SQL Server on Linux inside a Docker container, you can emulate the exact environment in which your application will run in production. It also provides an easy way to spin up a SQL Server instance on your desktop machine for testing purposes.

Setting Up SQL Server on Linux using Docker:

1. Install Docker from the Docker official website.
2. Pull the SQL Server Docker Image:

docker pull mcr.microsoft.com/mssql/server

3. Run SQL Server in a Docker Container:

docker run -e “ACCEPT_EULA=Y” -e “SA_PASSWORD=<your_password>” -p 1433:1433 –name sql_container -d mcr.microsoft.com/mssql/server

Connect to SQL Server using Azure Data Studio:

1. Download and install Azure Data Studio.
2. Open Azure Data Studio and click on New Connection.
3. In the Server field, enter localhost,1433.
4. For Authentication Type, select SQL Login, and then enter sa as the user and your previously set password for the password field.
5. Click Connect.

Conclusion

The integration of SQL Server with Linux exemplifies the industry’s move towards flexibility and choice. With SQL Server on Linux, businesses have another robust option for their database management needs. Leveraging Docker to run SQL Server on Linux offers flexibility and ensures a consistent development environment. Combined with Azure Data Studio, developers and DBAs can experience a seamless database management process regardless of their primary OS.

SQL Server is an excellent enterprise database management system on both the Windows and Linux platforms. We will take a closer look at SQL Server on Linux in future posts and cover some of the common questions asked by DBAs.