Install Latest MS SQL Server with Docker on Ubuntu 22.04: Best Setup

By Posted on

Install Latest MS SQL Server with Docker on Ubuntu 22.04: Best Setup

Install Latest MS SQL Server with Docker on Ubuntu 22.04: Best Setup

This guide provides a step-by-step walkthrough on how to Install Latest MS SQL Server with Docker on Ubuntu 22.04. As SQL Server 2022 has a preview list for Ubuntu 22.04, Docker offers a convenient way to install and run the latest SQL Server on this operating system. Microsoft SQL Server is a robust database management system, and Docker simplifies its deployment on Linux distributions. These instructions, inspired by the approach recommended by orcacore, will guide you through the process of Install Latest MS SQL Server with Docker on Ubuntu 22.04.

Before you begin to Install Latest MS SQL Server with Docker on Ubuntu 22.04, let’s review the necessary prerequisites.

Requirements for MS SQL Setup with Docker

  • Server Access: You need access to an Ubuntu 22.04 server as a non-root user with sudo privileges. Refer to an Ubuntu 22.04 initial server setup guide for assistance.
  • Docker Installation: Docker must be installed and running on your server. Consult a Docker installation guide for Ubuntu 22.04 if needed.

With these prerequisites in place, you can proceed to Install Latest MS SQL Server with Docker on Ubuntu 22.04 by following the steps below.

Step 1 – Verify Docker Status on Ubuntu 22.04

First, ensure that Docker is active and running on your server. Use the following command:

sudo systemctl status docker

The output should resemble this:

**Output**
● docker.service - Docker Application Container Engine
     Loaded: loaded (/lib/systemd/system/docker.service; enabled; vendor preset>
     Active: **active** (**running**) since Wed 2023-09-20 07:53:02 UTC; 40s ago
TriggeredBy: ● docker.socket
       Docs: https://docs.docker.com
   Main PID: 8985 (dockerd)
      Tasks: 9
     Memory: 27.0M
        CPU: 549ms
     CGroup: /system.slice/docker.service
             └─8985 /usr/bin/dockerd -H fd:// --containerd=/run/containerd/cont>

This confirms that the Docker service is running.

Step 2 – Pull MS SQL Server Docker Image

Next, use the docker pull command to download the latest MS SQL Server image for Ubuntu 22.04. Execute the following:

docker pull mcr.microsoft.com/mssql/server:2022-latest

Upon completion, you’ll see output similar to:

**Output**
2022-latest: Pulling from mssql/server
e481c36a257f: Pull complete
7d8f784510c6: Pull complete
c6ac2a806f6c: Pull complete
1b1611617f54: Pull complete
Digest: sha256:d00c97dedf4f5c785bbbd5894f490d50124ff409d7751fbba2d53eb7de9965da
Status: Downloaded newer image for mcr.microsoft.com/mssql/server:2022-latest
mcr.microsoft.com/mssql/server:2022-latest

This indicates that the SQL Server image has been successfully downloaded.

Step 3 – Create and Run MS SQL Server Docker Container on Ubuntu 22.04

Now, create a container to run the SQL Server image. Use the following command:

docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=Strongpassword" 
   -p 1433:1433 --name sql1 --hostname sql1 
   -d 
   mcr.microsoft.com/mssql/server:2022-latest

Explanation of the options:

  • -e "ACCEPT_EULA=Y": Accepts the SQL Server End-User License Agreement.
  • -e "MSSQL_SA_PASSWORD=Strongpassword": Sets the password for the system administrator (SA) account. Important: Replace "Strongpassword" with a strong, secure password.
  • -p 1433:1433: Maps port 1433 on the host to port 1433 in the container (SQL Server’s default port).
  • --name sql1: Assigns the name "sql1" to the container.
  • --hostname sql1: Sets the hostname of the container to "sql1".
  • -d: Runs the container in detached mode (in the background).

Upon successful execution, you’ll receive an output similar to:

**Output**
9542b451c2f120f4441da3f7e2f3a410681aa9eacde07191bffea408ab523a75

Note: Ensure your password meets the minimum complexity requirements: at least 8 characters, including uppercase letters, digits, and symbols.

Step 3 – Verify your SQL Server Container is Running on Ubuntu 22.04

Verify that the container is running with the following command:

docker ps -a

The output should show the SQL Server container in the list of running containers:

**Output**
CONTAINER ID   IMAGE                                        COMMAND                  CREATED         STATUS         PORTS                                       NAMES
fef8fd9b00be   mcr.microsoft.com/mssql/server:2022-latest   "/opt/mssql/bin/perm…"   8 seconds ago   Up 7 seconds   0.0.0.0:1433->1433/tcp, :::1433->1433/tcp   sql1

Step 4 – Connect To SQL Server Command Line Shell

Connect to the SQL Server instance using the sqlcmd command-line utility.

First, start an interactive shell inside the SQL Server container:

docker exec -it sql1 "bash"

This changes your prompt to the container’s shell:

mssql@sql1:/$

Then, use sqlcmd to connect to the SQL Server instance:

/opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "<sql-server-password>"

Replace <sql-server-password> with the password you set during container creation. After entering the correct password, you will see the SQL Server command prompt:

>

From the SQL Server shell, you can execute SQL commands to create and manage databases. For example, to create a test database and view the system databases:

> CREATE DATABASE TestDB;
> SELECT Name from sys.databases;

To see the results of the query, use the GO command:

> GO
1> CREATE DATABASE TestDB;
2> SELECT Name from sys.databases;
3> GO
Name
--------------------------------------------------------------------------------------------------------------------------------
master
tempdb
model
msdb
TestDB

(5 rows affected)
1>

For a comprehensive list of Microsoft SQL Server queries and commands, consult online resources.

Conclusion

You have now successfully learned how to Install Latest MS SQL Server with Docker on Ubuntu 22.04. Using Docker provides a convenient workaround for installing SQL Server on Ubuntu 22.04 due to potential repository incompatibility. Docker containers allow for easier install, configuration, and running SQL server.

Alternative Solutions for Installing MS SQL Server on Ubuntu 22.04

While Docker provides an excellent and isolated environment for running MS SQL Server, other methods exist. Here are two alternative approaches:

1. Manual Installation with Package Management (Using a Compatibility Layer)

This approach attempts to install SQL Server directly on Ubuntu 22.04. Since Ubuntu 22.04 is not officially supported at the time of writing, this involves using a compatibility layer and potentially adapting installation instructions from a similar, supported distribution.

Explanation:

The core idea is to trick the SQL Server installation process into believing it’s running on a supported distribution. This often involves modifying system files or using tools that provide a compatibility layer. This method is riskier and could lead to system instability.

Steps (Conceptual – Use with Caution):

  1. Identify a Similar Supported Distribution: Choose a distribution that SQL Server officially supports and is closely related to Ubuntu 22.04 (e.g., a slightly older Ubuntu version or a Debian version).

  2. Modify System Identification (Extremely Risky): This step involves changing files like /etc/os-release to mimic the supported distribution. This can break your system and is strongly discouraged unless you fully understand the risks.

  3. Attempt Installation: Follow the official SQL Server installation instructions for the distribution you are mimicking.

  4. Troubleshoot: Expect errors and dependency issues. You’ll need to resolve these manually, potentially by downloading and installing specific packages.

Why this is discouraged: This approach is fragile and can easily lead to a broken system. Upgrades and updates become problematic as they might rely on the modified system identification. It’s generally better to use a supported method like Docker.

2. Using a Virtual Machine (VM)

This method involves installing a supported operating system inside a virtual machine on your Ubuntu 22.04 host. You then install SQL Server on the guest operating system.

Explanation:

A virtual machine creates an isolated environment that emulates a complete computer system. You can install a supported operating system (e.g., an older Ubuntu version, CentOS, or Windows Server) within the VM and then install SQL Server on that OS.

Steps:

  1. Install a Hypervisor: Install a virtualization solution like VirtualBox or VMware on your Ubuntu 22.04 host.

    sudo apt update
sudo apt install virtualbox

  2. Create a New VM: Create a new virtual machine and allocate sufficient resources (CPU, memory, disk space).

  3. Install a Supported OS: Install a supported operating system inside the VM.

  4. Install SQL Server: Follow the official SQL Server installation instructions for the operating system you installed in the VM.

  5. Configure Networking: Configure the VM’s networking to allow access to the SQL Server instance from your host machine or other machines on your network.

Code Example (Port Forwarding with VirtualBox):

Assuming you’re using VirtualBox and want to forward port 1433 from the guest OS to port 1433 on the host:

  1. Shut down the VM.

  2. Open VirtualBox Manager.

  3. Select the VM.

  4. Click on "Settings."

  5. Go to "Network" -> "Adapter 1" (or the adapter you’re using).

  6. Click on "Advanced" and then "Port Forwarding."

  7. Add a new rule with the following settings:

    • Name: SQL Server
    • Protocol: TCP
    • Host IP: 127.0.0.1 (or the IP address of your host)
    • Host Port: 1433
    • Guest IP: (Leave blank)
    • Guest Port: 1433

  8. Start the VM.

Now, you should be able to connect to SQL Server from your host machine using localhost:1433.

Advantages of using a VM:

  • Isolation: Provides a completely isolated environment for SQL Server.
  • Compatibility: Allows you to run SQL Server on a supported operating system without modifying your host system.
  • Flexibility: You can easily create and manage multiple SQL Server instances with different configurations.

Disadvantages of using a VM:

  • Overhead: Virtual machines introduce some performance overhead compared to running SQL Server directly on the host.
  • Resource Consumption: VMs consume more system resources (CPU, memory, disk space) than Docker containers.
  • Complexity: Setting up and configuring a VM can be more complex than using Docker.

In summary, while Docker offers a streamlined and efficient solution for installing MS SQL Server on Ubuntu 22.04, VMs provide a robust alternative with better compatibility at the cost of increased resource consumption. The manual installation approach is generally discouraged due to its instability and potential to break your system. When choosing a method to Install Latest MS SQL Server with Docker on Ubuntu 22.04, carefully consider your needs, available resources, and comfort level with each technology.

Related posts: