Run Microsoft SQL Server 2022 in Docker / Podman Container

Databases are key elements in any production environment as they are used to store data. Database servers are primarily used to store and retrieve data as requested by other software applications. It can be installed on the same or different system across the network.

There are two main database types. These are:

• Relational databases (RDBMS or SQL databases): where data is stored in tables and rows, the tables are then linked using keys. They include MySQL, MariaDB, PostgreSQL, SQLite e.t.c
• Non-relational databases(NoSQL databases): here data is stored using a storage model optimized for specific requirements. They include Couchbase, Apache Cassandra, MongoDB, Redis, Apache HBase e.t.c

Microsoft SQL abbreviated as MsSQL is a popularly used relational database management system (RDBMS) developed by Microsoft. This product was first developed in 1989 as SQL Server 1.0, a 16-bit server for the OS/2 operating system. Ever since there have been several other release versions that include SQL Server 4.2, SQL Server 6.0, SQL Server 7.0, SQL Server 2005, SQL Server 2012, SQL Server 2017, SQL Server 2019 and SQL Server 2022.

Today, our key focus is the Microsoft SQL Server 2022, the latest stable release of the SQL Server database management system. This version was made available on November 16, 2022, available for download to customers with SQL on Enterprise Agreement (EA), Enterprise Agreement Subscription (EAS), and Server and Cloud Enrollment (SCE) programs.

This is the most modern data platform to transform your business. It is considered the core element of the Microsoft Intelligent Data Platform which allows customers to adapt in real-time, and add layers of intelligence to their dynamic applications while unlocking fast and predictive insights.

Features of Microsoft SQL Server 2022

The amazing features and benefits associated with Microsoft SQL Server 2022 are:

• Analytics: Real-time analytics over operational data in SQL Server 2022 (16.x) with seamless integration between operational stores in SQL Server 2022 (16.x) and Azure Synapse Analytics dedicated SQL pools. You can also query different types of data on different types of data sources from SQL Server.
• Availability: The improved backupset system table returns the last valid restore time. The contained availability group allows users to create an Always On availability group that;
  • manages its own metadata objects (users, logins, permissions, SQL Agent jobs etc.) at the availability group level in addition to the instance level.
  • Includes specialized contained system databases within the availability group
• Security: Microsoft Defender for Cloud integration that protects your SQL servers using the Defender for SQL plan. Moreso, the ledger feature provides tamper-evidence capabilities in your database. You can also use the  Azure Active Directory (Azure AD) authentication to connect to SQL Server.
• Performance: The System page latch concurrency enhancements allow concurrent updates to global allocation map (GAM) pages and shared global allocation map (SGAM) pages to reduce page latch contention while allocating/deallocating data pages and extents. The Buffer pool parallel scan improves the performance of buffer pool scan operations on large-memory machines by utilizing multiple CPU cores. There is also an improved column store segment elimination and in-memory OLTP management
• Query Store and intelligent query processing: This feature includes all the features that improve the performance of existing workloads with minimal implementation effort. These include the Query Store on secondary replicas, Query Store hints, Memory grant feedback, Parameter sensitive plan optimization, cardinality estimation feedback e.t.c
• Management: integrated setup experience for the Azure extension for SQL Server. You can use the SQL Server Configuration Manager to manage Azure extension for SQL Server service. There is accelerated Database Recovery (ADR) improvement and snapshot backup support.

Run Microsoft SQL Server 2022 in Docker / Podman Container

There are several ways to install the Microsoft SQL Server 2022. You can install it using the aid captured in the below guides:

• Install Microsoft SQL Server 2022 on Rocky Linux 8 / AlmaLinux 8
• How To Install Microsoft SQL Server 2022 on Ubuntu

In this guide, we will walk through how to run Microsoft SQL Server 2022 in Docker / Podman Container. This method is preferred because the container can be run on any system with Docker / Podman installed. It also provides the simplest method to run the server since the container is bundled with all the required packages.

Let’s dive in!. For this guide, you need:

• At least 2 GB of disk space.
• At least 2 GB of RAM.
• Docker Engine 1.8+ on any supported Linux distribution. For more information, see Install Docker.
• Docker overlay2 storage driver. This driver is the default for most users. If you aren’t using this storage provider and need to change, see the instructions and warnings in the Docker documentation for configuring overlay2.

Step 1. Install Podman / Docker Engine

For this to be a success, you need to have either Podman or Docker installed on your system. Depending on your desired tool, proceed as below.

Install Docker on Linux

For those who prefer using the Docker Engine to spin the containers, install it using the aid captured here:

• How To Install Docker CE on Linux Systems

To verify the installation, use the command:

$ docker --version
Docker version 23.0.3, build 3e7cbfd

Now add your system user to the Docker group to be able to execute the docker commands without sudo:

sudo usermod -aG docker $USER
newgrp docker

Install Podman on Linux

For those who want to use Podman, ensure it is installed on the system. For Linux systems, you can use the below commands:

#On Debian/Ubuntu
sudo apt install podman

#On CentOS/Rocky Linux
sudo yum install podman

#On Fedora
sudo dnf install podman

#On RHEL 8
sudo yum module enable -y container-tools:rhel8
sudo yum module install -y container-tools:rhel8

#On RHEL 7
sudo subscription-manager repos --enable=rhel-7-server-extras-rpms
sudo yum -y install podman

Verify the installation by executing the below command:

$ podman info
host:
  arch: amd64
  buildahVersion: 1.27.3
  cgroupControllers: []
  cgroupManager: cgroupfs
  cgroupVersion: v1
  conmon:
    package: conmon-2.1.0-1.module+el8.6.0+785+d1251653.x86_64
    path: /usr/bin/conmon
    version: 'conmon version 2.1.0, commit: 50f48a3659e41ca0f0483ae829abdfb74a73287c'
.........

On Debian/Ubuntu, you need to make the below config for your system to work with the required OCI registries;

$ sudo vim /etc/containers/registries.conf
unqualified-search-registries = ["registry.fedoraproject.org", "registry.access.redhat.com", "registry.centos.org", "docker.io"]

Step 2. Create a Persistent Volume

For the Microsoft SQL Server container’s data to persist, we need to have a persistent data volume created. In this guide, we will create a data directory as shown below:

sudo mkdir -p  /var/mssql/data
sudo chmod 777 -R /var/mssql/data
sudo chown 10001:0 /var/mssql/data

For RHEL-based systems, ensure that SELinux is configured using the below commands. Otherwise, the path might be inaccessible.

sudo setenforce 0
sudo sed -i 's/^SELINUX=.*/SELINUX=permissive/g' /etc/selinux/config

Step 3. Run SQL Server 2022 in container

We will pull the container image from the Microsoft Container Registry. The commands below can be used:

##For Podman
podman pull mcr.microsoft.com/mssql/server:2022-latest

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

Sample Output:

Trying to pull mcr.microsoft.com/mssql/server:2022-latest...
Getting image source signatures
Copying blob f09f69f4f754 done  
Copying blob ecaa8206c099 done  
Copying blob 0f6bf08cd563 done  
Copying config 61b7402240 done  
Writing manifest to image destination
Storing signatures
61b74022404897143fe3d0d063ed3948117cd3ade6fd0d12a7893c362d767abf

Once pulled, you can check the image with the command:

##For Podman
$ podman images
REPOSITORY                      TAG          IMAGE ID      CREATED      SIZE
mcr.microsoft.com/mssql/server  2022-latest  61b740224048  3 weeks ago  1.61 GB

##For Docker
$ docker images
REPOSITORY                       TAG           IMAGE ID       CREATED       SIZE
mcr.microsoft.com/mssql/server   2022-latest   61b740224048   3 weeks ago   1.6GB

You now have the image in your Local registry. You are now set to spin a container using the image. Use the commands below to start the container:

• Podman

podman run -d -e "ACCEPT_EULA=Y" \
   -e "MSSQL_SA_PASSWORD=StrongPassw0rd" \
   -p 1433:1433 \
    --name  MSSQL \
    --hostname MSSQL \
   -v /var/mssql/data:/var/opt/mssql \
   mcr.microsoft.com/mssql/server:2022-latest

• Docker:

docker run -d -e "ACCEPT_EULA=Y" \
   -e "MSSQL_SA_PASSWORD=StrongPassw0rd" \
   -p 1433:1433 \
    --name  MSSQL \
    --hostname MSSQL \
   -v /var/mssql/data:/var/opt/mssql:Z \
   mcr.microsoft.com/mssql/server:2022-latest

In the above commands, remember to replace StrongPassw0rd with your own desired password for the Microsoft SQL database server.

Now verify if the server is running:

##For Podman
$ podman ps 
CONTAINER ID  IMAGE                                       COMMAND               CREATED        STATUS            PORTS                   NAMES
894a4b72effc  mcr.microsoft.com/mssql/server:2022-latest  /opt/mssql/bin/sq...  8 seconds ago  Up 8 minutes ago  0.0.0.0:1433->1433/tcp  MSSQL

##For Docker
$ docker ps
CONTAINER ID   IMAGE                                        COMMAND                  CREATED          STATUS          PORTS                                       NAMES
9e5ce06659ee   mcr.microsoft.com/mssql/server:2022-latest   "/opt/mssql/bin/perm…"   19 seconds ago   Up 17 seconds   0.0.0.0:1433->1433/tcp, :::1433->1433/tcp   MSSQL

From the above output, the container is up and running. We are now ready to connect to it and run queries. You can view the logs in the container using the command:

$ docker exec -t MSSQL cat /var/opt/mssql/log/errorlog | grep connection
##OR
$ podman exec -t MSSQL cat /var/opt/mssql/log/errorlog | grep connection

You can check the CRI-O implementation dependencies using the command:

$ pstree
systemd─┬─ModemManager───2*[{ModemMana+
        ├─NetworkManager───2*[{Network+
        ├─accounts-daemon───2*[{accoun+
        ├─at-spi-bus-laun─┬─dbus-daemo+
        │                 └─3*[{at-spi+
        ├─at-spi2-registr───2*[{at-spi+
        ├─atd
        ├─auditd─┬─sedispatch
        │        └─2*[{auditd}]
        ├─avahi-daemon───avahi-daemon
        ├─catatonit
        ├─chronyd
        ├─colord───2*[{colord}]
        ├─conmon─┬─sqlservr─┬─sqlservr+++
        │        │          └─2*[{sqls+
        │        └─{conmon}
        ├─crond
.....

Step 4. Change system administrator password

The default system administrator account in the Microsoft SQL server is known as SA. The password for this account was set when spinning the container using the variable MSSQL_SA_PASSWORD.

The password set here should be as strong as possible for security purposes. In case you need to set a new password for the account, you need to execute a command with the below syntax:

##For Podman
podman exec -it MSSQL /opt/mssql-tools/bin/sqlcmd \
-S localhost -U SA \
 -P "$(read -sp "Enter current SA password: "; echo "${REPLY}")" \
 -Q "ALTER LOGIN SA WITH PASSWORD=\"$(read -sp "Enter new SA password: "; echo "${REPLY}")\""

##For Docker
docker exec -it MSSQL /opt/mssql-tools/bin/sqlcmd \
-S localhost -U SA \
 -P "$(read -sp "Enter current SA password: "; echo "${REPLY}")" \
 -Q "ALTER LOGIN SA WITH PASSWORD=\"$(read -sp "Enter new SA password: "; echo "${REPLY}")\""

Provide the current and new passwords where specified.

Step 5. Connect to Microsoft SQL Server 2022.

We can now connect to the server and run the desired queries. This can be done using the commands:

#For Podman
podman exec -it MSSQL "bash"

##For Docker
docker exec -it MSSQL "bash"

The above command specifies the name of the container as MSSQL. Below is the Sample Output:

$ docker exec -it MSSQL "bash"
mssql@MSSQL:/$ 

Now we can run the belowsqlcmdcommand to connect to the instance locally:

/opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "StrongPassw0rd"

Remember StrongPassw0rd is the password you set for the server. On successful authentication, you will this:

Step 6. Create and query data

Once in the shell, we can do several operations such as creating, deleting and adding data to databases.

To create a database, issue the commands:

CREATE DATABASE testdb
GO

List the available databases:

SELECT Name from sys.Databases
Go

Sample Output:

Now we will add sample data to the DB:

USE testdb
CREATE TABLE Inventory (id INT, name NVARCHAR(50), quantity INT)
INSERT INTO Inventory VALUES (1, 'Redhat', 150); INSERT INTO Inventory VALUES (2, 'Ubuntu', 154);
GO

Now query data from the table created:

SELECT * FROM Inventory WHERE quantity > 152;
GO

Sample Output:

To exit the shell use the commands:

1> QUIT
mssql@MSSQL:/$ exit
exit

Step 7. Manage Microsoft SQL Server in container

The Microsoft SQL Server 2022 Container can be started and stopped using the simple commands below:

##For Podman
podman stop MSSQL
podman start MSSQL

##For Docker
docker stop MSSQL
docker start MSSQL

To delete the container, use the command:

#For Podman
podman rm MSSQL

##For Docker
docker rm MSSQL

Verdict

That marks the end of this detailed demonstration of how to run the Microsoft SQL Server 2022 in Docker / Podman Container. You are now set to use the server as desired. I hope this was helpful.

Interested in more?

• How To Run QuestDB SQL database in Docker Container
• Run Microsoft SQL Server in Podman|Docker Container
• How To Run Redis in Podman / Docker Container
• Running PowerDNS and PowerDNS Admin in Docker Containers