Build a custom SQL Server image from scratch

In a previous wiki we built a custom SQL Server docker image from the SQL 2019 CU5 image in order to grant permissions to custom directories.

In this wiki we'll go through how to build a custom SQL image from "scratch". Ok, not exactly scratch, what we're going to do is start off with the Ubuntu 18.04 image and install SQL on it.

Here is the dockerfile: -

FROM ubuntu:18.04
 
RUN useradd -u 10001 mssql
 
RUN apt-get update && apt-get install -y wget software-properties-common apt-transport-https

RUN wget -qO- https://packages.microsoft.com/keys/microsoft.asc | apt-key add -

RUN add-apt-repository "$(wget -qO- https://packages.microsoft.com/config/ubuntu/18.04/mssql-server-2019.list)"

RUN apt-get update && apt-get install -y mssql-server

RUN chown -R mssql:mssql /var/opt/mssql

USER mssql

CMD /opt/mssql/bin/sqlservr

Let's go over each step in the dockerfile: -

1. Building from the Ubuntu 18.04 image
2. Creating the mssql user with ID 10001
3. Installing required packages
4. Import the Microsoft public repository GPG keys
5. Adding the SQL Server 2019 repository
6. Installing SQL Server
7. Setting the owner of the mssql directory to the mssql user
8. Switching to the mssql user from root
9. Starting SQL Server

Ok, now we can build the image: -

docker build -t customsqlimage1 .

Once that's complete we can check the image is available: -

docker image ls

Now we can spin up a container from the image: -

docker container run -d `
-p 15789:1433 `
--env ACCEPT_EULA=Y `
--env MSSQL_SA_PASSWORD=Testing1122 `
--name sqlcontainer1 `
customsqlimage1

And confirm that the container is running: -

docker container ls -a

Finally, check that we can connect to SQL within the container: -

mssql-cli -S localhost,15789 -U sa -P Testing1122 -Q "SELECT @@VERSION AS [Version];"

And that's us connected to SQL running in a container that was built from our custom image!