Run Microsoft SQL Server in Podman|Docker Container

Posted on 129 views

Welcome to this guide on how to run Microsoft SQL Server in Podman|Docker Container. Podman is a tool developed by RedHat to act as a drop-in replacement of docker. It is used to run images and containers just like docker. The only difference between the two is that Podman does not require a Docker Engine to run containers but still implements all the Docker CLI commands.

Podman is preferred when running containers since it allows one to run containers directly from Kubernetes as long as the container is OCI-compliant. Since Docker is not officially supported by RedHat, Podman, therefore, drops in to run these containers.

In this guide, we will run the Microsoft SQL server in a Podman container. A database is key in any production environment. Microsoft SQL is the most widely used relational database management system that offers the following features:

  • It is secure
  • Client/ Server Architecture
  • It is scalable
  • High Flexibility
  • Compatible on many operating systems
  • It is a very fast database language with a large number of benchmark test.
  • Allows roll-back
  • Offers high performance
  • Dual Password Support

Run Microsoft SQL Server in Podman|Docker Container

This guide offers a systematic illustration of how to run Microsoft SQL Server in Podman Docker Container.

Step 1. Install Podman|Docker on your System.

In this guide, I will illustrate how to run a Microsoft SQL server container on both Podman and Docker.

Install a tool of your choice and proceed.

Install Podman on Linux

Installing Podman on a Linux system is easy. It will be used to manage the Microsoft SQL server container. This can be accomplished as below.

#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 your Podman installation.

$ podman info
host:
  arch: amd64
  buildahVersion: 1.22.3
  cgroupControllers: []
  cgroupManager: cgroupfs
  cgroupVersion: v1
  conmon:
    package: conmon-2.0.29-1.module+el8.4.0+643+525e162a.x86_64
    path: /usr/bin/conmon
    version: 'conmon version 2.0.29, commit: ce0221c919d8326c218a7d4d355d11848e8dd21f'
.........

On Debian/Ubuntu, you will be required to configure your system to work with OCI registries by editing the file as below.

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

Install Docker on Linux.

Those who want to use Docker, install Docker CE on your Linux system with the aid of the dedicated guide below.

Version of Docker installed can be checked using the command below:

$ docker --version
Docker version 20.10.17, build 100c701

Add your user account to docker group:

sudo usermod -aG docker $USER
newgrp docker

Step 2. Create a Persistent Volume for the Microsoft SQL Server container.

Now we will begin by creating a persistent data volume for the Microsoft SQL server. In other words, this directory will be used to store database files by Microsoft SQL server.

sudo mkdir -p  /var/mssql/data
sudo chmod 755 -R /var/mssql/data

For RHEL based systems, you need to set SELinux in permissive mode to allow the above directory to be accessible.

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

Step 3. Provision the Microsoft SQL Server container

Then proceed fourth and pull the latest Microsoft SQL server image.

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

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

Sample Output.

Trying to pull mcr.microsoft.com/mssql/server:2019-latest...
Getting image source signatures
Copying blob 84a6a587b3fb done  
Copying blob 912596dfeaeb done  
Copying blob be2aa0ec326c done  
Copying blob 35807b77a593 done  
Copying blob a0ceb3206273 done  
Copying config 80bdc8efc8 done  
Writing manifest to image destination
Storing signatures
80bdc8efc8890107b8b5be642bf9e88cb5b3c336e6f9730b5ba9b08ced942c43

Check the downloaded image.

##For Podman
$ podman images
REPOSITORY                      TAG          IMAGE ID      CREATED      SIZE
registry.access.redhat.com/ubi8/pause  latest       20b34168e325  2 weeks ago  3.49 MB
mcr.microsoft.com/mssql/server  2019-latest  80bdc8efc889  7 weeks ago  1.56 GB

##For Docker
$ docker images
REPOSITORY                      TAG          IMAGE ID      CREATED      SIZE
registry.access.redhat.com/ubi8/pause  latest       20b34168e325  2 weeks ago  3.49 MB
mcr.microsoft.com/mssql/server  2019-latest  80bdc8efc889  7 weeks ago  1.56 GB

Now the image is available in the local registry.

Step 4. Run the Microsoft SQL Server container.

Now with the Microsoft SQL server image downloaded, we can easily spin the container using the command below.

1. For Podman, issue the commands below:

podman run -d -e 'ACCEPT_EULA=Y' -e \
  'MSSQL_SA_PASSWORD=Passw0rd'  \
  --name MSSQL \
  -p 1460:1433 \
  -v /var/mssql/data:/var/mssql/data:Z \
  mcr.microsoft.com/mssql/server:2019-latest

2. For Docker, use the commands below:

docker run -d -e 'ACCEPT_EULA=Y' -e \
  'MSSQL_SA_PASSWORD=Passw0rd'  \
  --name MSSQL \
  -p 1460:1433 \
  -v /var/mssql/data:/var/mssql/data:Z \
  mcr.microsoft.com/mssql/server:2019-latest

In the command above, replace Passw0rd with the preferred password you want to set for your Microsoft SQL database server.

Check if the container is running:

##For Podman
$ podman ps -a
CONTAINER ID  IMAGE                                       COMMAND               CREATED         STATUS             PORTS                   NAMES
89decb0a54d1  mcr.microsoft.com/mssql/server:2019-latest  /opt/mssql/bin/sq...  15 seconds ago  Up 15 seconds ago  0.0.0.0:1460->1433/tcp  MSSQL

##For Docker
$ docker ps -a
CONTAINER ID  IMAGE                                       COMMAND               CREATED         STATUS             PORTS                   NAMES
89decb0a54d1  mcr.microsoft.com/mssql/server:2019-latest  /opt/mssql/bin/sq...  15 seconds ago  Up 15 seconds ago  0.0.0.0:1460->1433/tcp  MSSQL

Check the dependencies in the CRI-O implementation.

$ pstree
systemd─┬─ModemManager───2*[ModemManager]
        ├─NetworkManager───2*[NetworkManager]
        ├─accounts-daemon───2*[accounts-daemon]
        ├─atd
        ├─auditd─┬─sedispatch
        │        └─2*[auditd]
        ├─avahi-daemon───avahi-daemon
.......

From the output, it is amazing that there are no dependencies with any daemon(docker).

In this guide, we ran the Microsoft SQL server container in isolation mode through Linux namespaces. Verify this as below.

$ ps aux | grep sqlservr
110000     33459  0.3  0.3  61116 22908 ?        Ssl  03:34   0:00 /opt/mssql/bin/sqlservr
110000     33473  4.8 11.4 11330728 676760 ?     Sl   03:34   0:06 /opt/mssql/bin/sqlservr
thor       33808  0.0  0.0  12136  1188 pts/0    S+   03:36   0:00 grep --color=auto sqlservr
$ sudo lsns 
4026532582 uts         2 33459 110000 /opt/mssql/bin/sqlservr
4026532583 ipc         2 33459 110000 /opt/mssql/bin/sqlservr
4026532584 pid         2 33459 110000 /opt/mssql/bin/sqlservr
4026532712 mnt         1  2008 colord /usr/libexec/colord

From the output, the Microsoft SQL server is running with PID 33459 in isolation mode.

Step 5. Connect to Microsoft SQL server.

Now that we have the container running, we need to connect to the Microsoft SQL server instance. This is achieved in the steps below.

First, start an interactive shell inside the container as below.

##For Podman
podman exec -it MSSQL "bash"

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

Sample output:

[email protected]:/$ 

The command above specifies the name of the container, here the container name is MSSQL.

Now while in the container, we will use the sqlcmdcommand to connect to the instance locally. You also have to specify the path where sqlcmd is.

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

Passw0rd is the password you set while deploying the Microsoft SQL server container.

On successful login, you will see this.

[email protected]:/$ /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "Passw0rd"
1>

Step 6. Create and query data in Microsoft SQL server.

With sqlcmd, you can create new databases, add data and run queries.

To create a new database, testdb run the command below.

CREATE DATABASE testdb

Then list all available databases on your system.

SELECT Name from sys.Databases

To execute previous commands type GO on a new line as shown.

3> GO

Sample Output:

Name                                                                                                                            
--------------------------------------------------------------------------------------------------------------------------------
master                                                                                                                          
tempdb                                                                                                                          
model                                                                                                                           
msdb                                                                                                                            
testdb                                                                                                                          

(5 rows affected)

Now add data to the created database.

USE testdb
CREATE TABLE Inventory (id INT, name NVARCHAR(50), quantity INT)
GO

We have created a new inventory table in the database.

Query data from the new table (Inventory)

SELECT * FROM Inventory WHERE quantity > 152;
GO

Exit the sqlcmd as below.

3> QUIT
[email protected]:/$ exit
exit

Step 7. Manage your Microsoft SQL server Container.

If you want to manage your Microsoft SQL container, use the below commands.

Start and stop the container.

##For Podman
podman stop MSSQL
podman start MSSQL

##For Docker
docker stop MSSQL
docker start MSSQL

In case you want to delete the container, stop it and remove it as below.

##For Podman
podman rm MSSQL

##For Docker
docker rm MSSQL

Conclusion.

That is it! I hope you learned a lot from this guide on how to run Microsoft SQL Server in Podman|Docker Container. We have seen that the two containerization tools Podman and docker have many more similarities than differences. I hope you enjoyed it.

 

coffee

Gravatar Image
A systems engineer with excellent skills in systems administration, cloud computing, systems deployment, virtualization, containers, and a certified ethical hacker.